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, 2023 in Others by Kithuzzz
• 38,010 points
801 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, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How to use COUNTIFS with multiple conditions and columns in Excel?

Use  SUMPRODUCT and double unay operators: =SUMPRODUCT ...READ MORE

answered Apr 4, 2023 in Others by Kithuzzz
• 38,010 points
292 views
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
• 63,420 points
935 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
• 63,420 points
499 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, 2023 in Others by narikkadan
• 63,420 points
386 views
0 votes
1 answer

VBA - Msgbox inside a Loop

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

answered Mar 19, 2023 in Others by Kithuzzz
• 38,010 points
490 views
0 votes
1 answer
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
876 views
0 votes
1 answer
0 votes
1 answer

Creating sheets with names in column B and assigning data to each sheet for particular name

after the first occurrence of Set sht = ...READ MORE

answered Feb 13, 2023 in Others by narikkadan
• 63,420 points
477 views
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