VBA: Hide 1 worksheet, Show 1 worksheet

[lawi227]

In cell F5 on worksheet "Instructions", I have a drop down menu. The user will either select "1" or "2". If the user selects "1", then I would like to hide the tab called "Org. 2 Calcs" and make sure the tab called "Org. 1 Calcs" is showing. If the user selects "2", then I would like "Org. 1 Calcs" AND "Org. 2 Calcs" to be showing.

Note: there are several other tabs in this workbook so the VBA coding cannot use and codes that "close all tabs".

Thanks!

 

[lawi227]

Thank you! But can you make one minor edit? When "2" is selected, I need to hide Sheets("Org. 1 Calcs"), and make Sheets("Org. 2 Calcs") visible. The current code makes Sheets("Org. 1 Calcs") AND Sheets("Org. 2 Calcs") visible.

Thanks!

Then you could put this in the code module for the Instructions sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("F5")) Is Nothing Then
        With Range("F5")
            If .Value = 1 Or .Value = 2 Then Sheets("Org. 1 Calcs").Visible = xlSheetVisible
            Sheets("Org. 2 Calcs").Visible = IIf(.Value = "2", xlSheetVisible, xlSheetHidden)
        End With
    End If
End Sub

 

[mikerickson]

Then use the IIf function for Sheet1 similar to how is it used for sheet2.

 

[lawi227]

Admittedly, I'm having some difficulty. I'm a novice when it comes to VBA and am learning. Can you post and provide a brief description? Thanks!

Then use the IIf function for Sheet1 similar to how is it used for sheet2.

 

[mikerickson]

Post the failing code that you tried.

 

[lawi227]

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}span.s1 {color: #011993}span.s2 {color: #000000}</style>Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("F5")) Is Nothing Then
With Range("F5")
If .Value = 1 Or .Value = 2 Then Sheets("LPA Tables & Graphs (1 org)").Visible = xlSheetVisible
Sheets("LPA Tables & Graphs (2 orgs)").Visible = IIf(.Value = "2", xlSheetVisible, xlSheetHidden)
Sheets("LPA Tables & Graphs (1 org)").Visible = xlSheetHidden
If .Value = 1 Then Sheets("SelectedList (Calcs Master)").Range("M:P").EntireColumn.Hidden = True
End With
End If
End Sub

I had trouble identifying where to add the "xlsheethidden" function for when the value = "2".

You'll notice I added another element to the code to hide a range when the value = 1 and changed the worksheet names. If I run the macro when "1" is entered, it hides columns M:P. However, if I type in "2" into F5, then columns M:P still remain hidden.

I think I need to add something like this:
If .Value = 2 Then Sheets("SelectedList (Calcs Master)").Range("M:P").EntireColumn.Hidden = False
Does that formula look correct? It works but I wanted to make sure I'm not overlooking an error that could occur.

Post the failing code that you tried.

 

[mikerickson]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("F5")) Is Nothing Then
        With Range("F5")
            Sheets("LPA Tables & Graphs (2 orgs)").Visible = IIf(.Value = "2", xlSheetVisible, xlSheetHidden)
            Sheets("LPA Tables & Graphs (1 org)").Visible = IIf(.Value = "1", xlSheetVisible, xlSheetHidden)
        End With
    End If
End Sub