×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VBA Combobox returns to top of code

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!!

RE: VBA Combobox returns to top of code

==> 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

RE: VBA Combobox returns to top of code

(OP)
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,

RE: VBA Combobox returns to top of code

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources