Excel ActiveX Combobox shows previous selection when losing focus

Question

I have this code which fills a combobox on Sheet1 with the Name column of Table1 on Sheet2.

Public Sub Worksheet_Activate()
   Me.ComboBox1.List = Worksheets("Sheet2").ListObjects("Table1")_
   .ListColumns("Name").DataBodyRange.Value
End Sub

Works fine but it has a weird effect when I click off the combobox onto the sheet. The selected entry in the box quickly flashes to the previous entry. For example, the currently selected item is "b" and then I select "c". If I click on the worksheet the entry in the box quickly flashes to "b" before going back to "c".

I've put this code alone in a new file and I still get the same effect. Has anyone else seen this?

Edit regarding reason for Public Sub:

Forgot to include the Workbook_Open code so that Sheet1 is considered Activated when you open the Workbook. But it doesn't matter if I keep that code or not, I still see the effect.

Private Sub Workbook_Open()
Call ActiveSheet.Worksheet_Activate
End Sub

Happens for me even without VBA code so I guess you're stuck with it — xthestreams
well that's annoying. I searched and didn't see anyone else mention it before. It's the kind of thing that just nags at me. — bigbucky
Not sure why it is a Public Sub, but I don't get this phenomenon on Excel 2010. Your code is in Sheet1 object? — PatricK
Yes it's in Sheet1. This is Excel 365. I forgot to include the Workbook_Open code which calls Worksheet_Activate. That's why it's Public. Otherwise when you first open up the Worksheet it's not considered Activated so you won't get the drop down list. — bigbucky

ericauv ericauv · Accepted Answer · 2018-07-19T01:11:02

Adding a LostFocus event with code that selects a cell on your worksheet should cause the flicker not to happen when you select a cell after changing the ComboBox's value.

Like the following:

Private Sub ComboBox1_LostFocus()
    ActiveSheet.Range("A1").select
End Sub

Excel ActiveX Combobox shows previous selection when losing focus

1 Answers