Change State of Toggle Button

[tminhv]

Hi, I have toggle buttons that (un)hide different columns respectively. However, I have a click button that unhides all my columns. When my columns are unhidden, I want the toggle buttons to return to their unpressed state. I've been trying to figure out how to basically get the following to work.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)


If Columns("E:I").Hidden = False Then
Me.ToggleButton2.Value = True
Else
Me.ToggleButton2.Value = False
End If

End Sub

 

[6StringJazzer]

One issue with this is that changing the value of the toggle button will cause the Click event to fire. I assume you have code
Private Sub ToggleButton2_Click.

Please show the code you are using for the toggle button action. We have to consider it all together.

 

[tminhv]

One issue with this is that changing the value of the toggle button will cause the Click event to fire. I assume you have code
Private Sub ToggleButton2_Click.

Please show the code you are using for the toggle button action. We have to consider it all together.

Private Sub ToggleButtonNote_Click()
Dim xAddress As String
xAddress = "H:H"
If ToggleButtonNote.Value Then
Application.ActiveSheet.Columns(xAddress).Hidden = True
Else
Application.ActiveSheet.Columns(xAddress).Hidden = False
End If
End Sub

 

[6StringJazzer]

Be aware that the Change event occurs only when a constant or formula in a cell changes, not if you hide or unhide columns. That is, if someone just hides columns, it won't even run.

The Click sub you provided is not for the same toggle button referenced in your first post.

But just for the sake of argument let's pretend it's the same. In your first sub, if E:H is not hidden, then the code sets the toggle to True. That trigger the Click event. The button is True, so the click event hides the columns. The same thing happens in reverse if E:H is hidden. Then it Clicks the toggle, the toggle is False, and and it makes the columns visible.

You need is a global variable that will indicate to the toggle Click code that it shouldn't do anything because the event was triggered by other code. Maybe try this. Also I suggest you use code tags when posting code.

Also I simplified the Boolean logic, which is equivalent to the logic you had.

Option Explicit

Dim ToggleDisabled As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

   ToggleDisabled = True
   
   Me.ToggleButtonButton.Value = Not Columns("E:I").Hidden
   
   ToggleDisabled = False

End Sub

Private Sub ToggleButtonNote_Click()

   Dim xAddress As String
   
   If ToggleDisabled Then Exit Sub
   
   xAddress = "H:H"
   Application.ActiveSheet.Columns(xAddress).Hidden = ToggleButtonNote.Value

End Sub