MsgBox Showing Twice

[Pacman52]

Hi all - I'm struggling to work out why a response MsgBox keeps popping up twice before moving on to the next box would be depending on the response being either yes or no. I'm sure its something simple that wrong with the code I've written but for some reason just can't see what's wrong so would be grateful if someone with better knowledge than me could work out what I'm doing wrong or missing.


The problem is with the last part of the sub and the msgbox

 MsgReply = MsgBox("Does this expense relate to a date period (i.e. between two dates)?", vbQuestion + vbYesNo, "Expense Date Required")

The full code in the sub is:

Private Sub txbExpAmount_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim MsgReply As VbMsgBoxResult

    If Not IsNumeric(txbExpAmount.Value) And txbExpAmount.Value > "" Then

        MsgBox "It looks like you've entered a text value. Please only enter a numeric value greater than 0.", vbExclamation, "Invalid Entry"
        Cancel = True
        txbExpAmount.Value = ""
        txbExpAmount.SetFocus
        txbExpAmount.SelStart = 0
        txbExpAmount.SelLength = Len(txbExpAmount.Value)
        txbExpAmount.BackColor = RGB(204, 255, 255)

    ElseIf txbExpAmount.Value = "" Then

        MsgBox "It looks like you've left this box empty. Please only enter a numeric value greater than 0. ", vbExclamation, "Invalid Entry"
        Cancel = True
        txbExpAmount.Value = ""
        txbExpAmount.SetFocus
        txbExpAmount.BackColor = RGB(204, 255, 255)

    ElseIf txbExpAmount.Value = "0" Then

        MsgBox "The total expense amount needs to be a value greater than 0. Please only enter a numeric value greater than 0.", vbExclamation, "Invalid Entry"
        Cancel = True
        txbExpAmount.Value = ""
        txbExpAmount.SetFocus
        txbExpAmount.BackColor = RGB(204, 255, 255)

    Else
    
    If txbExpAmount.Value > "0" Then

        MsgReply = MsgBox("Does this expense relate to a date period (i.e. between two dates)?", vbQuestion + vbYesNo, "Expense Date Required")
        

    If MsgReply = vbYes Then
    
        txbExpDate.Enabled = False
        txbPeriodFrom.SetFocus
        txbExpAmount.Value = Format(txbExpAmount.Value, "£0.00")

    Else

        txbExpDate.Enabled = True
        txbExpDate.SetFocus
    
    End If
    End If
    End If

End Sub

Many thanks for any replies Paul

 

[Pacman52]

Just a quick update on my question above. The MsgBox ONLY appears twice if the response is Yes

 

[Logit]

This is a guess and untested here. Does the following make a difference ?

ElseIf txbExpAmount.Value = "0" Then

        MsgBox "The total expense amount needs to be a value greater than 0. Please only enter a numeric value greater than 0.", vbExclamation, "Invalid Entry"
        Cancel = True
        txbExpAmount.Value = ""
        txbExpAmount.SetFocus
        txbExpAmount.BackColor = RGB(204, 255, 255)

    ElseIf txbExpAmount.Value > "0" Then            '<<<--- changed this line

        MsgReply = MsgBox("Does this expense relate to a date period (i.e. between two dates)?", vbQuestion + vbYesNo, "Expense Date Required")
        

        If MsgReply = vbYes Then
        
            txbExpDate.Enabled = False
            txbPeriodFrom.SetFocus
            txbExpAmount.Value = Format(txbExpAmount.Value, "£0.00")
    
        Else
    
            txbExpDate.Enabled = True
            txbExpDate.SetFocus
        
        End If
    End If
End If

 

[Pacman52]

Thanks for the reply I'm pretty sure I tried using the Elseif but got an error (I thing it was Else without if).

I've been looking at the 'flow' of the form again and just trying to see if I can do it another way, so far so good but thinking ahead I'm sure I'm just moving one problem to another box but can only try and see what happens.

I will post back if I do work it out and get a solution though.

 

[Skyybot]

I think a Select Case might work better here.

 

[Logit]

Yes ... Select Case would be a cleaner approach.

 

[Pacman52]

Thanks both - I've not used Select Case for ages and completely forgot about it.

 

[dmt32]

Hi,
I think you will find using SetFocus in that event triggers it again hence your msgbox being displayed twice.
To overcome this you need to prevent the code in the event being run twice which you can do by setting a Boolean variable

See if this update to your code resolves the issue

Dim EventsDisable As Boolean

Private Sub txbExpAmount_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim MsgReply        As VbMsgBoxResult
    Dim ExpenseRelates  As Boolean
    Dim ErrorBackColor  As XlRgbColor
    
    ErrorBackColor = rgbAqua
    
    If EventsDisable Then EventsDisable = False: Exit Sub
    
    With txbExpAmount
        If Len(.Value) > 0 Then
            
            If Val(.Value) = 0 Then
                
                MsgBox "The total expense amount needs To be a value greater than 0." & Chr(10) & _
                       "Please only enter a numeric value greater than 0.", vbExclamation, "Invalid Entry"
                Cancel = True
                txbExpAmount.Value = ""
                txbExpAmount.BackColor = ErrorBackColor
                
            ElseIf Val(.Value) > 0 Then
                
                EventsDisable = True
                
                MsgReply = MsgBox("Does this expense relate To a Date period (i.e. between two dates)?", _
                           vbQuestion + vbYesNo, "Expense Date Required")
                ExpenseRelates = MsgReply = vbYes
                
                txbExpDate.Enabled = Not ExpenseRelates
                Me.Controls(IIf(ExpenseRelates, "txbPeriodFrom", "txbExpDate")).SetFocus

                .Value = Format(txbExpAmount.Value, "£0.00")
                .BackColor = rgbWhite
                
            End If
            
        Else
            'check form not being closed
            Cancel = Me.Visible
            If Cancel Then
                MsgBox "It looks like you've left this box empty." & Chr(10) & _
                       "Please only enter a numeric value greater than 0. ", vbExclamation, "Invalid Entry"
                txbExpAmount.BackColor = ErrorBackColor
            End If
        End If
        
    End With
    
End Sub

Note the variable I have highlighted BOLD - This MUST be placed at the TOP of your userforms code page OUTSIDE any procedure.

Solution only lightly tested but hopefully, will help resolve for you

Dave

 

[Pacman52]

Hi Dave thanks so much for the reply and the code. Sadly the msgbox still showed twice even after removing the setfocus references and with using the select case statements.

I've decided to go back to the beginning on this form as I'm probably over complicating it anyway lol

Thanks again Paul

 

[dmt32]

Hi Dave thanks so much for the reply and the code. Sadly the msgbox still showed twice even after removing the setfocus references and with using the select case statements.

Curious - Did you follow guidance with regard to the variable shown in BOLD & use code as published or have you made alterations?

Dave