Control Toolbox and Private Sub Worksheet_Change(ByVal Target
 

If "toggle" refers to J2:

Private Sub ToggleButton1_Click()
Dim sh As Worksheet
For Each sh In Sheets(Array("Sheet2", "Sheet3"))
If Range("toggle") Then
sh.Visible = xlSheetHidden
Else
sh.Visible = xlSheetVisible
End If
Next sh

End Sub

"Toppers" wrote:

Change event is initialised by data ENTRY: changing the cell via a formula
will not activate the code.

Try this:

I named A1 as "Toggle"

Private Sub ToggleButton1_Click()

Dim sh As Worksheet

For Each sh In Sheets(Array("Sheet2", "Sheet3"))
If Range("toggle") = 1 Then sh.Visible = xlSheetHidden
If Range("toggle") = 2 Then sh.Visible = xlSheetVisible
Next sh

End Sub

HTH

" wrote:

Control Toolbox and Private Sub Worksheet_Change(ByVal Target As Range)
-------------------------------------------------------------------------

Dear Everyone,

I really appreciate all help I can get.

Here goes:

1) I have a toggle button when its switched on, cell J2 shows TRUE. If it is
switched off then FALSE.

2) In cell A1, i have this formula: =IF(J2=TRUE,1,2)


3) I have this macro (which does not work with the toggle button).

'By Ron de Bruin
'If you enter 1 in A1 it hide sheet2 and sheet3
'If A1 = 2 it unhide them

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
For Each sh In Sheets(Array("Sheet2", "Sheet3"))
If Target.Value = 1 Then sh.Visible = xlSheetHidden
If Target.Value = 2 Then sh.Visible = xlSheetVisible
Next sh
End If
End Sub


Summary:
A1 = Determines hide or unhide (macro)
J2 = LinkedCell from toggle button

Note: If I enter value 1 or 2 in cell A1, the macro will work. But I wanna
use a toggle button or stuff from the control toolbox.



How can I get it to work.
Thanks.