Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide a sheet with a variable name
G'Day
I am trying to hide a sheet(s) based on a cell value. I have found an old post thats uses: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("B8"), Target) Is Nothing Then For Each sh In Sheets(Array("Sheet2")) If Target.Value = "" Then sh.Visible = xlSheetHidden If Target.Value < "" Then sh.Visible = xlSheetVisible Next sh End If End Sub and this works perfectly. EXCEPT... my problem is that my sheet names change (in this case Sheet2) depending on the value of other cells in the workbook. The +ve side is that all the info for the sheet names and wether to hide other sheets is contained on a single worksheet (called Front Page) can anybody help? Thanks Scott |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide a sheet with a variable name
hi, Scott !
let's suppose in "Front Page" sheet you put the names for the worksheets to hide/show in B3:B4 range Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range Application.ScreenUpdating = False If Target.Address < "$B$8" Then Exit Sub For Each myCell In Range("b3:b4") Worksheets(myCell.Text).Visible = Range("b8") < "" Next End Sub hth, hector. __ OP __ I am trying to hide a sheet(s) based on a cell value. I have found an old post thats uses: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("B8"), Target) Is Nothing Then For Each sh In Sheets(Array("Sheet2")) If Target.Value = "" Then sh.Visible = xlSheetHidden If Target.Value < "" Then sh.Visible = xlSheetVisible Next sh End If End Sub and this works perfectly. EXCEPT... my problem is that my sheet names change (in this case Sheet2) depending on the value of other cells in the workbook. The +ve side is that all the info for the sheet names and wether to hide other sheets is contained on a single worksheet (called Front Page) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide a sheet with a variable name
G'Day Héctor
Thanks for the code it works quite well. I extended and moved the range from B3:B4 to A3:A10 and it still worked fine. However it (un)hides all the sheets in the range. What I need to do is be able to show/hide individual sheets (names in range A3:A10) depnding on whether or not the corresonponing target cell in range B3:10 has an entry? ie if B7 is a null value (which is the default) then hide the sheet name, based on A7 Thanks Scott "Miguel" wrote: hi, Scott ! let's suppose in "Front Page" sheet you put the names for the worksheets to hide/show in B3:B4 range Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range Application.ScreenUpdating = False If Target.Address < "$B$8" Then Exit Sub For Each myCell In Range("b3:b4") Worksheets(myCell.Text).Visible = Range("b8") < "" Next End Sub hth, hector. __ OP __ I am trying to hide a sheet(s) based on a cell value. I have found an old post thats uses: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("B8"), Target) Is Nothing Then For Each sh In Sheets(Array("Sheet2")) If Target.Value = "" Then sh.Visible = xlSheetHidden If Target.Value < "" Then sh.Visible = xlSheetVisible Next sh End If End Sub and this works perfectly. EXCEPT... my problem is that my sheet names change (in this case Sheet2) depending on the value of other cells in the workbook. The +ve side is that all the info for the sheet names and wether to hide other sheets is contained on a single worksheet (called Front Page) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide a sheet with a variable name
hi, Scott !
Thanks for the code it works quite well. I extended and moved the range from B3:B4 to A3:A10 and it still worked fine. However it (un)hides all the sheets in the range. What I need to do is be able to show/hide individual sheets (names in range A3:A10) depnding on whether or not the corresonponing target cell in range B3:10 has an entry? ie if B7 is a null value (which is the default) then hide the sheet name, based on A7 perhaps... Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range Application.ScreenUpdating = False If Intersect(Target, Range("b3:b10")) Is Nothing Then Exit Sub For Each myCell In Intersect(Target, Range("b3:b10")) Worksheets(myCell.Offset(, -1).Text).Visible = myCell < "" Next End Sub hth, hector. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide a sheet with a variable name
Héctor
Thanks a lot it works perfectly Scott "Héctor Miguel" wrote: hi, Scott ! Thanks for the code it works quite well. I extended and moved the range from B3:B4 to A3:A10 and it still worked fine. However it (un)hides all the sheets in the range. What I need to do is be able to show/hide individual sheets (names in range A3:A10) depnding on whether or not the corresonponing target cell in range B3:10 has an entry? ie if B7 is a null value (which is the default) then hide the sheet name, based on A7 perhaps... Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range Application.ScreenUpdating = False If Intersect(Target, Range("b3:b10")) Is Nothing Then Exit Sub For Each myCell In Intersect(Target, Range("b3:b10")) Worksheets(myCell.Offset(, -1).Text).Visible = myCell < "" Next End Sub hth, hector. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How To make a sheet reference Variable (eq: sum(sheet!D2:H2)) | Excel Discussion (Misc queries) | |||
Create Sheet, Hide Sheet | Excel Discussion (Misc queries) | |||
using sheet-variable in function | Excel Worksheet Functions | |||
Variable sheet and Formula Sheet | Excel Discussion (Misc queries) | |||
variable in a link where the variable is the name of the sheet | Excel Worksheet Functions |