How to access to all comboboxes on the form?

A

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
 
H

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
 
A

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
 
H

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.
 
H

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top