Ask Experts Questions for FREE Help !
Ask
    kyhelpdesk's Avatar
    kyhelpdesk Posts: 1, Reputation: 1
    New Member
     
    #1

    Oct 1, 2008, 10:45 AM
    Excel 2003 Macro VBA Hide / Unhide Multiple Sheets
    I'd like to assign a macro button to hide / unhide a range of sheets in a workbook.

    I have tried various codes from web but have not been successful. (acutally one of the codes worked if I selected 'run' from the macro menu but does not run if button is clicked).

    I have tried 'recording' and assigning to button, but still, button does not function. I cannot tell if it is an poorly written macro or an issue with, for some reason, my button is not enabled (BTW, it shows enabled in properties).

    For this example lets use sheets labeled: JAN, FEB, MAR, APRIL, MAY, JUNE. What would code look like if I want to hide FEB - APRIL & leave others visible. Then, unhide same list.

    I don't know much about VB but can folllow the logic of the code.

    Thanks,
    KhD
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    Oct 1, 2008, 03:54 PM

    1. Use the Control Toolbox toolbar to create a Command Button.
    2. Right-click the new button, and select Properties
    3. Put a new CAPTION called TURN FEB OFF
    4. Close the Properties Window
    5. Now, let's have some fun: Right-click the new button again and select View Code
    6. A blinking cursor is ready for your new code. Add the code below:
      Code:
              Application.ScreenUpdating = False
          If CommandButton10.Caption = "TURN FEB OFF" Then
              CommandButton10.Caption = "TURN FEB ON"
              CommandButton10.ForeColor = &HFFFF&
              Sheets("FEB").Visible = False
          Else: CommandButton10.Caption = "TURN FEB OFF"
              CommandButton10.ForeColor = &HC0&
              Sheets("FEB").Visible = True
    7. Edit the CommandButton10 to match the numeric value of your new button so it works...edit all the references.
    8. Now click the Exit Design Mode button on the Control Toolbox toolbar.
    9. Try clicking your new button


    If this works to your satisfaction, edit the VBA above to do all the sheets, or make individual buttons. Your call.
    stevesmith27's Avatar
    stevesmith27 Posts: 3, Reputation: 1
    New Member
     
    #3

    Mar 13, 2011, 03:40 AM
    Thank you for the code.

    Being new to Excel and using codes.

    What do you mean by;
    Edit the CommandButton10 to match the numeric value of your new button.

    Look forward to your reply.

    Steve
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #4

    Mar 13, 2011, 04:46 AM

    Each time you add a new CommandButton to your form or worksheet, it is given a default name. The names are sequential, the first button added is CommandButton1, then CommandButton2, etc.

    In my sample code above I demonstrated how the Caption (text showing on the button) can be tested/evaluated and then the button code can do specific things based on what that caption is. In this way, instead of having two buttons, one to hide and one to unhide a sheet, you can have a single button that "toggles" and does both jobs.

    Anyway, the sample code was showing how to edit the CommandButton10, and I was simply pointing out that when you edit the code for any particular CommandButton, make sure you reference all the commands in the macro TO the correct button by changing the 10 to the correct index number o the button being worked on.
    stevesmith27's Avatar
    stevesmith27 Posts: 3, Reputation: 1
    New Member
     
    #5

    Mar 13, 2011, 06:18 AM
    Comment on JBeaucaire's post
    You are a real star. It worked 1st time.
    I am sure I will will have more question, but...

    Thank you very much

    Steve
    stevesmith27's Avatar
    stevesmith27 Posts: 3, Reputation: 1
    New Member
     
    #6

    Mar 14, 2011, 12:10 AM
    Comment on JBeaucaire's post
    Now I have the buttons working to hide/unhide worksheets. Next can I have the same button not only unhide but also at the same time go to a 2nd worksheet, then another button on the now open worksheet to close and return to the 1st worksheet

    Cheers

    Steve

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Auto-Macro in Excel [ 4 Answers ]

Hi, I have a lot of macros built-in Excel, but when I want to update the certain data , I have to run them individually each time. Do you know anyway I can auto-run these macros? Thanks for any help.

Start excel with a macro [ 3 Answers ]

How can I start excel with a macro.

How to Automate a macro in excel [ 4 Answers ]

A program logs files at random in txt. abc_log.txt (example) When new info. Is added to the txt file,manualy activate macro and it does what it suppose to do, all OK there. Is there a way a macro code or excel, or perhaps a BAT program can be made to simply "detect" when new data is added...


View more questions Search