Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating through a ComboBox's values
I'm using a macro that iterates through a ComboBox's values and runs two
macros during each pass. However, the iteration doesn't appear to actually select the ComboBox's values on the worksheet. This is a problem because one of the macros depends on the ComboBox's linked cell value to change. For example: 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 How can I get this code to actually select the ComboBox's value so its linked cell's value changes before the macros execute. - Ronald K. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating through a ComboBox's values
Once again, hitting the Send button caused the solution to eventually
appear. Never mind. - Ronald K. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating through a ComboBox's values
kittronald presented the following explanation :
I'm using a macro that iterates through a ComboBox's values and runs two macros during each pass. However, the iteration doesn't appear to actually select the ComboBox's values on the worksheet. This is a problem because one of the macros depends on the ComboBox's linked cell value to change. For example: 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 How can I get this code to actually select the ComboBox's value so its linked cell's value changes before the macros execute. - Ronald K. I would change the ComboBox.ListIndex property so the linked cell changes, then run the macros. Optionally, you could use OnTime() or Sleep() to create a slight delay. -- 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
On Oct 21, 6:41*pm, "kittronald" wrote:
* * I'm using a macro that iterates through a ComboBox's values and runs two macros during each pass. * * However, the iteration doesn't appear to actually select the ComboBox's values on the worksheet. * * This is a problem because one of the macros depends on the ComboBox's linked cell value to change. * * For example: * * * * 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 * * How can I get this code to actually select the ComboBox's value so its linked cell's value changes before the macros execute. - Ronald K. Change your macros so that they accept an input argument, such as Macro1(X as variant) Then call your macro using the value of List(i) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating through a ComboBox's values
Garry,
Took a while to get back to this thread. The macro actually works with smaller data sets. Apparently, Excel runs out of memory and crashes before the macro finishes. Working with a smaller data set appears to fix the problem. Thanks for getting back to me. - Ronald K. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating through a ComboBox's values
Andrew.
The macro actually works with smaller data sets. Apparently, Excel runs out of memory and crashes before the macro finishes. Working with a smaller data set appears to fix the problem. Thanks for your idea. - Ronald K. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating through a ComboBox's values
kittronald presented the following explanation :
Garry, Took a while to get back to this thread. The macro actually works with smaller data sets. Apparently, Excel runs out of memory and crashes before the macro finishes. Working with a smaller data set appears to fix the problem. Thanks for getting back to me. - Ronald K. Yep! Normally large sets of data are processed in 'blocks' that are sized to run at decent performance without overloading resources. It's hard to say what size to use but generally 50% of RAM is a safe number. -- 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 a ComboBox's values | Excel Programming | |||
Iterating through cells, comparing values | Excel Programming | |||
Offset & Combobox's returned Value | Excel Programming | |||
checking for errors in combobox's | Excel Programming |