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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
Solution
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.
 
Upvote 0
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 :)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top