How not to use ComboBox change event?

[Waimea]

Hi,

I want to use a select case for the value of a combobox but I don't want to use the combobox change event?

Ex. If combobox1.ListIndex = 1 I want a macro to run, and if combobox1.listindex = 2 I want another macro to run?

 

[My Aswer Is This]

So the Countries will be a named range call "Countries"

If I select Germany in the combobox1 you want all the values in a range named Germany loaded into Combobox2 is this correct?

 

[My Aswer Is This]

I'm trying to help.

If I understand you have a named range on your worksheet named Countries

And then you have named ranges for cites in that county


So if you choose Germany from listbox one you want all the values in Named Range Germany loaded into Listbox2

Is this correct?

 

[My Aswer Is This]

If my assumptions are correct and all your named ranges are on the same sheet with the listbox's then you do not need to use select case.

Just use these two scripts.

Private Sub ComboBox1_Click()
'Modified  12/24/2018  1:04:09 AM  EST
On Error GoTo M
Dim ans As String
ans = ComboBox1.Value
ComboBox2.Clear
ComboBox2.List = Range(ans).Value
Exit Sub
M:
MsgBox "You have no range named  " & ans
End Sub
Private Sub CommandButton1_Click()
'Modified  12/24/2018  1:04:09 AM  EST
'This script loades the range named "Countries" into Listbox1
With ComboBox1
.Clear
.List = Range("Countries").Value
End With
End Sub

 

[Waimea]

Hi My Aswer Is This,

I don't have any listboxes in this sheet. I have two comboboxes and depending on the value of combobox1.listindex and combobox2.listindex I want different macros to run.

I think that nested select case is my best option!

Merry Christmas!

 

[My Aswer Is This]

Hi My Aswer Is This,

I don't have any listboxes in this sheet. I have two comboboxes and depending on the value of combobox1.listindex and combobox2.listindex I want different macros to run.

I think that nested select case is my best option!

Merry Christmas!

I never mentioned listboxes.

 

[Waimea]

Hi,

you are right, I read your reply to fast.

 

[My Aswer Is This]

You mentioned countries and cities.

So if user chooses Sweden then Oslo what Macro would then to run.

Not sure why it would take 20 some options and 360 other options to run a script.

 

[Waimea]

If there is a better way then writing 320 macros or 320 calls to the same macro with multiple parameters I am very interested.

When I select combobox1.listindex = 1 and combobox2.listindex = 1 then a macro runs that calculates the difference between 40 cities. So for listindex = 1 I have 1600 rows (40*40) that I calculate in batches of 40 and then returns a top5 list with cities in that country.

 

[My Aswer Is This]

I believe I saw a question nearly a week ago asking about batches of 40. Which I never got involved with.

Needing 320 macros to perform a task would surely not be a way to do things.

I asked:

So if user chooses Sweden then Oslo what Macro would then need to run.

And you did not answer that question. Would the name of the script be Oslo??

And you mentioned each city had it's own named Range

That's a lot of named ranges.

Have you ever shown us the script you want to run?


​

 

[Waimea]

You are correct, I asked about batches of 40 and coloring them differently based on min and max values.

I haven't completed the script I want to run, it is work in progress so I am not sure what happens when you select Oslo or Helsinki.

I think it could be a combination or the 40 batches script that colors cells based on value and perhaps a pivot table.