Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating through a ComboBox's values
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating through a ComboBox's values
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating through a ComboBox's values
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating through a ComboBox's values
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating through a ComboBox's values
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating through a ComboBox's values
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating through a ComboBox's values
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[s] being treated as a single word. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Iterating through cells, comparing values | Excel Programming | |||
Offset & Combobox's returned Value | Excel Programming | |||
Referring to OLEObjects (combobox's on worksheet) | Excel Programming | |||
checking for errors in combobox's | Excel Programming |