Why is a (Excel VBA) combobox change event triggering every time one of its properties is referenced?

Alphaboss7

New Member
Joined
Jul 31, 2017
Messages
28
I'm having issues with my combobox. I basically have an "Edit Employee" userform that has a combobox referencing current employees in the spreadsheet. Upon selecting the employee of your choice, the rest of the userform is populated by pulling information throughout the workbook. I have a textbox that is populated with the value of the combobox to even allow the user to update the spelling of the name. However, after clicking OKAY to unload the userform, the userform cycles back through the code that pulled values into the userform based off the combobox and ignores the updated values throughout the rest of the userform except for the updated name in the textbox.

This is the code that pulls data into the userform based on the name selected from the combobox:
VBA Code:
'Employee & Comp Info page updates when name is selected from combobox
    Private Sub ComboBox47_Change()
           
    Dim rownumb As Integer
    Dim boranumb As Integer
    Dim knowval As String
           
        rownumb = Application.WorksheetFunction.Match(UserForm5.ComboBox47.Value, Sheets("Assigned Points").Range("A4:A100"), 0) + 4    'identifies the row the name is on
        boranumb = Application.WorksheetFunction.Match(UserForm5.ComboBox47.Value, Sheets("Assigned Points").Range("A4:A100"), 0) + 1   'identified on the benefits sheet OR the ability sheet (benefit or ability number - boranumb)
        knowval = Application.WorksheetFunction.VLookup(UserForm5.ComboBox47.Value, Sheets("Input Sheet1").Range("C4:J100"), 8, False)  'count of knowledge levels to help assist the option buttons in identifying which one is selected
    
    
    'This assigns a value of True or False to the knowledge level tab in the userform according to what is selected
       
        For m = 1 To 12
            If Sheets("Knowledge Level").Range("C" & m + 1).Value = knowval Then
                Sheets("Knowledge Level").Range("D" & m + 1).Value = True
                Me.Controls("optionbutton" & m + 34).Value = Sheets("Knowledge Level").Range("D" & m + 1).Value
            Else
                Sheets("Knowledge Level").Range("D" & m + 1).Value = False
                Me.Controls("optionbutton" & m + 34).Value = Sheets("Knowledge Level").Range("D" & m + 1).Value
            End If
        Next m
    
  
    'This pulls in identifying employee informaton from the input sheet back onto the userform for editing purposes
        Title_Lookup = Application.WorksheetFunction.VLookup(UserForm5.ComboBox47.Value, Sheets("Input Sheet1").Range("C4:J100"), 2, False)         'Job Title ID
        Description_Lookup = Application.WorksheetFunction.VLookup(UserForm5.ComboBox47.Value, Sheets("Input Sheet1").Range("C4:J100"), 3, False)  'Job Description ID
        Wage_Lookup = Application.WorksheetFunction.VLookup(UserForm5.ComboBox47.Value, Sheets("Input Sheet1").Range("C4:J100"), 4, False)         'Wage ID
    
  
    'This code should pull the data values into the userform for editing purposes
        UserForm5.TextBox5.Text = UserForm5.ComboBox47.Value                                'Name adjustment
        UserForm5.ComboBox46.Value = Title_Lookup                                           'Job Title pull
        UserForm5.Label106.Caption = Description_Lookup                                     'Job Description pull

  End Sub

This is the code for pushing the updated values back out to the spreadsheet, but the yellow portion makes the code loop back up through the code above.
VBA Code:
'Employee & Comp Info page "Submit" button
    Private Sub CommandButton4_Click()
    On Error Resume Next
    
        rowchange = Application.WorksheetFunction.Match(UserForm5.ComboBox47.Value, Sheets("Assigned Points").Range("A4:A100"), 0) + 3    'identifies the row the name is on
        borachange = Application.WorksheetFunction.Match(UserForm5.ComboBox47.Value, Sheets("Assigned Points").Range("A4:A100"), 0) + 1   'identified on the benefits sheet OR the ability sheet (benefit or ability number - boranumb)
        cknow = Application.WorksheetFunction.CountA(Range("Know_Level"))           'count of knowledge levels to help assist the option buttons in identifying which one is selected
    
    
    'This assigns a value or True or False to the knowledge level sheet according to the option button selected on the userform to allow the proper reference to be pulled later
        For m = 1 To cknow
            Sheets("Knowledge Level").Range("B" & m + 1).Value = Me.Controls("optionbutton" & m + 34).Value
        Next m
    
  
    'These variables capture the point value assigned with these employee information factors, these values are impartial to job factor weightings
        Title_Lookup = Application.WorksheetFunction.VLookup(UserForm5.ComboBox46.Value, Sheets("Employee Info & Comp").Range("$A$3:$B$100"), 2, False)         'This identifies the job title point value
        Description_Lookup = Application.WorksheetFunction.VLookup(UserForm5.Label106.Caption, Sheets("Employee Info & Comp").Range("$D$3:$E$100"), 2, False)   'This identifies the job description point value according to the job selected
        Wage_Lookup = Application.WorksheetFunction.VLookup(UserForm5.ComboBox1.Value, Sheets("Employee Info & Comp").Range("$G$3:$H$100"), 2, False)           'This identifies the wage substantial point value
        Know_Level = Application.WorksheetFunction.VLookup(True, Sheets("Knowledge Level").Range("B2:C13"), 2, False)                                           'This identifies which knowledge level option is TRUE
    
  
    'This code transports the assigned values and userform inputs to the spreadsheet in various output locations to generate the Employee ID
        [COLOR=rgb(247, 218, 100)]Sheets("Assigned Points").Range("A" & rowchange).Value = UserForm5.TextBox5.Value[/COLOR]        'Name input to "Assigned Points" sheet
        Sheets("Input Sheet1").Range("D" & rowchange).Value = UserForm5.ComboBox46.Value          'Job Title output onto "Input Sheet1"
        Sheets("Assigned Points").Range("D" & rowchange).Value = Title_Lookup                    'Job Title value from the variable above dumped onto the "Assigned Points" sheet
 End Sub

I'm not sure how to get this code to stop triggering the combobox change event when I click the submit button. One suggestion I saw from a similar post on another forum suggested caching the range being referenced, but I'm not sure how to do this after several hours of research.

Anything will help.

Thanks,
AB7
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
VBA Code:
Application.EnableEvents = False
'Update name in ComboBox & Spreadsheet
Application.EnableEvents = True
 

Watch MrExcel Video

Forum statistics

Threads
1,129,795
Messages
5,638,370
Members
417,023
Latest member
Zimbo38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top