Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
unhide sheets
Hi all,
I have a list in C8:C17 that lists the names of sheets that I would like to have a macro that UNHIDES the sheets that are listed. Bob Phillips provided me with this code that works, however, it has a drawback in that after I enter in the name of the sheet, I have to actually DELETE the contents of that cell before the macro will run. The sheets that I am UNHIDING have VLOOKUP formulas on them that reference otheR cells on that main sheet so the sheet name needs to stay visible for VLOOKUP to have a reference. Can someone modify this so that the macro will run without having to delete the cell contents. Thanks! Option Explicit Private Const WS_RANGE As String = "C8:C17" '<=== change to suit Private prevVal As String Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value = "" Then If prevVal < "" Then Worksheets(prevVal).Visible = True End If Else Worksheets(.Value).Visible = False End If End With End If ws_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then prevVal = Target.Value End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
unhide sheets
Private Const WS_RANGE As String = "C8:C17" '<=== change to suit
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sh as worksheet If Target.count 1 then exit sub If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then set sh = nothing on error resume next set sh = worksheets(Target.value) On error goto 0 if sh.Visible < xlsheetVisible then sh.Visible = xlSheetVisible Else sh.visible = xlSheetHidden end if End If End Sub since I don't know what question you asked that caused Bob to write that code, it is hard to say how to change it (in otherwords, I don't know what functionality you asked for). In any event, if you select a cell in WS_Range, if it has a worksheet name in it, then if that sheet is visible, it is hidden and if it is hidden it is made visible. If this is what you want Remove the existing SelectionChange and Change events and copy in this code. Perhaps test it in a copy of your workbook to see if that is what you want. -- Regards, Tom Ogilvy "Tom" wrote: Hi all, I have a list in C8:C17 that lists the names of sheets that I would like to have a macro that UNHIDES the sheets that are listed. Bob Phillips provided me with this code that works, however, it has a drawback in that after I enter in the name of the sheet, I have to actually DELETE the contents of that cell before the macro will run. The sheets that I am UNHIDING have VLOOKUP formulas on them that reference otheR cells on that main sheet so the sheet name needs to stay visible for VLOOKUP to have a reference. Can someone modify this so that the macro will run without having to delete the cell contents. Thanks! Option Explicit Private Const WS_RANGE As String = "C8:C17" '<=== change to suit Private prevVal As String Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value = "" Then If prevVal < "" Then Worksheets(prevVal).Visible = True End If Else Worksheets(.Value).Visible = False End If End With End If ws_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then prevVal = Target.Value End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
unhide sheets
Hi Tom,
Thanks for the reply, here is the original problem: Hi all, Is it possible to have a list of sheet names in C8:C17 on sheet "Main", and have a macro that will UNHIDE those sheets that are listed in that range? Essentially, I want to have all my sheets (except sheet "Main") hidden until they are listed in that range, then UNHIDE upon running the macro. As I mentioned, the code provided does work...but only upon deleting the cells contents which doesn't allow my VLOOKUP formula to have a reference. I need the list to remain in C8:C17 for the VLOOKUP. Thanks, Tom "Tom Ogilvy" wrote: Private Const WS_RANGE As String = "C8:C17" '<=== change to suit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sh as worksheet If Target.count 1 then exit sub If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then set sh = nothing on error resume next set sh = worksheets(Target.value) On error goto 0 if sh.Visible < xlsheetVisible then sh.Visible = xlSheetVisible Else sh.visible = xlSheetHidden end if End If End Sub since I don't know what question you asked that caused Bob to write that code, it is hard to say how to change it (in otherwords, I don't know what functionality you asked for). In any event, if you select a cell in WS_Range, if it has a worksheet name in it, then if that sheet is visible, it is hidden and if it is hidden it is made visible. If this is what you want Remove the existing SelectionChange and Change events and copy in this code. Perhaps test it in a copy of your workbook to see if that is what you want. -- Regards, Tom Ogilvy "Tom" wrote: Hi all, I have a list in C8:C17 that lists the names of sheets that I would like to have a macro that UNHIDES the sheets that are listed. Bob Phillips provided me with this code that works, however, it has a drawback in that after I enter in the name of the sheet, I have to actually DELETE the contents of that cell before the macro will run. The sheets that I am UNHIDING have VLOOKUP formulas on them that reference otheR cells on that main sheet so the sheet name needs to stay visible for VLOOKUP to have a reference. Can someone modify this so that the macro will run without having to delete the cell contents. Thanks! Option Explicit Private Const WS_RANGE As String = "C8:C17" '<=== change to suit Private prevVal As String Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value = "" Then If prevVal < "" Then Worksheets(prevVal).Visible = True End If Else Worksheets(.Value).Visible = False End If End With End If ws_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then prevVal = Target.Value End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
unhide sheets
Remove all events in the sheet (Main) with the range C8:C17 and paste in
this one event. Test this on a copy of your workbook: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet, sh1 As Worksheet Dim rng As Range, cell As Range Set rng = Worksheets("Main").Range("C8:C17") If Not Intersect(Target, rng) Is Nothing Then For Each sh1 In Worksheets bVisible = False If LCase(sh1.Name) < "main" Then For Each cell In rng Set sh = Nothing On Error Resume Next Set sh = Worksheets(cell.Value) On Error GoTo 0 If Not sh Is Nothing Then If sh.Name = sh1.Name Then bVisible = True Exit For End If End If Next cell If bVisible Then sh1.Visible = xlSheetVisible Else sh1.Visible = xlSheetHidden End If End If Next sh1 End If End Sub worked for me. -- Regards, Tom Ogilvy "Tom" wrote: Hi Tom, Thanks for the reply, here is the original problem: Hi all, Is it possible to have a list of sheet names in C8:C17 on sheet "Main", and have a macro that will UNHIDE those sheets that are listed in that range? Essentially, I want to have all my sheets (except sheet "Main") hidden until they are listed in that range, then UNHIDE upon running the macro. As I mentioned, the code provided does work...but only upon deleting the cells contents which doesn't allow my VLOOKUP formula to have a reference. I need the list to remain in C8:C17 for the VLOOKUP. Thanks, Tom "Tom Ogilvy" wrote: Private Const WS_RANGE As String = "C8:C17" '<=== change to suit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sh as worksheet If Target.count 1 then exit sub If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then set sh = nothing on error resume next set sh = worksheets(Target.value) On error goto 0 if sh.Visible < xlsheetVisible then sh.Visible = xlSheetVisible Else sh.visible = xlSheetHidden end if End If End Sub since I don't know what question you asked that caused Bob to write that code, it is hard to say how to change it (in otherwords, I don't know what functionality you asked for). In any event, if you select a cell in WS_Range, if it has a worksheet name in it, then if that sheet is visible, it is hidden and if it is hidden it is made visible. If this is what you want Remove the existing SelectionChange and Change events and copy in this code. Perhaps test it in a copy of your workbook to see if that is what you want. -- Regards, Tom Ogilvy "Tom" wrote: Hi all, I have a list in C8:C17 that lists the names of sheets that I would like to have a macro that UNHIDES the sheets that are listed. Bob Phillips provided me with this code that works, however, it has a drawback in that after I enter in the name of the sheet, I have to actually DELETE the contents of that cell before the macro will run. The sheets that I am UNHIDING have VLOOKUP formulas on them that reference otheR cells on that main sheet so the sheet name needs to stay visible for VLOOKUP to have a reference. Can someone modify this so that the macro will run without having to delete the cell contents. Thanks! Option Explicit Private Const WS_RANGE As String = "C8:C17" '<=== change to suit Private prevVal As String Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value = "" Then If prevVal < "" Then Worksheets(prevVal).Visible = True End If Else Worksheets(.Value).Visible = False End If End With End If ws_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then prevVal = Target.Value End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
unhide sheets
Absolutely perfect!
Thanks again for the assistance. "Tom Ogilvy" wrote: Remove all events in the sheet (Main) with the range C8:C17 and paste in this one event. Test this on a copy of your workbook: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet, sh1 As Worksheet Dim rng As Range, cell As Range Set rng = Worksheets("Main").Range("C8:C17") If Not Intersect(Target, rng) Is Nothing Then For Each sh1 In Worksheets bVisible = False If LCase(sh1.Name) < "main" Then For Each cell In rng Set sh = Nothing On Error Resume Next Set sh = Worksheets(cell.Value) On Error GoTo 0 If Not sh Is Nothing Then If sh.Name = sh1.Name Then bVisible = True Exit For End If End If Next cell If bVisible Then sh1.Visible = xlSheetVisible Else sh1.Visible = xlSheetHidden End If End If Next sh1 End If End Sub worked for me. -- Regards, Tom Ogilvy "Tom" wrote: Hi Tom, Thanks for the reply, here is the original problem: Hi all, Is it possible to have a list of sheet names in C8:C17 on sheet "Main", and have a macro that will UNHIDE those sheets that are listed in that range? Essentially, I want to have all my sheets (except sheet "Main") hidden until they are listed in that range, then UNHIDE upon running the macro. As I mentioned, the code provided does work...but only upon deleting the cells contents which doesn't allow my VLOOKUP formula to have a reference. I need the list to remain in C8:C17 for the VLOOKUP. Thanks, Tom "Tom Ogilvy" wrote: Private Const WS_RANGE As String = "C8:C17" '<=== change to suit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sh as worksheet If Target.count 1 then exit sub If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then set sh = nothing on error resume next set sh = worksheets(Target.value) On error goto 0 if sh.Visible < xlsheetVisible then sh.Visible = xlSheetVisible Else sh.visible = xlSheetHidden end if End If End Sub since I don't know what question you asked that caused Bob to write that code, it is hard to say how to change it (in otherwords, I don't know what functionality you asked for). In any event, if you select a cell in WS_Range, if it has a worksheet name in it, then if that sheet is visible, it is hidden and if it is hidden it is made visible. If this is what you want Remove the existing SelectionChange and Change events and copy in this code. Perhaps test it in a copy of your workbook to see if that is what you want. -- Regards, Tom Ogilvy "Tom" wrote: Hi all, I have a list in C8:C17 that lists the names of sheets that I would like to have a macro that UNHIDES the sheets that are listed. Bob Phillips provided me with this code that works, however, it has a drawback in that after I enter in the name of the sheet, I have to actually DELETE the contents of that cell before the macro will run. The sheets that I am UNHIDING have VLOOKUP formulas on them that reference otheR cells on that main sheet so the sheet name needs to stay visible for VLOOKUP to have a reference. Can someone modify this so that the macro will run without having to delete the cell contents. Thanks! Option Explicit Private Const WS_RANGE As String = "C8:C17" '<=== change to suit Private prevVal As String Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value = "" Then If prevVal < "" Then Worksheets(prevVal).Visible = True End If Else Worksheets(.Value).Visible = False End If End With End If ws_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then prevVal = Target.Value End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unhide sheets | Excel Programming | |||
Help unhide sheets | Excel Programming | |||
Unhide sheets | Excel Programming | |||
How to Unhide sheets | Excel Discussion (Misc queries) | |||
Add sheets that are unhide | Excel Programming |