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!

 

[mikerickson]

I'm not sure what kind of drop down this is, but the hide/unhide coding should be

Sheets("Org. 1 Calcs").Visible = xlSheetVisible
Sheets("Org. 2 Calcs").Visisble = IIf(DropDownValue = "2", xlSheetVisible, xlSheetHidden)

The remaining question is what kind of drop down is being used and how to determine the DropDownValue variable in the above.

 

[lawi227]

It's a data validation List. With "1" and "2"

 

[mikerickson]

What cell is this list validating?

 

[lawi227]

F10 = "1"
F11= "2"
On a worksheet called "LPA Categories"

What cell is this list validating?

 

[mikerickson]

Which of those cells controls the visibility?

If F10="1" and F11="2" what sheets do you want to see.
What about the other 3 possibilities (1/1, 2/2, 2/1)?

 

[lawi227]

I want to link it to the Instructions tab and cell I mentioned in my initial post. There is only 1 option: either the user will selected "1" or "2".

Which of those cells controls the visibility?

If F10="1" and F11="2" what sheets do you want to see.
What about the other 3 possibilities (1/1, 2/2, 2/1)?

 

[mikerickson]

In which cell will the user make that selection.

I have the impression that you have a cell (perhaps more) that have List Validation set on them so that the use can only enter 1 or 2 into that cell (or cells).

Post 5 sounds like you have that same list validation on those both F10 and F11, or are they the source of the Validation list. If so, then what cell is had the validation in it. Which cell does the user change to change the sheet visibility.

 

[lawi227]

In cell F5 on a tab labeled "Instructions" is where the number is entered by selecting 1 or 2 from the drop-down list.

In which cell will the user make that selection.

I have the impression that you have a cell (perhaps more) that have List Validation set on them so that the use can only enter 1 or 2 into that cell (or cells).

Post 5 sounds like you have that same list validation on those both F10 and F11, or are they the source of the Validation list. If so, then what cell is had the validation in it. Which cell does the user change to change the sheet visibility.

 

[mikerickson]

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