Hide Unhide sheet

[Abhijeet]

Hi
Please tell me when i open the file that time all sheets hide when i click on Playing Cricket then this sheet unhide only other sheets should be hide when i click on Sending Birthday Card then this sheet can unhide all other sheets should be hide.Please tell me

 

[Luke M]

You could do this with a generic private sub that does the work. Below, I show two examples of how you would write the other macros to utilize the ShowOnly macro.

Option Explicit

Sub Example1()
'Show only the Birthday sheet
ShowOnly ("Birthday")
End Sub
Sub Example2()
'Show only the IPL sheet
ShowOnly ("Ipl")
End Sub

'Here's the macro that does all the real work
Private Sub ShowOnly(shName As String)
Dim ws As Worksheet
Application.ScreenUpdating = False

'Need to first unhide, so that we don't
'cause an error by trying to hide all sheets
Worksheets(shName).Visible = True

For Each ws In ThisWorkbook.Worksheets
    ws.Visible = (ws.Name = shName)
Next ws
Application.ScreenUpdating = True
End Sub

 

[Abhijeet]

No this only show one sheet i want also shown my sheet1 when i click on playing cricket then that sheet should be unhide

 

[Luke M]

When you click on a button, how many sheets do you want visible?
If 1, the code I posted above is correct.
If 2, and the main sheet/dashboard should always be visible, we can add an If statement into the loop like

If ws.Name <> "Dashboard" Then ws.Visible = (ws.Name = shName)

 

[Abhijeet]

Hi
I have 10 sheets i want Sheet 1 Always Visible. can u please use this statement If ws.Name <> "Dashboard"Then ws.Visible = (ws.Name = shName) using my above file so i can understand & create 10 new sheets

 

[Smallman]

Abhijeet

To add to Luke's answer if you always keep the Dashboard sheet at the end of your Sheets then the following should be OK under the above conditions. Remember - Dashboard always on far right

I have included both conditions and a file to show how it may work.

Sub Hide()  'Hide all of the sheets which are hidden in an Excel file.
Dim i As Integer

    For i = 1 To Worksheets.Count - 1
        Sheets(i).Visible = False
    Next i
End Sub

and if you want to unhide.

Sub UnHide()  'Hide all of the sheets which are hidden in an Excel file.
Dim i As Integer

    For i = 1 To Worksheets.Count - 1
        Sheets(i).Visible = True
    Next i
End Sub

Take care

Smallman

 

[Abhijeet]

Hi
I attach file is show only One sheet when i click Back to Dashboard then go to Dashboard sheet but i want to always visible dasboard sheet then what should change in VB code

 

[Smallman]

Post 4 - Luke's answer will do that. Just open the file and place the line Luke suggested after the For Each line of the Private Sub ShowOnly(shName As String) procedure.

Take care

Smallman

 

[Abhijeet]

Ok but how to do that please give full VBA Code or upload the file

 

[Smallman]

Abhijeet

What are you going to do if your file needs further enhancement in the future? Learning the basics will help you adapt the code. As a suggestion there are lots of online vba courses.

Here is the file you requested.

Take care

Smallman

 

[Abhijeet]

Hi
Thanks for given this file. Your suggestion learn VBA course online i will learn from Chabdoo.org in soon

 

[Smallman]

Here is a link to Chandoo's vba course.

http://files.chandoo.org/free/VBAClasses-course brochure.pdf

For a small consideration, your skill level will go from good to great in a very small amount of time. You don't ever regret learning.

Take care

Smallman

 

[Abhijeet]

Here is a link to Chandoo's vba course.

http://files.chandoo.org/free/VBAClasses-course brochure.pdf

For a small consideration, your skill level will go from good to great in a very small amount of time. You don't ever regret learning.

Take care

Smallman

Hi Samallman are you learn VBA from Chnadoo.org or ?

 

[Smallman]

Abhijeet

I went to the University of London to learn vba. The Chandoo course looks to have all of the aspects of the course I studied. Some of the learning material is first rate and I don't say that lightly. The important thing to remember about the language is that it is so diverse that a course can only give you the building blocks. You will pick up the rest by using XL and by coming on forums like this one and answering and reading posts.

The good news is it is such an easy language to pick up if you are using XL all of the time.

Take care

Smallman