I was attempting to use a simple ActiveX combobox to display a dropdown list to the user. Sounds simple enough, but my issue is that when I switch out of Design mode to text the interaction with the drop down, and select an item from that dropdown list, it will not update what is populated in the list until after I click a cell elsewhere on the spreadsheet. And similarly, if I then pick a different item, it will not update from the previously selected item to the most recent pick until clicking another cell.
I've tried creating a new blank spreadsheet with a simple list as shown to make sure it's not something else affecting, and it still behaves the same. I was expecting it to update as soon as I click the selection from the list. I emailed the file to a coworker, and it does seem to behave as expected for him, also running the same MS 365 Excel. Is there a setting somewhere within Excel that would control this response?
Edit to add: it DOES immediately update a cell that it's linked to, though, so that part works great. It's just that it will confuse an end user thinking they haven't actually changed the selection in the drop down (until they decide to click another cell on the sheet).
Edit #2: I realize it's probably helpful to list what's in the code section for VB. I'm new to this so just figured out where that is. For the ComboBox1 section: (the dropdown at the top has "Change" selected) Private Sub ComboBox1_Change()
End Sub For Worksheet: (SelectionChange is selected in the dropdown) Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub