vba - Excel VBA 值动态命名文本框

Question

底线修复:将动态命名的文本框更改为在列和行之间添加 _ 分隔符，以消除名称的歧义。

以前的代码:

Set cCntrl = PickTicketForm.Controls.Add("Forms.TextBox.1", "PalletNumber"& i & r, True)

修复:
Set cCntrl = PickTicketForm.Controls.Add("Forms.TextBox.1", "PalletNumber"& i & "_"& r, True)

<小时/>

我有一个用户表单，它有 15 个文本框，每列 X 行(动态)。

用户在文本框中输入数字。然后我希望他们点击用户表单上的 Print 按钮来运行 PrintLabel() 子函数并将这些值垂直放入电子表格中 (B24:Bxx)。然后让它打印出电子表格并返回到用户表单。

我的问题是我似乎无法从文本框中获取值。

文本框名称采用多维数组样式格式:

托盘编号&“行”&“列”

因此第一行将是 PalletNumber0_0 到 PalletNumber0_15。下一行将是 PalletNumber1_0 到 PalletNumber1_15。

更新:

用户在文本框中输入值“1234”，然后单击“查找”来运行 lookup()。然后在电子表格中搜索该数字并获取所有匹配的行并将它们放入用户表单中。

这是代码片段

For Each c In Worksheets("Sheet1").range("A2:A" & iRowCount)
  If c.value = OrderNumber Then
    ReDim Preserve aGetData(6, i)

    For a = 0 To 6 'Change this for total of columns Our first index will hold each col of data that is why
                   'it is set to 0 (arrays start at a base of zero, so
                   '0,1,2,3,4,5 will be each col(A,B,C).
      aGetData(a, i) = c.Offset(0, a) 'This gets each value from col A,B and C

    Next a


      'Get the data and set it into variables.
      ItemNumber = aGetData(5, i)
      ItemQty = aGetData(2, i)

      'Create "ItemQuantity" text box.
       Set cCntrl = PickTicketForm.Controls.Add("Forms.Label.1", "ItemQuantity" & i, True)
        With cCntrl
            .Caption = ItemQty
            .Width = 85
            .Height = 18
            .Top = 86 + (i * 20)  
            .Left = 40
            .TextAlign = 1 'Left
            .Font.Name = "Arial Black"
            .Font.Size = "10"
            .BackColor = BackgroundColor
        End With

      'Create "ItemNumber" box
       Set cCntrl = PickTicketForm.Controls.Add("Forms.Label.1", "ItemNumber" & i, True)
        With cCntrl
            .Caption = ItemNumber
            .Width = 340
            .Height = 18
            .Top = 86 + (i * 20)
            .Left = 86
            .TextAlign = 1 'Left
            .Font.Name = "Arial Black"
            .Font.Size = "10"
            .BackColor = BackgroundColor
        End With

    'Create each inputbox for the pallets.
    For r = 0 To 14
        Set cCntrl = PickTicketForm.Controls.Add("Forms.TextBox.1", "PalletNumber" & i & "_" & r, True)
            With cCntrl
                .Width = 28
                .Height = 18
                .Top = 86 + (i * 20)
                .Left = 354 + (r * 30) 
                .TextAlign = 1 'Left
                .Font.Name = "Arial Black"
                .Font.Size = "10"
                .BackColor = BackgroundColor
                .Text = i & r
            End With
    Next r               

    i = i + 1 'Increment for array in case we find another order number
              'Our second index "aGetData(index1,index2) is being resized
              'this represents each order number found on the sheet

    LineCount = LineCount + 1

    'Set the scroll bar height for the final form.
    ScrollBarHeight = 150 + (i * 20)

  End If

Next c

这是代码片段:

'Loop through first column completely ( 0 - 5 to test)
' i = 0 means go through every box in column 0 first
For i = 0 To 5
    'Loop through rows.
    For r = 0 To 2 '( 0 - 2 rows to test)
        ActiveCell.Offset(i, 0).value = PickTicketForm.Controls("PalletNumber" & i & r).Text
    Next r
Next i

这是用户表单布局:

电子表格输出应如下所示:

Answer 1

您还应该在 For i = 0 To 5 循环中添加控件。如果它仍然为空，则在搜索 PalletNumber00、PalletNumber01、PalletNumber02 等时添加 PalletNumber0、PalletNumber1、PalletNumber2 等。