Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Post Unsuccessful in suppressing userform control changes

    I am having difficulty in suppressing a userform control change in my userform.

    Please consider this code ...

    Code:
    Public mbEvents As Boolean
    Code:
    Private Sub UserForm_Initialize()
         ... some code ...
         Me.mbEvents = True
         ... more code ...
         Me.mbEvents = False
    exit sub
    Two toggle button Click event codes:
    Code:
    Private Sub tglb_hp_dias_Click()
        toggleclicks1
    End Sub
    Private Sub tglb_hp_flds_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 similar to below ...
            
        ElseIf Me.MultiPage1.Value = 1 Then 
                 ... code similar to below ...
        
        ElseIf Me.MultiPage1.Value = 2 Then 
            Set lbtarget = Me.hp_list
            scrit10 = "HP"
            If tglb_hp_dias.Value = True Then 
                scrit5 = "=D*"
                dirflag = 0
                Me.mbEvents = False
                If Not mbEvents Then Exit Sub
                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 
                scrit5 = "=F*"
                dirflag = 0
                Me.mbEvents = False
                If Not mbEvents Then Exit Sub
                Me.tglb_hp_dias.Value = False
                Me.tglb_hp_crts.Value = False
                Me.tglb_hp_others.Value = False
                Me.mbEvents = True
            ElseIf tglb_hp_crts.Value = True Then 
                'If Not mbEvents Then Exit Sub
                scrit5 = "=C*"
                dirflag = 0
                Me.mbEvents = False
                If Not mbEvents Then Exit Sub
                Me.tglb_hp_dias.Value = False
                Me.tglb_hp_flds.Value = False
                Me.tglb_hp_others.Value = False
                Me.mbEvents = True
            ElseIf tglb_hp_others.Value = True Then
                'If Not mbEvents Then Exit Sub
                Set raf1_criteria = ws_vh.Range("B6:E7")
                ws_vh.Range("E7") = "HP"
                dirflag = 1
                Me.mbEvents = False
                If Not mbEvents Then Exit Sub
                Me.tglb_hp_dias.Value = False
                Me.tglb_hp_flds.Value = False
                Me.tglb_hp_crts.Value = False
                Me.mbEvents = True
            Else ' 
                If Not mbEvents Then Exit Sub
                scrit5 = "<>"
                dirflag = 0
                Me.mbEvents = False
                Me.tglb_hp_dias.Value = False
                Me.tglb_hp_flds.Value = False
                Me.tglb_hp_crts.Value = False
                Me.tglb_hp_others.Value = False
                Me.mbEvents = True
            End If
            
        ElseIf Me.MultiPage1.Value = 3 Then 'RIM PAGE BUTTONS
                 ... code similar to above ...
        
        Else  'WATERLOO PARK PAGE BUTTONS
                 ... code similar to above ...
        
        End If
        
        With ws_core
            ... code to access and filter database based on variable assigned above, assign variable rnglist to visible range of filtered data ...
        End With
        With ws_th
            ... code to copy rnglist to destination worksheet
        End With
        llstrow = ws_th.Cells(Rows.Count, 1).End(xlUp).Row
        Set rngSource = ws_th.Range("A2:G" & llstrow)
        With lbtarget
            ...transfer contents to listbox (lbtarget) from source ...
        End With
        
    End Sub
    Behaviour:
    With user initially selecting tglb_hp_flds, sub togglebuttons1 is executed. tglb_hp_flds is depressed (value=true), all other buttons set to value=false to reset any previously selected buttons. Listbox properly populates as per code.
    However, when the user then selects tglb_hp_dias, togglebuttons1 executes, but triggers the tgbl_hp_flds click event when tgbl_hp_flds value is set to false (to reset it from the previous selection).

    Application.EnableEvents cannot be used as its a userform. I tried the suggested alternative to use a booleen variable (mbEvents) to suppress unwanted userform events from triggering during initialization for example. I need to use it (or something) here to prevent triggering of click events during resetting.

    It is obvious that I have either coded wrong, or have taken the wrong approach as it appears my efforts are failing.

    Can anyone help correct my errors?

    References: here and here.
    Crossposted here, with no solution.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JennEx,

    Not having your form to test limits us some but here are my observations for what they are worth.

    1. mbEvents is not a form object so the use of Me. preceding it is not necessary and I'm surprised that it doesn't generate an error.
    2. To prevent the event from executing it's code you need to test for the value of mbEvents at the beginning of each events code.

    Code:
    Private Sub MyButton_Click()
    
       If mbEvents Then
    
        [your code to process the Click event for MyButton goes here]
    
       End if
    
    End Sub   'MyButton_Click()
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi RG, I so much thank you for your support.

    There is a chance I may not have interpreted your suggestions accurately as this appears to not have made any improvement.

    I have changed my toggle button click events ...

    Code:
    Private Sub tglb_hp_dias_Click()
        If mbEvents Then
        toggleclicks1
        End If
    End Sub
    
    Private Sub tglb_hp_flds_Click()
        If mbEvents Then
        toggleclicks1
        End If
    End Sub
    I then reviewed at the togglebuttos1 code trying different options from removing all references to mbEvents (for which I removed me. in all), to leaving in the the mbEvents=true and mbEvents False in each "if" routine. In addition to your suggestion for the togglebutton click code, I was unsure what, if any changes needed to be applied to the togglebuttons1 code.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JennEx,

    You should not reference mbEvents in the toggleClicks code with the exception of turning it ON or OFF at the very end of the processing. Actually this could be done in each Click() event when execution returns from toggleClicks.

    I know that you are trying to write efficient code by using a standard click handler, however this can lead to problems if you are not sure exactly what you are doing. You may be better served by writing each click event code separately (without outside calls) until you get your code working the way you want. Once you have that solved you can then look at the code for commonality and ways to move some of the redundant code to a common function.

    Again, it is hard to give concrete advice when we can't see the whole problem. This is why posting a sanitized copy of your workbook will result in much better and more targeted advice.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JennEx,

    Ok, I've just look seriously at your code and IMHO you need to reconsider what you are doing.

    First you need to use some of the other coding constructs to make your code easier to read. Such as using the Select Case construct as follows:
    Code:
       Select Case Me.MultiPage1.Value
    
            Case 0  'ALL PAGE BUTTONS
                 ... code similar to below ...
            
            Case 1  
                 ... code similar to below ...
        
            Case 2 
             
            Case 3     'RIM PAGE BUTTONS
                 ... code similar to above ...
        
            Case Else  'WATERLOO PARK PAGE BUTTONS
                 ... code similar to above ...
        
        End Select
    Next get rid of the ElseIf chain. These are extremely hard to follow and the If ... Exit Sub statements violate good coding practice of one entry/exit for each function. If you haven't I'd suggest you create a good old fashioned flow chart for the logic you need to accomplish this task as that will likely make things much clearer and easier to code.

    Also you should adopt a standard indention sequence like that shown in the code above again to make the code easier to read and follow.

    An example of the problems with this code:
    Code:
    			
                If tglb_hp_dias.Value = True Then 
                  scrit5 = "=D*"
                  dirflag = 0
                  mbEvents = False
    			  
                  If Not mbEvents Then Exit Sub
    
    			  Me.tglb_hp_flds.Value = False
                  Me.tglb_hp_crts.Value = False
                  Me.tglb_hp_others.Value = False
                  mbEvents = True
    The lines in blue first you set mbEvents = False then you check to see if it is false? Not only confusing but inefficient.

    I'm not trying to be nasty but rather to get you set on a more productive course in your coding.

    And please post back with any further questions you may have.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks RG for the constructive criticism. I appreciate the comments and understand the importance of proper structure. Much of what I had posted was a result of throwing code in for trial and error based on internet search suggestions (not best practice but without formal training that is how I learn best), so much of the structure was lost and likely some unintended redundancy. There is no doubt that my entire project could find efficiency through improved logic I will clean things up to see if that will help me follow the logic a bit better.

    It has been suggested to use option buttons to do much of what I am struggling to do. Certainly something to consider if all else fails, but the buttons look a bit out of place on my userform. When I approached toggle buttons as a choice, I didn't realize that coding a value change was the equivalent to a "click". Now I realize that a click is simply a means of changing the value between true and false. I guess I'm just committed to seeing this through at this point. Optimistically speaking (and again without learned knowledge), I think finding the proper placement of the mbEvent codes may be what is needed.

  7. #7
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts
    It looks like my hurdle has been overcome.
    After cleaning up my code (replaced at least one if / then / end if structure with case), it was suggested I "reset" the toggle buttons in the togglebutton click code and remove such, and any mbEvent handling code from the called upon routine:

    Example:

    Code:
    Private Sub tglb_hp_others_Click()
        If mbEvents = False Then Exit Sub
            mbEvents = False
            Me.tglb_hp_dias.Value = False
            Me.tglb_hp_flds.Value = False
            Me.tglb_hp_crts.Value = False
            toggleclicks1
    End Sub
    Code:
    ...
    Case 2                                       
            
            Set lbtarget = Me.hp_list
            scrit10 = "HP"
            
            If tglb_hp_dias.Value = True Then 
                scrit5 = "=D*"
                dirflag = 0
            
            ElseIf tglb_hp_flds.Value = True Then 
                scrit5 = "=F*"
                dirflag = 0
            
            ElseIf tglb_hp_crts.Value = True Then 
                scrit5 = "=C*"
                dirflag = 0
                        
            ElseIf tglb_hp_others.Value = True Then 
                Set raf1_criteria = ws_vh.Range("B6:E7")
                ws_vh.Range("E7") = "HP"
                dirflag = 1
    
            Else 
                scrit5 = "<>"
                dirflag = 0
            
            End If
    ...
    With respect to your advice,
    Next get rid of the ElseIf chain. These are extremely hard to follow
    Can the above code, with the elseif etc. be cleaned up at all? Can CASE be used in this circumstance? I wasn't certian which test expression to apply to SELECT.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JennEx,

    Although not a traditional use of Select Case (usually it is used to test differing values of the same variable) you can use it to make your code easier to read. I made a test program from your code that does not depend on the form but it will demonstrate the use of the Select Case construct in this instance.
    Code:
    Option Explicit
    
    Sub Test()
    
      Dim tglb_hp_dias   As Boolean
      Dim tglb_hp_flds   As Boolean
      Dim tglb_hp_crts   As Boolean
      Dim tglb_hp_others As Boolean
      Dim Scrit5         As String
      Dim dirflag        As Integer
            
      tglb_hp_dias = False
      tglb_hp_flds = False
      tglb_hp_crts = False
      tglb_hp_others = True
      
       Select Case True
       
            Case tglb_hp_dias
                Scrit5 = "=D*"
                dirflag = 0
            
            Case tglb_hp_flds
                Scrit5 = "=F*"
                dirflag = 0
            
            Case tglb_hp_crts
                Scrit5 = "=C*"
                dirflag = 0
                        
            Case tglb_hp_others
                dirflag = 1
    
            Case Else
                Scrit5 = "<>"
                dirflag = 0
                
       End Select
       
       Debug.Print Scrit5, dirflag
       
    End Sub
    I ran the code once for each possibility (starting with the first one true then moving down each time having only one true until the last run which had all falses) and here is what is printed out by the Debug.Print statement in the immediate window.
    Code:
    =D*            0 
    =F*            0 
    =C*            0 
                   1 
    <>             0
    Note: If more than one of the values were True only the first one encountered in the Select Case would be executed!

    Of course when adapting this for your code you'll need to add the .Value back in and replace the statements I removed. Basically, you just want to copy the logic, e.g. replacing the ElseIf with Case etc.

    You'll also notice that the Case statements merely use the variable name, as the variables are boolean (True/False) that's all you need there is no need to test against a value with an equal sign.

    Finally, Like If statements Select Case statements can be NESTED just make sure you keep you're End Select statements matched up.

    HTH
    Last edited by RetiredGeek; 2016-02-01 at 11:49.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Much cleaner! Thank you RG.

Posting Permissions

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