Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro to Hide Sheets and Show Others on Button Click

0

I created 1 workbook with 5 Sheets and protected with password.

I want the following  activity.

If i open that file (i) it must open with sheet 1 only, others hidden.

(ii) In Sheet 1 how to create command button(s) 

(iii) If i click command button1 in sheet 1, Sheet2 and Sheet3 only shown and others hidden.

(iv) If i click command button2 in sheet 1, Sheet4 and Sheet5 only shown and others hidden.

(v) in all sheets create 1 command button( namly HOME), if i click that button(HOME) it shows Sheet1 only all others hidden.

    How to do this?

    Please give detailed answers,because i don't know macros and VB.

Please give answer very soon.

Thanks in advance.

excelmacros-vba
MS Office Version: 2016
posted by: brsapril1978 (rep: 8) on Fri Oct 7, 2016 at 11:40 am
Post Edited
Title: Title was not descriptive.
Answer
Discuss

Answers

0
Selected Answer

First, follow this tutorial (one of ours) in order to make the first sheet only appear when the file is opened.

As far as the macro to show and hide the sheets you just need two macros like this:

Sub macro_1()

'sheets you want to hide
Sheets("Name of Sheet").Visible = xlVeryHidden
Sheets("Name of Sheet 2").Visible = xlVeryHidden

'sheets you want to show
Sheets("Name of Sheet").Visible = True
Sheets("Name of Sheet 2").Visible = True

End Sub

The first one should show the frist two sheets you want and hide the others and then just copy and paste that macro and switch the True and xlVeryHidden parts to hide the first two and show the others.

To make the macro to go back to the Home worksheet make one more macro like the above macro but hide all of the sheets and show the Home worksheet.

Then, follow this tutorial to attach macros to a button. (one of ours)

posted by: don (rep: 1989) on Fri Oct 7, 2016 at 1:31 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login