VBA Combobox returns to top of code
VBA Combobox returns to top of code
(OP)
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!!
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!!





RE: VBA Combobox returns to top of code
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
RE: VBA Combobox returns to top of code
Thanks,
RE: VBA Combobox returns to top of code
CODE
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