EXCEL Userform - Creating multiple Labels and Textboxes with specific names

Question

I have a Data Entry Userform that works but now I want to replicate it I need 36 fields in total (144 items not including buttons) for an example Field 1 will consist of a TextBox and 3 labels. (Data Entry, Title, Bottom Border and FieldRequired label.

What I want to do is to generate the above with names like Txt1,Txt2,Txt3.... Title1, Title2, Title3, Bdr1,Bdr2,Bdr3, Fr1,Fr2,Fr3 and for some I need to create Listbox1,Listbox2 and Listbox3 inside of frames 1 2 and 3 but this I can do manually.

I want to separate them so 4 fields across and 9 fields down.

Is there an easy solution to doing this or just doing it manually?

I can sort of do this using the below and then just doing this 4 times and adding 80 to the left I would then need do to the same for the other fields and apply the events to them and fonts/font sizes etc but I cant figure out how to use events against them.

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

In order to create/use events you should create specific classes for each control type. I can show you how. But, will all of them should have the same code? If not so, is there an algorithm to select its behavior according to their numeric suffix? — FaneDuru
– FaneDuru, Commented Feb 8, 2023 at 18:05
Yeah all the same using the enter exit and change events hiding the labels and making them active all based on the selection — New2Programming
– New2Programming, Commented Feb 8, 2023 at 18:38
I cannot completely understand how exactly the code should be. If I will prepare an answer showing how to create the necessary class for the 5 text boxes added on the fly, name them as "Txt1", "Txt2" to "Txt5" and the code part to allocate the Change event to all created text boxes, will it be what you need? I mean, you should replicate the way for all the others controls having (their own the same or different) events. — FaneDuru
– FaneDuru, Commented Feb 8, 2023 at 18:58
Yeah as the rest can just apply it to the other events. Thanks — New2Programming
– New2Programming, Commented Feb 8, 2023 at 19:06
OK. I will prepare a piece of code covering what I said above... — FaneDuru
– FaneDuru, Commented Feb 8, 2023 at 19:44

FaneDuru · Accepted Answer · 2023-02-08 20:07:42Z

1

Please, test the next scenario:

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

Now, show the form and play with text in the 5 newly created text boxes.

You can show one of its instances in the next way:

a) Name it "frmTxtBEvents"

b) Use the next Sub:

Sub ShowTheForm()
   Dim frm As New frmTxtBEvents
   frm.Show vbModeless
End Sub

When enter 4 characters, according to the last text box name digit their Change event will show specific message boxes...

If something not clear enough, do not hesitate to ask for clarifications.

But it is late in my country and (today) I will be available for no more than half an hour.

answered Feb 8, 2023 at 20:07

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FaneDuru Over a year ago

New2Programming Didn't you find some time to test the above suggestion? If tested, didn't it do something similar with what you want? No offence, but can you even better explain what you want? I mean, do you need controls to be created on the fly, like in the code you show, or you need to add controls able to remain on the respective form?

FaneDuru Over a year ago

@New2Programming Saying "replicate" it is possible to programmatically create a UserForm to remain and place controls on it, as on the model one, write their events code, but it is a little more complicated and a different way of doing. Theoretically, it should be possible to have such a form as reference and extract from it the necessary controls, their position etc. A little more complicated to also extract and set all their properties which are not default...

Collectives™ on Stack Overflow

EXCEL Userform - Creating multiple Labels and Textboxes with specific names

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related