How to access to all comboboxes on the form?

[avkokin]

Hello.
Sorry, but I unknow how I can access to all ComboBox on my form. So, I
have the template with UserForm. This UserForm has 5 combobox. All
combobox has different names. I need to fill all these combobox some
value from one table, aside from first combobox. How can I do it?
Thank you very much.
For example, I can fill my one combobox:
Private Sub UserForm_Initialize()
Dim Source As Document
Dim SourceTable As Table
Dim cmb As ComboBox
Dim i As Long
Dim s As String
'Fill the first combobox
cmbOrder.List = Array("One", "Two", "Three", "Next", "Five", "Zero")
'get values from the table
Set Source = Documents.Open("E:\Backup_copy\Sport\table.doc",
ReadOnly:=True, Visible:=False)
Set SourceTable = Source.Tables(1)
'Filling one from others 4 combobox
With Me.cmbFood1
For i = 1 To SourceTable.Columns(1).Cells.Count
s = SourceTable.Columns(1).Cells(i).Range.Text
.AddItem Left(s, Len(s) - 2)
Next i
End With
Source.Close
End Sub

 

[Helmut Weber]

Hi Anton,

apart from other methods,
you can put your controls like comboboxes in an array,
but unlike in VB only at runtime in VBA.
Here is an example for optionbuttens:

Private Sub UserForm_Initialize()
' Count Optionbuttons
Dim oCnt As Control
Dim iCnt As Integer
For Each oCnt In Me.Controls
If TypeOf oCnt Is MSForms.OptionButton Then
iOpt = iOpt + 1
End If
Next
' create array of optionbuttons
ReDim ArrOpt(iOpt) As OptionButton
' assign each optionbutton
For Each oCnt In Me.Controls
If TypeOf oCnt Is MSForms.OptionButton Then
iCnt = iCnt + 1
Set ArrOpt(iCnt) = oCnt
' setting caption for testing
ArrOpt(iCnt).Caption = Format(iCnt, "Opt- 00")
End If
Next
End Sub

Note, that the "for each loop" over all controls,
seemably, processes them in the order they were created.
--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

 

[avkokin]

Hi Helmut. Thank you very much. I did it for my combobox's control
(the code is below). But I don't understand some part of this code,
for example: If TypeOf - what is this and what do it?
And how I can exclude first ComboBox from array? Thank you.

My code:
Private Sub UserForm_Initialize()
Dim iCmb As Integer
Dim oCmb As Control
Dim i As Integer
For Each oCmb In Me.Controls
If TypeOf oCmb Is MSForms.ComboBox Then
iCmb = iCmb + 1
End If
Next
' create array of comboboxes
ReDim ArrCmb(iCmb) As ComboBox
' assign each comboboxes
For Each oCmb In Me.Controls
If TypeOf oCmb Is MSForms.ComboBox Then
i = i + 1
Set ArrCmb(i) = oCmb
' setting caption for testing
ArrCmb(i).AddItem "item"
End If
Next
End Sub

 

[Helmut Weber]

Hi Anton,

this is the code of the userform:

Option Explicit

Private Sub UserForm_Initialize()
' -------------------- count comboboxes
Dim oCnt As Control ' any control
Dim iCmb As Integer ' counter for comboboxes
Dim x As Long
For Each oCnt In Me.Controls
' if the control is a combobox then count it
If TypeOf oCnt Is MSForms.ComboBox Then
iCmb = iCmb + 1
End If
Next

' --------------- dim array of comboboxes
ReDim ArrCmb(iCmb) As ComboBox
iCmb = 0
For Each oCnt In Me.Controls
' if the control is a combobox then
' assign the control to the array of comboboxes
If TypeOf oCnt Is MSForms.ComboBox Then
iCmb = iCmb + 1
Set ArrCmb(iCmb) = oCnt
ArrCmb(iCmb).Text = CStr(iCmb) ' for testing
End If
Next

' process each combobox except ArrCmb(1)
For x = 2 To iCmb
MsgBox ArrCmb(x).Text
Next

End Sub

Hope this helps.

 

[avkokin]

Thank you very much, Helmut!

 

[Helmut Weber]

Hi Anton,

still better:

Instead of
ReDim ArrCmb(iCmb) As ComboBox
use
ReDim ArrCmb(1 to iCmb) As ComboBox

which avoids creating an entry ArrCmb(0)

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP