CODEWITHSUNDEEP
excelvbauserform
New2Programming
New2Programming

Reputation: 383

EXCEL Userform - Creating multiple Labels and Textboxes with specific names

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

Upvotes: 0

Views: 583

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

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

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.

Upvotes: 1

Related Questions