Module for Toggle Buttons

[Gregg Wolin]

I have a series of worksheets (reports) that contain up to 48 months (rows) of data. Each sheet contains a filter column that can be toggled with a "ToggleButton1" that triggers the filter via the sub below.

Private Sub ToggleButton1_Click()

    If ToggleButton1.Value = True Then
        ActiveSheet.Range("$z$1:$z$60").AutoFilter Field:=1, Criteria1:="=1", _
        Operator:=xlOr, Criteria2:="="
        ToggleButton1.Caption = "Show ZERO Rows"
            Else
        ActiveSheet.Range("$z$1:$z$60").AutoFilter Field:=1
        ToggleButton1.Caption = "Hide ZERO Rows"
    End If
End Sub

I want to consolidate this code to a single Module that will apply to ANY worksheet that contains a filter column with a ToggleButton but I haven't found any examples that don't require a macro on EACH individual sheet.

 

[p45cal]

Have a look at http://spreadsheetpage.com/index.php/site/tip/handle_multiple_userform_buttons_with_one_subroutine/
It's for buttons on a userform but it works for activeX objects on sheets too.
I've not much time in the coming days but I'll try to put together a workbook with such togglebuttons, but it would help a lot if you could prepare a slimmed down version of your workbook and attach it here and I'll try adding the code to that.

 

[Gregg Wolin]

Have a look at http://spreadsheetpage.com/index.php/site/tip/handle_multiple_userform_buttons_with_one_subroutine/
It's for buttons on a userform but it works for activeX objects on sheets too.
I've not much time in the coming days but I'll try to put together a workbook with such togglebuttons, but it would help a lot if you could prepare a slimmed down version of your workbook and attach it here and I'll try adding the code to that.

I will take a look. in the meantime, here is a stripped down version of my workbook.

 

[p45cal]

See attached

 

[Gregg Wolin]

See attached

I'm confused. You have added Module2, Module3 and Class2. Your comments in Class2 indicate that I can put my macro in the Class2 module, but you put the macro in Module 3? Why? If I deactivate 'comment out the code in the r_Builder worksheet and activate the code in Class2, nothing works.

 

[p45cal]

'nothing works': probably you need to run CreateToggleButtonGroup again; as you're developing, the vba project will get reset as you're adjusting the code.
For a normal user, you'd only need to run CreateToggleButtonGroup once (eg as it is now on file open) BUT, there's no harm in running it again later, so you could have CreateToggleButtonGroup in a Workbook_SheetActivate (in the ThisWorkBook code-module) as well or instead of.

re: 'but you put the macro in Module 3'
Purely a matter of personal preference. I think (but haven't checked) that it would be easier to call the process from elsewhere (another sub) if the code is in a standard code-module rather than a class module.