Behaviour of Toggle Button Values

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a userform with 4 toggle buttons on it

With each button I have an on '_Click' event.

Code:
Private Sub tglb_hp_crts_Click()
    toggleclicks1
End Sub

Private Sub tglb_hp_dias_Click()
    toggleclicks1
End Sub

Private Sub tglb_hp_flds_Click()
    toggleclicks1
End Sub

Private Sub tglb_hp_others_Click()
    toggleclicks1
End Sub

Code:
Private Sub toggleclicks1()
    
    Dim scrit5 As String
    Dim scrit10 As String
    Dim dirflag As Long
    Dim raf1_criteria As Range
    
    
    If Me.MultiPage1.Value = 0 Then 'ALL PAGE BUTTONS
        
        '... code ...
    
    ElseIf Me.MultiPage1.Value = 2 Then 'HILLSIDE PAGE BUTTONS
        Set lbtarget = Me.hp_list
        scrit10 = "HP"
        If tglb_hp_dias.Value = True Then 'HILLSIDE DIAMONDS ACTIVATED
            scrit5 = "=D*"
            dirflag = 0
            Me.tglb_hp_flds.Value = False
            Me.tglb_hp_crts.Value = False
            Me.tglb_hp_others.Value = False
        ElseIf tglb_hp_flds.Value = True Then 'HILLSIDE FIELDS ACTIVATED
            scrit5 = "=F*"
            dirflag = 0
            Me.tglb_hp_dias.Value = False
            Me.tglb_hp_crts.Value = False
            Me.tglb_hp_others.Value = False
        ElseIf tglb_hp_crts.Value = True Then 'HILLSIDE COURTS ACTIVATED
            scrit5 = "=C*"
            dirflag = 0
            Me.tglb_hp_dias.Value = False
            Me.tglb_hp_flds.Value = False
            Me.tglb_hp_others.Value = False
        ElseIf tglb_hp_others.Value = True Then 'HILLSIDE OTHERS ACTIVATED
            Set raf1_criteria = ws_vh.Range("B6:E7")
            ws_vh.Range("E7") = "HP"
            dirflag = 1
            Me.tglb_hp_dias.Value = False
            Me.tglb_hp_flds.Value = False
            Me.tglb_hp_crts.Value = False
        Else ' all HILLSIDE BUTTONS FALSE
            scrit5 = "<>"
            dirflag = 0
            Me.tglb_hp_dias.Value = False
            Me.tglb_hp_flds.Value = False
            Me.tglb_hp_crts.Value = False
            Me.tglb_hp_others.Value = False
        End If
    
        ' ... code ...    
    
    End If
    
    With ws_core
        .Activate '''
        .Range("A:W").EntireColumn.Hidden = False
        .AutoFilterMode = False
        If dirflag = 0 Then
            .Range("A1:V1").AutoFilter Field:=5, Criteria1:=scrit5
            .Range("A1:V1").AutoFilter Field:=10, Criteria1:=scrit10
        Else
            .Range("A:V").AdvancedFilter _
                Action:=xlFilterInPlace, _
                CriteriaRange:=raf1_criteria
        End If
        .Range("B:B,D:D,G:G,I:J,M:M,P:V").EntireColumn.Hidden = True
        Set Rnglist = .Range("A1:O" & .Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    End With
    With ws_th
        .Activate '''
        .Cells.ClearContents
        Rnglist.Copy ws_th.Range("A1")
    End With
    llstrow = ws_th.Cells(Rows.Count, 1).End(xlUp).Row
    Set rngSource = ws_th.Range("A2:G" & llstrow)
    With lbtarget
        .Clear
        .ColumnCount = 9
        .ColumnWidths = "50;40;20;200;125;50;50;50;50"
        For Each oneRow In rngSource.Rows
            .AddItem oneRow.Range("A1").Text
            .List(.ListCount - 1, 1) = oneRow.Range("B1").Text
            .List(.ListCount - 1, 2) = oneRow.Range("C1").Text
            .List(.ListCount - 1, 3) = oneRow.Range("D1").Text
            .List(.ListCount - 1, 4) = oneRow.Range("E1").Text
            .List(.ListCount - 1, 5) = oneRow.Range("F1").Text
            .List(.ListCount - 1, 6) = oneRow.Range("G1").Text
            .List(.ListCount - 1, 7) = oneRow.Range("H1").Text
            .List(.ListCount - 1, 8) = oneRow.Range("I1").Text
        Next oneRow
    End With
    
End Sub

When the user form initializes all 4 buttons are deselected (in their false state). Suppose the user selects the tglb_hp_flds button. The button is depressed (now in true state), and the CLICK routine is executed which executes the 'toggleclicks1' routine.

Everything works as expected.

However, an issue arises when the user then selects the tglb_hp_dias button. The button depresses (now in the true state), and the previously selected button also remains in the depressed (true) position. (as expected)

The 'toggleclicks1' is again executed. It moves through the ifs until it hits 'tglb_hp_dias.Value = True Then", and proceeds to execute it's respective commands. To return the previously selected tglb_hp_flds button back to its default FALSE state, the code executes 'Me.tglb_hp_flds.Value = False'. But when it does this ... it seems to equate to a CLICK, thus executing the 'Sub tglb_hp_flds_Click()' routine.

My process of resetting previously selected buttons in their TRUE state back to FALSE is triggering CLICK routines, appearing to result in a loop of unwanted behaviour.

Is this normal behaviour when coding a value change to a toggle button, to mimick a CLICK? If so, what do I need to do to avoid this?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
You can control this from happening.
Look into
Application.EnableEvents=False
and
Application.EnableEvents=True
 
Upvote 0
Hi NoSparks. Thank you for your reply.

Unless I'm using it incorrectly, Application.EnableEvents does not seem to have any change in how things are working.

Rich (BB code):
If tglb_hp_dias.Value = True Then 'HILLSIDE DIAMONDS ACTIVATED
            scrit5 = "=D*"
            dirflag = 0
            Application.EnableEvents = False
            Me.tglb_hp_flds.Value = False
            Me.tglb_hp_crts.Value = False
            Me.tglb_hp_others.Value = False
            Application.EnableEvents = True
        ElseIf tglb_hp_flds.Value = True Then 'HILLSIDE FIELDS ACTIVATED
            scrit5 = "=F*"
            dirflag = 0
            Application.EnableEvents = False
            Me.tglb_hp_dias.Value = False
            Me.tglb_hp_crts.Value = False
            Me.tglb_hp_others.Value = False
            Application.EnableEvents = True

I was under the impression that Application.EnableEvents had no effect on userform changes, so I also experimented with using a boolean variable (mbEvents). But I may be using this incorrectly as well
Rich (BB code):
Public mbEvents As Boolean
Rich (BB code):
If tglb_hp_dias.Value = True Then 'HILLSIDE DIAMONDS ACTIVATED
            If Not mbEvents Then Exit Sub
            scrit5 = "=D*"
            dirflag = 0
            Me.mbEvents = False
            Me.tglb_hp_flds.Value = False
            Me.tglb_hp_crts.Value = False
            Me.tglb_hp_others.Value = False
            Me.mbEvents = True
        ElseIf tglb_hp_flds.Value = True Then 'HILLSIDE FIELDS ACTIVATED
            If Not mbEvents Then Exit Sub            
            scrit5 = "=F*"
            dirflag = 0
            Me.mbEvents = False
            Me.tglb_hp_dias.Value = False
            Me.tglb_hp_crts.Value = False
            Me.tglb_hp_others.Value = False
            Me.mbEvents = True

Any help overcoming this hurdle will be greatly appreciated.
 
Upvote 0
Sorry Ark, I should learn to read.

I'm not sure mbEvents has anything to do with events, as after typing me. in the code it does not give EnableEvents in the VBA intellisense drop down. (Excel 2010)
Using Public EnableEvents As Boolean as per Suppressing Events In UserForms
EnableEvents does show up in the intellisense drop down.

Interesting scenario, I'll be watching for sure.
 
Upvote 0
It looks like you are wanting your ToggleButtons to act like OptionButtons. i.e. setting one to True will set all the others to False.
Have you considered replacing the ToggleButtons with OptionButtons?

If not, I concur that the techniques discussed in NoSparks "Disable_Change" link would be useful.
 
Upvote 0
Thanks Mike for sharing your insight...
I had thought at one point using the optionbuttons, but didn't find them as appealing. (the tiny circle of the button didn't blend well with the userform). As a last resort I know it's an option, but I'd prefer to see if this can work.

I reviewed the thread NoSparks provided and you endorsed, but finding it difficult to follow and adapt to my scenario. Will keep experimenting.

Rich (BB code):
Private Sub tglb_hp_dias_Click()
    If mbEvents = False Then Exit Sub
    toggleclicks1
    
End Sub

Private Sub tglb_hp_flds_Click()
    If mbEvents = False Then Exit Sub
        toggleclicks1
End Sub

Rich (BB code):
Private Sub toggleclicks1()
    mbEvents = False
    Dim scrit5 As String
    Dim scrit10 As String
    Dim dirflag As Long
    Dim raf1_criteria As Range
    
    ... some code for multipage1 if's values 0 and 1
  
    ElseIf Me.MultiPage1.Value = 2 Then 'HILLSIDE PAGE BUTTONS
        Set lbtarget = Me.hp_list
        scrit10 = "HP"
        If tglb_hp_dias.Value = True Then 
            scrit5 = "=D*"
            dirflag = 0
            Me.tglb_hp_flds.Value = False
            Me.tglb_hp_crts.Value = False
            Me.tglb_hp_others.Value = False
        ElseIf tglb_hp_flds.Value = True Then 
            scrit5 = "=F*"
            dirflag = 0
            Me.tglb_hp_dias.Value = False
            Me.tglb_hp_crts.Value = False
            Me.tglb_hp_others.Value = False
        ElseIf tglb_hp_crts.Value = True Then 
            scrit5 = "=C*"
            dirflag = 0
            Me.tglb_hp_dias.Value = False
            Me.tglb_hp_flds.Value = False
            Me.tglb_hp_others.Value = False
        ElseIf tglb_hp_others.Value = True Then 
            Set raf1_criteria = ws_vh.Range("B6:E7")
            ws_vh.Range("E7") = "HP"
            dirflag = 1
            Me.tglb_hp_dias.Value = False
            Me.tglb_hp_flds.Value = False
            Me.tglb_hp_crts.Value = False
        Else ' 
            scrit5 = "<>"
            dirflag = 0
            Me.tglb_hp_dias.Value = False
            Me.tglb_hp_flds.Value = False
            Me.tglb_hp_crts.Value = False
            Me.tglb_hp_others.Value = False
        End If

        ... more ifs ...

    End If
    mbEvents = True
End Sub
 
Upvote 0
Hi Ark

In my expermentation, and what I setup is probably not like yours, I got it to work but found I had to move the changing state of the other toggles out of the main sub and into the Click events.
Code:
Private Sub tgl1_Click()
If mbEvents = False Then Exit Sub
mbEvents = False
    Me.tgl2 = False
    Me.tgl3 = False
    Me.tgl4 = False
    toggleclicks1
End Sub
and reset mbEvents to True at end of toggleclicks1 sub
 
Last edited:
Upvote 0
Your mbEvents approach is the way to go.
I use the descriptive name DisableMyEvents, with code like this
Code:
Dim DisableMyEvents as Boolean

Private Sub tglb_hp_dias_Click()
    If DisableMyEvents Then Exit Sub
    toggleclicks1
End Sub

Sub toggleClicks1()
    DisableMyEvents = True

    ' more code

    DisableMyEvents = False
End Sub

In the linked thread, Andrew Paulsom does the same thing, but with the values the other way 'round.
variable EnableEvents
EnableEvents = True in the Userform_Intialize event.
test If EnableEvents = False Then Exit Sub in every event

Its all the same basic logic, just with the values reversed. BUT, the use of descriptive names helps one remember what they need to be set to when the time comes to set those variables.
 
Upvote 0
Yay!!! Very well done guys, thank you sooooo much!
At this point (until the next hurdle) all seems to have been resolved. I appreciate everyone's assistance. NoSParks, your insight was exactly what I needed.
I spent the weekend testing to find the sweet spot in my code to place the mbEvent handlers that I never even considered moving the togglebutton value codes.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top