Assign counting vba text boxes with counting values

[Neileee]

I am passing dates into variables labeled "Bx" using an increasing counter. There will be 45 of these variables so it will go Bx1 , Bx2, Bx3....Bx45.

Code for this part:

do while k<>46
Dim Bx(0 to 45)
blah blah blah
Bx(k) = blah blah blah
k = k + 1
loop


This works when defining variables but what if I want userform text boxes to named "Bx1, Bx2...Bx45" to display the value they've be defined with.

Currently the text boxes named Bx(1-45) have no values associated to them.

A tad confusing, this is the first time for me posting to an excel forum, so recommendations on how to clarify things is encouraged.

Thanks!

 

[HotRhodium]

Ok this is on a userform correct? If this was in a worksheet you could use the Worksheet.Shapes collection to set your text box object to each one but I don't see any collection like that in a userform. That means you will probably need to set them using something like this

Dim Bx As String
Bx = "blah blah blah"
 
 'blah blah blah
 Me.Bx1.Value = Bx
 Me.Bx2.Value = Bx
 'to Me.Bx45 Etc...

Please remember that the Dim Bx(0 To 45) line will repeat 45 times. This will cause an error. Maybe someone else will have a better idea to dynamically set these Text Boxes for you sorry.

 

[Neileee]

Unfortunately the whole point of using the counter is so that I don't have to write the code over and over again.

Thanks for your response though.

 

[HotRhodium]

FYI I will sometimes have a macro write a macro when it is too tedious or complicated for example putting this code in a worksheet.

Public Sub MacroMaker()
Dim i As Integer, j As Integer
Dim FirstStr As String, LastStr As String
 FirstStr = "Me.Bx"
 LastStr = ".Value = Bx"
 j = 1
 Me.Cells(j, 1) = "Public Sub MyFunction()"
 j = j + 1
 Me.Cells(j, 1) = "Dim Bx As String"
 j = j + 1
 Me.Cells(j, 1) = "Bx = ""Blah"""""
 For i = 1 To 45
  Me.Cells(i + j, 1) = FirstStr & i & LastStr
 Next i
 Me.Cells(j + i, 1) = "End Sub"
 j = j + 1
End Sub

Will put this macro into col A

Public Sub MyFunction()
Dim Bx As String
Bx = "Blah"""
 Me.Bx1.Value = Bx
 Me.Bx2.Value = Bx
 Me.Bx3.Value = Bx
 Me.Bx4.Value = Bx
 Me.Bx5.Value = Bx
 Me.Bx6.Value = Bx
 Me.Bx7.Value = Bx
 Me.Bx8.Value = Bx
 Me.Bx9.Value = Bx
 Me.Bx10.Value = Bx
 Me.Bx11.Value = Bx
 Me.Bx12.Value = Bx
 Me.Bx13.Value = Bx
 Me.Bx14.Value = Bx
 Me.Bx15.Value = Bx
 Me.Bx16.Value = Bx
 Me.Bx17.Value = Bx
 Me.Bx18.Value = Bx
 Me.Bx19.Value = Bx
 Me.Bx20.Value = Bx
 Me.Bx21.Value = Bx
 Me.Bx22.Value = Bx
 Me.Bx23.Value = Bx
 Me.Bx24.Value = Bx
 Me.Bx25.Value = Bx
 Me.Bx26.Value = Bx
 Me.Bx27.Value = Bx
 Me.Bx28.Value = Bx
 Me.Bx29.Value = Bx
 Me.Bx30.Value = Bx
 Me.Bx31.Value = Bx
 Me.Bx32.Value = Bx
 Me.Bx33.Value = Bx
 Me.Bx34.Value = Bx
 Me.Bx35.Value = Bx
 Me.Bx36.Value = Bx
 Me.Bx37.Value = Bx
 Me.Bx38.Value = Bx
 Me.Bx39.Value = Bx
 Me.Bx40.Value = Bx
 Me.Bx41.Value = Bx
 Me.Bx42.Value = Bx
 Me.Bx43.Value = Bx
 Me.Bx44.Value = Bx
 Me.Bx45.Value = Bx
End Sub

Obviously it is better to cycle through a collection of some sort but keep it in mind for situations like this

 

[HotRhodium]

I know this is an old post but I have found out more about this so I thought I could correct my previous post for future knowledge seekers. A userform does have a collection that includes text boxes but they are combined with everything on your form. So to do what you were wanting you could use something like this.

Sub InputBxData()
Dim LoopControl As Control
Dim TempLng As Long
Dim TempStr As String
 For Each LoopControl In Me.Controls
  If InStr(LoopControl.Name, "Bx") Then
   TempStr = Replace(LoopControl.Name, "Bx", "")
   If IsNumeric(TempStr) Then
    TempLng = CLng(TempStr) 'TempLng now includes Bx number
    'From here you can use TempLing to figure out what Bx you are working with
    'and the use LoopControl = "Value" to set values
   Else
    'No number in name or text also included fail test
   End If
  Else
   'No Match
  End If
 Next LoopControl
End Sub

Or even evaluate what type of control each one is and make separate collections for each control type. Hope this helps better late then never.

 

[Rick Rothstein]

I know this is an old post but I have found out more about this so I thought I could correct my previous post for future knowledge seekers. A userform does have a collection that includes text boxes but they are combined with everything on your form. So to do what you were wanting you could use something like this.

Sub InputBxData()
Dim LoopControl As Control
Dim TempLng As Long
Dim TempStr As String
 For Each LoopControl In Me.Controls
  If InStr(LoopControl.Name, "Bx") Then
   TempStr = Replace(LoopControl.Name, "Bx", "")
   If IsNumeric(TempStr) Then
    TempLng = CLng(TempStr) 'TempLng now includes Bx number
    'From here you can use TempLing to figure out what Bx you are working with
    'and the use LoopControl = "Value" to set values
   Else
    'No number in name or text also included fail test
   End If
  Else
   'No Match
  End If
 Next LoopControl
End Sub

Or even evaluate what type of control each one is and make separate collections for each control type. Hope this helps better late then never.

Is the idea here to assign the number from the name to TextBoxes whose name starts with "BX" (that is, if the TextBox name is BX23 then that TextBox would be made to display 23)? If so, here is a CommandButton Click event that will make those assignments...

'  Assumes the TextBoxes are consecutively
'  named BX1, BX2,...,BX44, BX45
Private Sub CommandButton1_Click()
  Dim X As Long
  For X = 1 To 45
    Controls("BX" & X).Value = X
  Next
End Sub