Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Behaviour of Toggle Button Values

  1. #1
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    2,942
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Behaviour of Toggle Button Values

    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?

  2. #2
    Board Regular
    Join Date
    Mar 2013
    Posts
    555
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Behaviour of Toggle Button Values

    You can control this from happening.
    Look into
    Application.EnableEvents=False
    and
    Application.EnableEvents=True

  3. #3
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    2,942
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Behaviour of Toggle Button Values

    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.

    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
    Code:
    Public mbEvents As Boolean
    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.

  4. #4
    Board Regular
    Join Date
    Mar 2013
    Posts
    555
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Behaviour of Toggle Button Values

    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.

  5. #5
    Board Regular
    Join Date
    Mar 2013
    Posts
    555
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Behaviour of Toggle Button Values

    Another question along the same lines, currently being addressed by a couple of pros may also shed some light.

    http://www.mrexcel.com/forum/excel-q...-userform.html

  6. #6
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,930
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Behaviour of Toggle Button Values

    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.

  7. #7
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    2,942
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Behaviour of Toggle Button Values

    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.

    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
    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

  8. #8
    Board Regular
    Join Date
    Mar 2013
    Posts
    555
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Behaviour of Toggle Button Values

    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 by NoSparks; Jan 31st, 2016 at 10:02 PM.

  9. #9
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,930
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Behaviour of Toggle Button Values

    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.

  10. #10
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    2,942
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Behaviour of Toggle Button Values

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •