Toggle all Togglebuttons

[docmago]

Hi,
for a few days now I am trying to solve this.
I have 12 Togglebuttons in an Excel-sheet, that toggle the show/hide status of a month in a calendar, using the following function:
[tt]
Private Sub ToggleButton1_Click()
Dim blend As Boolean
Const rowon As String = "show rows"
Const rowoff As String = "hide rows"

With ToggleButton1
If .Caption = rowon Then
.Caption = rowoff
blend = False
Else
.Caption = rowon
blend = True
End If
End With

Dim n As Integer
For n = 4 To 34
Rows.Hidden = blend
Next
End Sub
[/tt]
I would now like to include a new button to hide/unhide all months at once by switching all buttons.
Can someone point me to a way how to set each buttons.caption to rowoff and then call the click routine?
Thanks in advance,
Martin

 

[vladk]

docmago,

This is how to set the captions through the special command button for all your toggle butons.

You could try to interrogate the objTglBtn.LinkedCell (set it first) and based on that value call one or another sub that would be also called from the toggle button click events.

Option Explicit

Private Const rowon As String = "show rows"
Private Const rowoff As String = "hide rows"

Private Sub CommandButton1_Click()
SetToggleCaption
End Sub

Private Sub SetToggleCaption()
Dim objTglBtn As OLEObject

For Each objTglBtn In ActiveSheet.OLEObjects

If objTglBtn.progID = "Forms.ToggleButton.1" Then
If objTglBtn.Object.Caption = rowon Then
objTglBtn.Object.Caption = rowoff
Else
objTglBtn.Object.Caption = rowon
End If
End If

Next objTglBtn

Set objTglBtn = Nothing

End Sub

vladk