Assign macro to combo box

**Mike Stephens** · 09-07-2011, 04:29 PM

Hi,

I am creating a worksheet that will have many, many sheets. I want to keep them hidden except when user determines to unhide with a combo box. The macros work fine, but I cannot get the combo box to control the macros based on the combo box selection. I have attached a sample workbook of what I am trying to accomplish. The buttons launch the macros fine, but can anyone help me figure out how to do the same with the combo box? Please keep in mind that this is my first excel project with macros. I also welcome any suggestions with the code for my macros as well.

Thanks.

**Leith Ross** · 09-07-2011, 07:30 PM

Hello Mike Stephens,

Welcome to the Forum!

This macro will do what you want. Copy and paste this to either Sheet1 or to a new VBA module then assign the macro to the drop down.

Please Login or Register  to view this content.

**Mike Stephens** · 09-07-2011, 08:19 PM

Thanks Leith,

It works, but it only hides the sheets. It does not unhide them. For example, when I click sheet2 it hides sheet2, when I click sheet3 it hides sheet3. What I want it to do is when I click sheet 2 it unhides sheet2 and hides the others. When I click sheet3 it unhides sheet3 and hides the others. I wrote the macros to do this and they are assigned to the buttons on the workbook, but I don't know how to get the combo box to call these macros. Does your macro make the combo box call my macros, or does it do something different all together? Maybe I am going about this the wrong way. If I am can someone steer me in the right way? Sorry if I sound macro ignorant, it is only because I am.

Thanks for the help.

**Leith Ross** · 09-07-2011, 08:40 PM

Hello Mike Stephens,

Here is the macro changed to call the other macros based on the Drop Down selection.

Please Login or Register  to view this content.

**royUK** · 09-08-2011, 02:33 AM

You don't need code for each sheet

Please Login or Register  to view this content.

**Mike Stephens** · 09-08-2011, 10:15 AM

Thanks so much Leath and Roy. Both work great. But I'm curious about something. Roy, you mentioned that your code does not require a macro for each sheet. Is there a reason why doing it that way is better? Is there something wrong with using a macro for each sheet? Why would one be better than the other? The reason I'm asking is they both do exactly the same thing with no problems.

**royUK** · 09-08-2011, 02:43 PM

Why write macros for each sheet? If you add sheets then you need to write another macro & what's the point in several lines of code that do in effect the same action

**Mike Stephens** · 09-08-2011, 05:44 PM

Great point and thanks for all the help. You guys really made my life easier.

**Mike Stephens** · 09-09-2011, 02:18 PM

I didn't notice right away, but I am getting one error with RoyUK's code. The All_Unhide macro returns the following error:

Run-time error '1004': Method 'Visible' of Object '_Worksheet' failed.

The highlighted error in Debug: ws.Visible = xlSheetHidden

Does anyone know what causes this and how I can fix it? I tried to figure it out myself, but couldn't. Here is the code:

Please Login or Register  to view this content.

Thanks

Assign macro to combo box

LinkBack

Thread Tools

Rate This Thread

Display

Assign macro to combo box

Re: Assign macro to combo box

Re: Assign macro to combo box

Re: Assign macro to combo box

Re: Assign macro to combo box

Re: Assign macro to combo box

Re: Assign macro to combo box

Re: Assign macro to combo box

Re: Assign macro to combo box

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions