英文:
EXCEL Userform - Creating multiple Labels and Textboxes with specific names
问题
我有一个数据录入用户窗体,它可以工作,但现在我想要复制它,我需要总共36个字段(不包括按钮的144个项目)
举个例子
字段1将包括一个文本框和3个标签(数据录入、标题、底部边框和字段必填标签)。
我想要做的是生成上述内容,类似于Txt1、Txt2、Txt3... Title1、Title2、Title3、Bdr1、Bdr2、Bdr3、Fr1、Fr2、Fr3 这样的命名,对于某些字段,我需要在框架1、2和3内创建Listbox1、Listbox2和Listbox3,但这部分我可以手动完成。
我希望将它们分开,每行4个字段,每列9个字段。
有没有简单的解决方案,还是只能手动完成?
我可以使用以下方法实现这一点,然后再重复4次并将左侧增加80
然后,我需要对其他字段执行相同的操作,并应用事件和字体/字体大小等,但我无法弄清楚如何使用事件来处理它们。
英文:
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
答案1
得分: 1
请测试以下场景:
- 插入一个类模块,将其命名为 "clsTbox" 并复制以下代码到其中:
Option Explicit
Public WithEvents newTBox As MSForms.TextBox
Private Sub newTBox_Change()
If Len(newTBox.Text) > 3 Then '当输入的字符数超过4时执行以下操作:
Select Case CLng(Right(newTBox.name, 1))
Case 1, 3
MsgBox newTBox.name & "已更改(" & newTBox.Text & ")"
Case 2, 4
MsgBox newTBox.name & "已更改其文本"
Case Else
MsgBox newTBox.name & "不同的文本..."
End Select
End If
End Sub
- 插入一个
Userform
并将以下代码复制到其代码模块中:
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) '在这里使用您打算创建的文本框的最大数量
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) '保留加载的数组元素
End Sub
- 现在,显示该表单并与新创建的5个文本框中的文本进行互动。
您可以按照以下方式显示其中一个实例:
a) 将其命名为 "frmTxtBEvents";
b) 使用以下 Sub
:
Sub ShowTheForm()
Dim frm As New frmTxtBEvents
frm.Show vbModeless
End Sub
当输入4个字符时,根据最后一个文本框的名称数字,它们的 Change
事件将显示特定的消息框...
如果有任何不清楚的地方,请随时提出澄清。但是,我所在的国家现在已经很晚,(今天) 我只能再工作半个小时。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论