VBA Combobox returns to top of code

[smma]

I have a combobox with the following code:

Private Sub SN3_Change()
cRng = 6
If Worksheets(1).Range("A6") <> "" Then
CfgName = Worksheets(1).Range("A6")
Else
MsgBox "You must enter a Part Number before selecting a Paint Specification."
SN3.Value = "~"
Exit Sub
End If
If Worksheets(1).Range("A2") = "SLDASM" And Worksheets(1).Range("A6") <> "" Then
SetColor = MsgBox("Set components to configuration color?", vbYesNo, "Set Color")
If SetColor = vbYes Then
SetCompColor
ElseIf SetColor = vbNo Then
End If
End If
End Sub

The code is for a combobox embedded in an Excel spreadsheet. It runs with no errors, but it has a strange irregularity. From the third line of code, if the IF statement is false, that is, if nothing is in Worksheets(1).Range("A6"), the If statement skips to the Else portion and evaluates the consecutive arguments. The irregularity occurs after the msgbox pops up. I want the combobox to reset its value to the first item in the list, namely, a tilde (~)--which it does. However, as soon as that code executes, it returns to the top of the code and runs through it again. The end result is that the combobox will return to its correct state, but the user will have to click on the msgbox twice. Is there any way to keep this from happening? I know it's a small thing, but I don't understand why it is doing it and I would like to know how I can prevent it.

Thank you!!

 

[CajunCenturion]

==> I know it's a small thing, but I don't understand why it is doing it and I would like to know how I can prevent it.
The event is a change event, and from inside the change event, you're making to the parent object. A new change event is triggered with the assignment statement, 'SN3.Value = "~"', which in turn triggers another change event. It doesn't trigger a third change event because the second time through, SN3 is already '~' so the assignment statement doesn't change the value.

You prevent it by not making a change from within the change event, or in the code, using flags, identify when the change is programmatic (i.e. the assignment statement) or interactive (i.e. caused by user), and only execute the change logic on an "interactive" change.


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[smma]

Clever! I hadn't realized that, but it makes sense now. About your recommmendation, I'm not exactly following you. What flag should I use to identify the change? Is there an example that I could look at that would show a similar condition?

Thanks,

 

[CajunCenturion]

You might be able to use the .Tag property of the ComboBox as the flag.

Private Sub SN3_Change()

   IF SN3.Tag <> "ProgChange" Then
      cRng = 6
      If Worksheets(1).Range("A6") <> "" Then
         CfgName = Worksheets(1).Range("A6")
      Else
         MsgBox "You must enter a Part Number before selecting a Paint Specification."
         SN3.Tag = "ProgChange"
         SN3.Value = "~"
         Exit Sub
      End If
      If Worksheets(1).Range("A2") = "SLDASM" And Worksheets(1).Range("A6") <> "" Then
         SetColor = MsgBox("Set components to configuration color?", vbYesNo, "Set Color")
         If SetColor = vbYes Then
            SetCompColor
         ElseIf SetColor = vbNo Then
         End If
      End If
   Else
      SN3.Tag = ""
   End If

End Sub

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein