Control Arrays in VBA UserForms 1

[Bowers74]

See my latest FAQ!

faq707-4085

Enjoy!! ;-)

I hope this helps!!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[deadzone285]

Thanks, This was Realy Helpful!

 

[CajunCenturion]

Control Arrays are indeed a powerful construct and being missing in VBA is a deficiency. But as Bowers74 has indicated, there are ways to simulate their behavior as nicely described in the FAQ.

But you can extent the power of the simulation by taking advantage of the Name key of the Controls collection which provides two additional benefits, that being that becomes easier to simulate multiple control arrays of the same type along with scalars all on the same form, and you do not have to pay the overhead of traversing the entire controls collection each time you wish to perform an array type operation, and performing conditional checks to insure that you’re dealing with the correct control. Suppose that you need 7 textboxes on your form, 3 to simulate an array of names, 3 to simulate an array of visit counts, and 1 as a scalar textbox to hold the total number of visits. The key to making this work is by applying some discipline into the naming of the controls.

Name the 3 textboxes for name as ‘txtName1’, ‘txtName2’, and ‘txtName3’
Name the 3 textboxes for visits as ‘txtVisit1’, ‘txtVisit2’, and ‘txtVisit3’
Name the total visits textbox as ‘txtTotalVisits’

Just to show how the simulation works, drop in two command buttons (cmdInitTexts and cmdAddVisits) and the following code snippets:

Private Sub cmdInitTexts_Click()

   InitTextBoxes "txtName", "First Name 1", "First Name 2", "First Name 3"
   InitTextBoxes "txtVisit", "0", "0", "0"

End Sub

Private Sub cmdAddVisits_Click()

   txtTotalVisits = TallyTextBoxes("txtVisit")

End Sub

Public Sub InitTextBoxes(TextBoxArray As String, ParamArray InitValue())

   Dim Idx As Integer

   For Idx = 1 To 3
      Me.Controls(TextBoxArray & Trim(Idx)) = InitValue(Idx - 1)
   Next Idx

End Sub

Private Function TallyTextBoxes(TextBoxArray As String) As Long

   Dim Idx As Integer
   Dim Total As Long

   Total = 0
   For Idx = 1 To 3
      Total = Total + Me.Controls(TextBoxArray & Trim(Idx))
   Next Idx

   TallyTextBoxes = Total

End Function

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein