Iterating through a ComboBox's values

[kittronald]

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.

 

[kittronald]

Once again, hitting the Send button caused the solution to eventually
appear.

Never mind.



- Ronald K.

 

[GS]

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.

 

[Andrew]

    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)

 

[kittronald]

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.

 

[kittronald]

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.

 

[GS]

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.