Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Control Toolbox and Private Sub Worksheet_Change(ByVal Target As R
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Control Toolbox and Private Sub Worksheet_Change(ByVal Target | Excel Discussion (Misc queries) | |||
ByVal Target Range Great Code but need Help | Excel Discussion (Misc queries) | |||
Private Sub Worksheet_Change(ByVal Target As Excel.Range) | Excel Worksheet Functions | |||
Where is the control toolbox? | New Users to Excel | |||
Where is the control toolbox? | New Users to Excel |