Excel code and macros

L

Legacy 489182

Guest
Hi,

I’m wanting to hide 5 worksheets within an excel document and then based on the value of a particular cell on my front sheet (sheet1) unhide certain sheets.

For example if the value in cell E10 on Sheet1 was 0 or blank I would want sheet1 visible.
If the value changed to 1 I would want only sheet1 and sheet2 visible. If this was changed to 2 I would want sheet1, sheet2 and sheet3 visible etc.

Is there a way of doing this? I’ve struggled to find an answer to this so any help would be appreciated!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,119
Office Version
  1. 2013
Platform
  1. Windows
We need more specifics.
What is the name of the sheet which you plan to use as your launch pad
Meaning the sheet where you plan to enter the value in Range("E10") to hide certain sheets.
And it's always easier to write a script if you provide specific sheet names.
Like sheet Named "Master" and sheet Named "Alpha and sheet Named "Bravo"
Since you should have a data validation list in Range("E10") I do not belie we can use nothing as a result. So lets not ever try using Blank unless you want to use "BlanK"
You will never be able to hide sheet named "Master" for example or you will not be able to enter a value in Range("E10"
And you said or 0 or blank You would have to delete a cell value already in range E10 to use blank a script cannot run when you have a empty cell because you cannot enter nothing

My script could work automatically when you enter a value in Range("E10") Of sheet named Master.

And I need to know all 5 sheet names

Try this modify as need and get back with me if you need more help:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  2/11/2022  7:40:20 AM  EST
If Target.Address = "$E$10" Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub

Dim i As Long
Select Case Target.Value
    Case 0: Sheets("Alpha").Visible = True
    Case 3: Sheets("Bravo").Visible = False
    
        Case 5
            Sheets("Alpha").Visible = True
            Sheets("Charlie").Visible = True
    
        Case 8
            ' Unhide sheets 1 to 5
            For i = 1 To 5
            Sheets(i).Visible = True
            Next
  
        Case 10
            'Make all sheets visible
            For i = 1 To Sheets.Count
                Sheets(i).Visible = True
            Next
    
         Case 12
            'Hide all sheets except sheet named "Master"
            For i = 1 To Sheets.Count
                If Sheets(i).Name <> "Master" Then Sheets(i).Visible = False
            Next
  
        End Select


End If
End Sub
 
Solution

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,119
Office Version
  1. 2013
Platform
  1. Windows
You answered this as a solution very quickly. Is that true this worked for you or did you make a mistake. Normally users would also say thanks for that it worked as needed.
 
L

Legacy 489182

Guest
L
Hi,

I’m wanting to hide 5 worksheets within an excel document and then based on the value of a particular cell on my front sheet (sheet1) unhide certain sheets.

For example if the value in cell E10 on Sheet1 was 0 or blank I would want sheet1 visible.
If the value changed to 1 I would want only sheet1 and sheet2 visible. If this was changed to 2 I would want sheet1, sheet2 and sheet3 visible etc.

Is there a way of doing this? I’ve struggled to find an answer to this so any help would be appreciated!
No longer required :)
 
L

Legacy 489182

Guest
You answered this as a solution very quickly. Is that true this worked for you or did you make a mistake. Normally users would also say thanks for that it worked as needed.
Yes, it did work when I modified, thank you. I planned to come back to thank you later when I had some free time and explain the modification I made. Needless to say I won’t be using this forum again if this is the sort of message you can expect when you don’t reply immediately saying thank you, people are busy and just because I didn’t respond straight away doesn’t mean I wasn’t going to.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,119
Office Version
  1. 2013
Platform
  1. Windows
Yes, it did work when I modified, thank you. I planned to come back to thank you later when I had some free time and explain the modification I made. Needless to say I won’t be using this forum again if this is the sort of message you can expect when you don’t reply immediately saying thank you, people are busy and just because I didn’t respond straight away doesn’t mean I wasn’t going to.
No I did not mean to be ugly. I could see you were new to the forum and I thought maybe you by accident clicked the solution button
 

Forum statistics

Threads
1,176,312
Messages
5,902,427
Members
434,975
Latest member
snoski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top