+ Reply to Thread
Results 1 to 9 of 9

Assign macro to combo box

  1. #1
    Registered User
    Join Date
    09-07-2011
    Location
    Californina
    MS-Off Ver
    Excel 2003
    Posts
    19

    Assign macro to combo box

    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.
    Attached Files Attached Files
    Last edited by Mike Stephens; 09-08-2011 at 05:45 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Assign macro to combo box

    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.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    09-07-2011
    Location
    Californina
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Assign macro to combo box

    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.

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Assign macro to combo box

    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.

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Assign macro to combo box

    You don't need code for each sheet

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Registered User
    Join Date
    09-07-2011
    Location
    Californina
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Assign macro to combo box

    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.

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Assign macro to combo box

    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

  8. #8
    Registered User
    Join Date
    09-07-2011
    Location
    Californina
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Assign macro to combo box

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

  9. #9
    Registered User
    Join Date
    09-07-2011
    Location
    Californina
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Assign macro to combo box

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1