EXCEL 用户窗体 – 创建多个具有特定名称的标签和文本框

huangapple go评论79阅读模式
英文:

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

请测试以下场景:

  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
  1. 插入一个 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
  1. 现在,显示该表单并与新创建的5个文本框中的文本进行互动。

您可以按照以下方式显示其中一个实例:

a) 将其命名为 "frmTxtBEvents";

b) 使用以下 Sub

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

当输入4个字符时,根据最后一个文本框的名称数字,它们的 Change 事件将显示特定的消息框...

如果有任何不清楚的地方,请随时提出澄清。但是,我所在的国家现在已经很晚,(今天) 我只能再工作半个小时。

英文:

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.

huangapple
  • 本文由 发表于 2023年2月9日 01:52:17
  • 转载请务必保留本文链接:https://go.coder-hub.com/75389840.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定