EXCEL Userform - Creating multiple Labels and Textboxes with specific names

0 votes

I now have a working Data Entry Userform, but I want to duplicate it. I would like an example with 36 fields (144 items without include buttons). Three labels and a TextBox will make up Field 1. (FieldRequired label, Bottom Border, Title, and Data Entry.

In order to generate the aforementioned files with names like Txt1,Txt2,Txt3, Title1, Title2, Title3, Bdr1,Bdr2,Bdr3, Fr1,Fr2,Fr3, and for some, Listbox1,Listbox2, and Listbox3 inside of frames 1–3 must be created manually.

4 fields across and 9 fields down is how I want to divide them.

Is there a simple way to achieve this, or must you do it by hand?

I can sort of accomplish this by applying the formula below, repeating it four times, and adding 80 to the left. I would then need to apply the same formula to the other fields and apply events, fonts, font sizes, etc. to them, but I'm not sure how to do it.

Sub addLabel()
frmUserAdd.Show vbModeless
Dim lblid As Object
Dim lblc As Long

For lblc = 1 To 9
    Set lblid = frmUserAdd.Controls.Add("Forms.Label.1", "Alert" & lblc, True)
    With lblid
        .Caption = "*Field Required" & lblc
        .Left = 10
        .Width = 60
        .Top = 30 * lblc
    End With
Next
end sub
Feb 9 in Others by Kithuzzz
• 34,760 points
111 views

1 answer to this question.

0 votes

Please, test the next scenario:

  1. Insert a class module, name it "clsTbox" and copy the next code inside it:
Option Explicit

Public WithEvents newTBox As MSForms.TextBox

Private Sub newTBox_Change()
   If Len(newTBox.Text) > 3 Then 'it do something for 4 entered digits:
        Select Case CLng(Right(newTBox.name, 1))
            Case 1, 3
                MsgBox newTBox.name & " changed (" & newTBox.Text & ")"
            Case 2, 4
                MsgBox newTBox.name & " changed its text"
            Case Else
               MsgBox newTBox.name & " Different text..."
        End Select
  End If
End Sub

Insert a Userform and copy the next code in its code module:

Option Explicit

Private TBox() As New clsTBox

Private Sub UserForm_Initialize()
    Dim i As Long, txtBox01 As MSForms.TextBox, leftX As Double, tWidth As Double, k As Long
    Const txtBName As String = "Txt"
    
    leftX = 20: tWidth = 50
    ReDim TBox(10) 'use here the maximum number of text boxes you intend creating
    For i = 1 To 5
         Set txtBox01 = Me.Controls.Add("Forms.TextBox.1", txtBName & i)
        With txtBox01
            .top = 10
            .left = leftX: leftX = leftX + tWidth
            .width = tWidth
            .Text = "something" & i
        End With
        
        Set TBox(k).newTBox = txtBox01: k = k + 1
    Next i
    ReDim Preserve TBox(k - 1) 'keep only the loaded array elements
End Sub

answered Feb 9 by narikkadan
• 59,740 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

SharePoint - Excel Online - Edit in Excel and Edit in Browser multiple users

Here is the Microsoft article regarding co-authoring https://support.office.com/en-us/article/Collaborate-on-Excel-workbooks-at-the-same-time-with-co-authoring-7152aa8b-b791-414c-a3bb-3024e46fb104?ui=en-US&rs=en-US&ad=US When ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 59,740 points
419 views
0 votes
1 answer

Create unique rows in Excel with limited data to be used in multiple columns

This setup isn't readily generalizable, though since ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 59,740 points
180 views
0 votes
1 answer

Repeated excel rows based on a cell with multiple values

You can use this query: let ...READ MORE

answered Oct 20, 2022 in Others by narikkadan
• 59,740 points
212 views
0 votes
1 answer

Excel VBA userform paste text in expanded format (not just to one cell)

It should work if you simply supply ...READ MORE

answered Jan 21 in Others by narikkadan
• 59,740 points
76 views
0 votes
1 answer

VBA - Msgbox inside a Loop

 Try this: Private Sub CommandButton1_Click() ...READ MORE

answered Mar 19 in Others by Kithuzzz
• 34,760 points
38 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
575 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,548 views
0 votes
1 answer
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP