Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Show
Hi.
On sheet1 in column A I have 14 tools listed in A6:A20. In column C6:C20 I have TRUE or FALSE values which are managed by tick boxes located over column B. Each tool has a sheet named after it. How can I make it so that if a tool is designated a TRUE, then the sheet will show, and if a FALSE will keep it hidden. The tool names are exactly the same as the sheet names... I've managed this with one sheet but got lost doing more than one. Any help from anyone seriously clever will be appreciated... Thanks in advance. Gordon... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Show
what kind of tickboxes?
Checkboxes from the Control Toolbox Toolbar or from the Forms Toolbar. What method did you use to get one to work? (show the code?) -- Regards, Tom Ogilvy "Gordon" wrote: Hi. On sheet1 in column A I have 14 tools listed in A6:A20. In column C6:C20 I have TRUE or FALSE values which are managed by tick boxes located over column B. Each tool has a sheet named after it. How can I make it so that if a tool is designated a TRUE, then the sheet will show, and if a FALSE will keep it hidden. The tool names are exactly the same as the sheet names... I've managed this with one sheet but got lost doing more than one. Any help from anyone seriously clever will be appreciated... Thanks in advance. Gordon... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Show
Hi Tom...
Tick boxes aren't the key really, I'd have hoped to have used cell values to trigger the sheet to show or not. So I have no code for that at this point. Though if you think using the tick boxes is easier then I'm happy to learn. I'm using tick boxes from the Forms Toolbar. Below I have some experimental code that allowed me to hide or unhide a sheet. Private Sub Worksheet_Calculate() Dim sh As Worksheet Set sh = Worksheets("FRONTPAGE") With Sheets("hammer") If sh.Range("e21").Value = 1 Then .Visible = xlSheetHidden ElseIf sh.Range("e21").Value = 0 Then .Visible = xlSheetVisible End If End With End Sub Cheers Gordon... "Tom Ogilvy" wrote: what kind of tickboxes? Checkboxes from the Control Toolbox Toolbar or from the Forms Toolbar. What method did you use to get one to work? (show the code?) -- Regards, Tom Ogilvy "Gordon" wrote: Hi. On sheet1 in column A I have 14 tools listed in A6:A20. In column C6:C20 I have TRUE or FALSE values which are managed by tick boxes located over column B. Each tool has a sheet named after it. How can I make it so that if a tool is designated a TRUE, then the sheet will show, and if a FALSE will keep it hidden. The tool names are exactly the same as the sheet names... I've managed this with one sheet but got lost doing more than one. Any help from anyone seriously clever will be appreciated... Thanks in advance. Gordon... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Show
Right click on the tab for FrontPage and select View code. Put in code like
this (remove your other code). Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet, sh1 As Worksheet Dim rng As Range Set sh = Worksheets("FRONTPAGE") Set rng = sh.Range("C6:C20") If Target.Count 1 Then Exit Sub If Not Intersect(Target, rng) Is Nothing Then On Error Resume Next Set sh1 = Worksheets( _ sh.Cells(Target.Row, 1).Value) On Error GoTo 0 If Not sh1 Is Nothing Then If Target.Value = True Then sh1.Visible = xlSheetVisible Else sh1.Visible = xlSheetHidden End If Else MsgBox "No sheet for " & sh.Cells(Target.Row, 1) End If End If End Sub -- Regards, Tom Ogilvy "Gordon" wrote: Hi Tom... Tick boxes aren't the key really, I'd have hoped to have used cell values to trigger the sheet to show or not. So I have no code for that at this point. Though if you think using the tick boxes is easier then I'm happy to learn. I'm using tick boxes from the Forms Toolbar. Below I have some experimental code that allowed me to hide or unhide a sheet. Private Sub Worksheet_Calculate() Dim sh As Worksheet Set sh = Worksheets("FRONTPAGE") With Sheets("hammer") If sh.Range("e21").Value = 1 Then .Visible = xlSheetHidden ElseIf sh.Range("e21").Value = 0 Then .Visible = xlSheetVisible End If End With End Sub Cheers Gordon... "Tom Ogilvy" wrote: what kind of tickboxes? Checkboxes from the Control Toolbox Toolbar or from the Forms Toolbar. What method did you use to get one to work? (show the code?) -- Regards, Tom Ogilvy "Gordon" wrote: Hi. On sheet1 in column A I have 14 tools listed in A6:A20. In column C6:C20 I have TRUE or FALSE values which are managed by tick boxes located over column B. Each tool has a sheet named after it. How can I make it so that if a tool is designated a TRUE, then the sheet will show, and if a FALSE will keep it hidden. The tool names are exactly the same as the sheet names... I've managed this with one sheet but got lost doing more than one. Any help from anyone seriously clever will be appreciated... Thanks in advance. Gordon... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Show
Gordon,
My screw up. The change event is not firing when the checkbox is clicked. I have sent you two files that do work. One uses Control Toolbox Toolbar checkboxes and the other Forms Toolbar checkboxes. -- Regards, Tom Ogilvy "Gordon" wrote: Hi Tom... This hasn't worked. Sheets should appear and disappear depending on whether check boxes are ticked and unticked... Nothing changes at the moment. All previous code removed. If you ran it on a test file and it worked could you forward it to me at Thanks again. Cheers.. "Tom Ogilvy" wrote: Right click on the tab for FrontPage and select View code. Put in code like this (remove your other code). Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet, sh1 As Worksheet Dim rng As Range Set sh = Worksheets("FRONTPAGE") Set rng = sh.Range("C6:C20") If Target.Count 1 Then Exit Sub If Not Intersect(Target, rng) Is Nothing Then On Error Resume Next Set sh1 = Worksheets( _ sh.Cells(Target.Row, 1).Value) On Error GoTo 0 If Not sh1 Is Nothing Then If Target.Value = True Then sh1.Visible = xlSheetVisible Else sh1.Visible = xlSheetHidden End If Else MsgBox "No sheet for " & sh.Cells(Target.Row, 1) End If End If End Sub -- Regards, Tom Ogilvy "Gordon" wrote: Hi Tom... Tick boxes aren't the key really, I'd have hoped to have used cell values to trigger the sheet to show or not. So I have no code for that at this point. Though if you think using the tick boxes is easier then I'm happy to learn. I'm using tick boxes from the Forms Toolbar. Below I have some experimental code that allowed me to hide or unhide a sheet. Private Sub Worksheet_Calculate() Dim sh As Worksheet Set sh = Worksheets("FRONTPAGE") With Sheets("hammer") If sh.Range("e21").Value = 1 Then .Visible = xlSheetHidden ElseIf sh.Range("e21").Value = 0 Then .Visible = xlSheetVisible End If End With End Sub Cheers Gordon... "Tom Ogilvy" wrote: what kind of tickboxes? Checkboxes from the Control Toolbox Toolbar or from the Forms Toolbar. What method did you use to get one to work? (show the code?) -- Regards, Tom Ogilvy "Gordon" wrote: Hi. On sheet1 in column A I have 14 tools listed in A6:A20. In column C6:C20 I have TRUE or FALSE values which are managed by tick boxes located over column B. Each tool has a sheet named after it. How can I make it so that if a tool is designated a TRUE, then the sheet will show, and if a FALSE will keep it hidden. The tool names are exactly the same as the sheet names... I've managed this with one sheet but got lost doing more than one. Any help from anyone seriously clever will be appreciated... Thanks in advance. Gordon... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with a project sheet, need date to show/not show based o | Excel Discussion (Misc queries) | |||
How to format sheet to show first box whilst tabbing across sheet | Excel Discussion (Misc queries) | |||
How do i get sheet 2 to show a specific section of sheet 1?Please | Excel Worksheet Functions | |||
How do I get items from sheet 3 to show up on sheet 1 and 2? | Excel Worksheet Functions | |||
Excel: have add'l rows entered in sheet 1 always show up in sheet | Excel Worksheet Functions |