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...

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...

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...

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...

Sheet Show
 

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...

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...