HIDE / UNHIDE sheets based on user answers

[Jonatas Vasconcellos]

I would like to know how to hide / unhide sheets based on user answers in a
"main menu" sheet. Example: There are 2 sheets, "May" and "June", initially
hidden. In the "main menu" sheet (which is always visible), the user can type
either "show" or "hide" after each sheet name. After clicking on a "refresh"
button, the workbook refreshes showing or hiding the selected worksheets. I
am trying to use the following code, but it doesn't work. Hope you can help
me with this issue.

Range("H7").Activate
If Activecell.Value = "show" Then
Workbook.Sheets("May").visible = true
Else
Workbook.sheets("May").visible = false
End if
Range(ActiveCell.Offset(1,0)).Activate
If activecell.value = "show" then
Workbook.sheets("June").visible = true
Else
Workbook.sheets("June").visible = false
End if

 

[Bob Phillips]

With Range("H7")

If .Value = "show" Then
Workbook.Sheets("May").Visible = xlSheetVisible
Else
Workbook.Sheets("May").Visible = xlSheetHidden
End If

With .Range(.Offset(1, 0))

If .Value = "show" Then
Workbook.Sheets("June").Visible = xlSheetVisible
Else
Workbook.Sheets("June").Visible = xlSheetHidden
End If
End With
End With

 

[Mike H]

Maybe this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H7,H8")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Select Case UCase(Range("H7").Value)
Case Is = "SHOW"
Sheets("May").Visible = True
Case Is = "HIDE"
Sheets("May").Visible = xlVeryHidden
End Select

Select Case UCase(Range("H8").Value)
Case Is = "SHOW"
Sheets("JUNE").Visible = True
Case Is = "HIDE"
Sheets("JUNE").Visible = xlVeryHidden
End Select
End If
End Sub

Mike

 

[Joel]

Try this

If Range("H7").Value = "show" Then
For Each sht In Sheets
If sht.Name = "May" Then
sht.Visible = True
Exit For
End If
Next sht
Else
Workbook.Sheets("May").Visible = False
End If

If Range("H8").Value = "show" Then
For Each sht In Sheets
If sht.Name = "June" Then
sht.Visible = True
Exit For
End If
Next sht
Else
Workbook.Sheets("June").Visible = False
End If

 

[Jonatas Vasconcellos]

Thanks guys, but after trying each and every one of the alternatives, none of
them worked out. I am an Excel Programming begginer, so please understand.
Where exactly should I enter the code? How can I create a refresh button? I
am not sure that these codes work when, after unhiding some sheets, the user
wants to hide tham again just by typing hide. How can I do something like
that?

 

[Joel]

The first thing is to get the worksheet change function working. I would add
a msgbox for testing purposes. There are three different type VBA Code
sheets in Excel

1) ThisWorkBooK
2) Sheets (one for every worksheet)
3) Modules

The Worksheet Change has to be in the VBA sheet for the sheet where you are
making the cahnge. the best way of geting there is to Right Click the Tab on
the bottom of the worksheet (normally sheet1) and select VIEW CODE. Then put
the routine in the VBA window.

Private Sub Worksheet_Change(ByVal Target As Range)

msgbox("worksheet Changing is working")
If Not Intersect(Target, Range("H7,H8")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Select Case UCase(Range("H7").Value)
Case Is = "SHOW"
Sheets("May").Visible = True
Case Is = "HIDE"
Sheets("May").Visible = xlVeryHidden
End Select

Select Case UCase(Range("H8").Value)
Case Is = "SHOW"
Sheets("JUNE").Visible = True
Case Is = "HIDE"
Sheets("JUNE").Visible = xlVeryHidden
End Select
End If
End Sub