msgbox appearing twice

[Mikeymike_W]

Hi,

I was hoping someone could tell me why my msgbox gets triggered twice?
Its in the comboboxchange so my initial guess was because i was making Comboxarea2B.value="" that it was triggering it again but if this is the case then how do I make Comboxarea2B value blank without triggering the code again?

Many thanks in advance for your help,

Mike

Private Sub Comboxarea2B_Change()

Dim Q As Worksheet
Dim myvalue2 As String
Dim p As Range
Dim myvalue3 As String

If Comboxarea2.Value = "" Then
Comboxarea2B.Value = ""
MsgBox "Select Area one First", , "Area One"
Exit Sub
End If



If Optmajor2.Value = True Then
TextBox2.Text = Comboxmajor2.Value
Else
If Optminor2.Value = True Then
TextBox2.Text = Comboxnumber2.Value & " OF " & Comboxminor2.Value
End If
End If


Set Q = Worksheets("Interpretations")
    myvalue2 = TextBox2.Value & " " & Comboxarea2B.Value
   
    Set p = Q.Range("A:MM").Find(myvalue2, LookIn:=xlValues, lookat:=xlWhole)
    
    If Not p Is Nothing Then
        TBanswer2.Value = TBanswer2.Text & vbNewLine & p.Offset(0, 1).Value
    Else
    Set Q = Worksheets("Interpretations")
    myvalue3 = Comboxmajor2.Value & " " & Comboxarea2.Value
    Set p = Q.Range("A:MM").Find(myvalue3, LookIn:=xlValues, lookat:=xlWhole)
    If Not p Is Nothing Then
        TBanswer2.Value = TBanswer2.Text & vbNewLine & p.Offset(0, 1).Value
    Else
        TBanswer2.Value = ""
    End If
    End If
   
   
End Sub

 

[jasonb75]

Don't think that I've ever tried using this with form objects, although I see no reason why it shouldn't work.

If Comboxarea2.Value = "" Then
Application.EnableEvents = False
Comboxarea2B.Value = ""
Application.EnableEvents = True
MsgBox "Select Area one First", , "Area One"
Exit Sub
End If

 

[Mikeymike_W]

Hi Jasonb75,

Thanks for your help. I gave it a go but the msgbox still gets triggered twice :/

Many thanks Mike

 

[jasonb75]

I thought that should have worked, guess form object changes don't count as events. Try this as the first line of the code

If Comboxarea2B.Value = "" Then Exit Sub

 

[Mikeymike_W]

Hi Jason, the event is a Comboxarea2B change event. So if the event is triggered that means that Comboxarea2B will never be blank.
I need the message box to prompt the user to fill Comboxarea2 first so even if i did

if comboxarea2.value="" then exit sub

it would miss out the msgbox.

I hope that makes sense, thanks again i appreciate your help

 

[Mikeymike_W]

just had an idea, could make it a click event rather than a change event that way a value wouldnt be entered yet

Just tried it out and it worked fine:

Private Sub Comboxarea2B_Click()

If Comboxarea2.Value = "" Then
Comboxarea2B.Value = ""
MsgBox "Select Area one First", , "Area One"
End If

End Sub

Thanks Jason for your help

 

[jasonb75]

So if the event is triggered that means that Comboxarea2B will never be blank.

It is blank, that is what is causing the problem, when the code changes the value to "" and shows the message a second run is triggered. You need to exit that second run without anything being done.

 

[Fluff]

Out this at the very top of the userform module (before any code)

Dim DisableEvents As Boolean

and then use

Private Sub Comboarea2B_Change()
   If DisableEvents Then Exit Sub
   DisableEvents = True
   If Comboxarea2.Value = "" Then
      Comboxarea2B.Value = ""
      MsgBox "Select Area one First", , "Area One"
      DisableEvents = False
      Exit Sub
   End If
End Sub