Transfer value from cell to ComboBox avoiding triggering said ComboBox change

Question

Basically that's my question. I have comboBoxes that affect other ComboBoxes on change and that's all perfect, but I have a need to be able to get the value back from cell to ComboBox on a separate Excel file open and like iI have it written now - it triggers change ComboBox and affects other ComboBoxes which now will be empty and that's bad. What I basically need is to maintain selected items in ComboBoxes once opening the saved workbook once again.

The way I have it written from ComboBox to Sheet:

'Paste value to sheet
Worksheets("Machine Specification").Range("C4").Value = UserForm_TVPM.Language_ComboBox.Text

And the way I get it loaded back to ComboBox from sheet:

UserForm_TVPM.Language_ComboBox.Value = Worksheets("Machine Specification").Range("C4").Value

Same issue occurs upon unloading and showing the UserForm. SInce I have to populate the now empty ComboBoxes.

Could someone, help me solve this problem, please?

P.S.

I have a module that has many subroutines with load functions such as provided in the original post for each userform. That's how I get the value from cell to combobox. AND I have a userform initialize subroutine that mostly has additem function for all the comboboxes in said userform. Later on while user is working with the file - all is fine as I can hide userforms without losing the date in it's combopboxes, BUT upon unloading the userform in any way (weather closing the workbook or simply going to another userform that unloads the previous) the data will be lost and on next initialize of the userform will be loaded back in. Because I have comboboxes that items are dependant on selection in some other combobox - in this scenario I lose the calculated value in userform AND in worksheet.

My understanding is that it happens because upon loading the userform, it's all blank, and upon loading the values it triggers each combobox change event causing some dependant combobox items to be cleared recalculated to provide a correct range of options to select from, and that in turn deletes the dependant comboboxes value in the worksheet because of the transferring combobox value to worksheet. So I generally need to maintain all the combobox values as they were sent to worksheet upon loading a userform, much like it would be if I were to hide a userform and then show it again. :)

Did you try Application.EnableEvents? docs.microsoft.com/en-us/office/vba/api/… — Алексей Р
Yeah, I'm not sure If I used it correctly. I basically disabled it in change sub and after all teh functions enabled it and it didn't have any effect — Eduards
@АлексейР somehow Application.EnableEvents does not disable events from user forms and its controls but only events from the worksheets. — Pᴇʜ
In taht case I don't understand the concept of it at all. It's not only about saving... it's the same upon unloading and showing the now blank UserForm taht now has to be populated with values from Worksheet — Eduards

Pᴇʜ Pᴇʜ · Accepted Answer · 2021-05-06T06:19:54

You can set the ControlSource of the ComboBox to your cell. So it always shows the same value as the cell and you don't need to paste it from the ComboBox to the cell and vice versa.

UserForm_TVPM.Language_ComboBox.ControlSource = "'Machine Specification'!C4"

Or set the ControlSource in the propery window of the combo box.

The only other idea I have is to have a global variable as switch.

Option Explicit

Private SkipChangeEvent As Boolean

Private Sub Language_ComboBox_Change()
    If SkipChangeEvent Then Exit Sub
    
    'your code here
End Sub

Public Sub Example()
    SkipChangeEvent = True   'disable change event
    UserForm_TVPM.Language_ComboBox.Value = Worksheets("Machine Specification").Range("C4").Value
    SkipChangeEvent = False  'enable change event
End Sub

But I would prefer the first solution.

According comments I propose another solution:

In every userform you want to use that put on top:

Option Explicit

Private IsInitialized As Boolean 'this is false by default and false after every unload too.

This has to be private and every userform needs its own variable declaration otherwise it does not work with multiple userforms.

In your change events do

Private Sub Language_ComboBox_Change()
    If Not IsInitialized Then Exit Sub ' events don't run until you tell the userform IsInitialized is true.
    
    'your code here
End Sub

Where ever you have your code to initialize the values of your combo boxes put the following after initialization is finished:

IsInitialized = True

Transfer value from cell to ComboBox avoiding triggering said ComboBox change

1 Answers