0

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
bigbucky
  • 407
  • 6
  • 20
  • Happens for me even without VBA code so I guess you're stuck with it – xthestreams Dec 12 '17 at 04:56
  • 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 Dec 12 '17 at 05:14
  • 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 Dec 12 '17 at 06:17
  • 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 Dec 12 '17 at 23:49

1 Answers1

0

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
ericauv
  • 160
  • 16