VB Drop down menu Code in module called from multiple Excel Sheets

[kawi6rr]

I have an Excel Workbook with many sheets. I’m using a dropdown menu to navigate to each sheet based on the selection. What I would like to do is set this code in a module and call is from each page like so “Call Module1”. I keep getting this error when trying to call the module.

Error: Expected variable or procedure, not module

Name: ComboBox1_Change()

Code In Module

Please Login or Register  to view this content.

I’ve tried renaming the combobox in the module to ComboBox and calling it.
Call ComboBox
I’ve tried calling the module
Call Module1

Thanks

[AlphaFrog]

Qualify the combobox's worksheet.

Please Login or Register  to view this content.

[6StringJazzer]

You can't use a variable for a procedure name then call it.

You also can't call a module.

You can only call a procedure (Sub or Function) within a module.

What exactly are you trying to call? What do you want to do that is different from what your code currently does?

[6StringJazzer]

Qualify the combobox's worksheet.

Please Login or Register  to view this content.

I am unclear on the question so I am unclear on this solution. If the Sub ComboBox1_Change is getting fired, then that means that the name ComboBox1 should be directly visible in the code module without having to qualify it with the sheet name. That is, this code must already be in Sheet1 so the qualification should be superfluous. Unless you are understanding the question in a way that I am not getting.

[AlphaFrog]

Unless you are understanding the question in a way that I am not getting.

I read it as the OP moved the code to a standard module and is calling it like a regular procedure. If that's the case, the combobox's sheet needs to be qualified.

[6StringJazzer]

I read it as the OP moved the code to a standard module and is calling it like a regular procedure. If that's the case, the combobox's sheet needs to be qualified.

Aha. If that is the case then, I agree, and also the sub will have to be changed from Private to Public. The call from the sheets would be

Please Login or Register  to view this content.

[kawi6rr]

What I’m trying to do is only call one piece of code from many pages(sheets). I’ve built a report navigation(drop down menu) on each sheet but I don’t want all that code on each sheet, the example I gave above is 4 of the 54 departments. Each sheet will have a drop down menu and I would like to call the module to trigger the drop down menu.

Sheet2
Call Module1.ComboBox1_Change
Sheet3
Call Module1.ComboBox1_Change
Sheet4
Call Module1.ComboBox1_Change
Etc…

Please Login or Register  to view this content.

I qualified the code as you suggested but I get the following error now.

Error: Subscript out of range

Please Login or Register  to view this content.

[kawi6rr]

I think I need to pass a value from the comboBox1_Change on each sheet.

Call Module1.ComboBox1_Change("1 Diamond Head")

But can't get it to work.

[Fluff13]

try on each sheet

Please Login or Register  to view this content.

and then in a regular module

Please Login or Register  to view this content.

[kawi6rr]

Thanks so much that works!

[Fluff13]

Glad to help & thanks for the feedback