Iterating through a ComboBox's values

[kittronald]

If ComboBox1 contains multiple values, how could you programmatically
iterate through each one as if you were selecting each value manually with
the mouse ?

For example, in order, the values for ComboBox1 are Apple, Banana and
Orange.

A Save_File macro saves Sheet1 with the name equal to ComboBox1's first
value = Apple.

After the macro runs, ComboBox1's next value, Banana, is selected.

The Save_File macro runs again and saves Sheet1 as ComboBox1's next
value = Banana.




- Ronald K.

 

[GS]

Example...

Sub IterateComboboxList()
Dim i As Integer
For i = 0 To Sheets(1).ComboBox1.ListCount - 1
Debug.Print Sheets(1).ComboBox1.List(i)
Next 'i
End Sub

Results in Immediate Window:

Apple
Banana
Orange

 

[GS]

A tad more efficient...

Sub IterateComboboxList()
Dim i As Integer
With Sheets(1).ComboBox1
For i = 0 To .ListCount - 1: Debug.Print .List(i): Next 'i
End With 'Sheets(1).ComboBox1
End Sub

 

[kittronald]

Garry,

Thanks for the quick response.

I'm putting together an automated routine that goes something like this:

Sub Macro_Automate()
Dim i As Integer
With Sheets("Settings").ComboBox2
For i = 0 To .ListCount - 1: Debug.Print .List(i):
********* Set "Data_Type" name to the text value equal to the current
ComboBox2 i position *********
Macro_Change_Function
Macro_Export_Output
Next 'i
End With
End Sub


How can I get the named range "Data_Type" to be set before
Macro_Change_Function runs ?




- Ronald K.

 

[GS]

kittronald formulated the question :

Garry,

Thanks for the quick response.

I'm putting together an automated routine that goes something like this:

Sub Macro_Automate()
Dim i As Integer
With Sheets("Settings").ComboBox2
For i = 0 To .ListCount - 1: Debug.Print .List(i):
********* Set "Data_Type" name to the text value equal to the current
ComboBox2 i position *********
Macro_Change_Function
Macro_Export_Output
Next 'i
End With
End Sub


How can I get the named range "Data_Type" to be set before
Macro_Change_Function runs ?




- Ronald K.

You're welcome!

Try this...

Sub Macro_Automate()
Dim i As Integer
With Sheets("Settings").ComboBox2
For i = 0 To .ListCount - 1
Sheets("Settings").Range("Data_Type") = .List(i)
Call Macro_Change_Function: Macro_Export_Output
Next 'i
End With
End Sub

 

[kittronald]

Garry,

Hey, that works very nice !

So using CALL allows you to run macros in serial fashion and the colon
acts as a multi-value delimiter.

This is a real time saver as I can now walk away for a few hours instead
of having to babysit this workbook and manually run each iteration of the
macro every 40 minutes.

Thanks again !



- Ronald K.

 

[GS]

kittronald presented the following explanation :

Garry,

Hey, that works very nice !

So using CALL allows you to run macros in serial fashion and the colon
acts as a multi-value delimiter.

This is a real time saver as I can now walk away for a few hours instead
of having to babysit this workbook and manually run each iteration of the
macro every 40 minutes.

Thanks again !



- Ronald K.

Ron,
You're welcome, ..again!<g>

Just to be clear, the 'Call' statement is well defined in the online
help but I used it here because of the colon I used to separate the
executables. Thus, the colon is a line-delimiter...

Without colon:
Sub Macro_Automate()
Dim i As Integer
With Sheets("Settings").ComboBox2
For i = 0 To .ListCount - 1
Sheets("Settings").Range("Data_Type") = .List(i)
Call Macro_Change_Function
Macro_Export_Output
Next 'i
End With
End Sub

With colon:
Sub Macro_Automate()
Dim i As Integer
With Sheets("Settings").ComboBox2
For i = 0 To .ListCount - 1
Sheets("Settings").Range("Data_Type") = .List(i):
Macro_Change_Function: Macro_Export_Output
Next 'i
End With
End Sub

**Note here that the 'Call' statement is not used AND the single line
wraps here. In an effort to make it NOT wrap in the viewer I had to put
the wrapped line (calls to 2 macros) as a separate line. Because these
calls were separated by the colon, I had to use 'Call' so VB[A]
wouldn't interpret the first call as a label. This would have happened
because of the macro name being treated as a single word.