| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HIDE / UNHIDE sheets based on user answers
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HIDE / UNHIDE sheets based on user answers
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 "Jonatas Vasconcellos" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HIDE / UNHIDE sheets based on user answers
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 "Bob Phillips" wrote: 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 -- __________________________________ HTH Bob "Jonatas Vasconcellos" <Jonatas wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HIDE / UNHIDE sheets based on user answers
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? "Mike H" wrote: 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 "Jonatas Vasconcellos" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HIDE / UNHIDE sheets based on user answers
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 "Jonatas Vasconcellos" wrote: 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? "Mike H" wrote: 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 "Jonatas Vasconcellos" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hide or unhide sheets based on cell | Excel Discussion (Misc queries) | |||
| Hide/Unhide Sheets | Excel Programming | |||
| Hide/Unhide sheets | Excel Programming | |||
| Hide/Unhide sheets | Excel Programming | |||
| Password to hide and unhide sheets | Excel Programming | |||
Linear Mode
