Excel ActiveX ComboBox Displays Previous Value Issue

3
votes

Issue with Embedded ActiveX ComboBox form on a spreadsheet where:

  • Upon changing the value in the ComboBox, the value changes properly

  • However, when a cell/shape is selected on the sheet, the ComboBox value reverts back to the previous value for a split second before going back to the new value

  • Problem because: If a button for a macro is pressed after changing the ComboBox value, the old ComboBox value is displayed while the macro is running, rather than the new value

Question: Is there a way to force this event (reverting to old value before going displaying new value) programmatically?

I've tried using the following in the Change event for the ComboBox, as well as within the macro that is called by another shape on the sheet:

  • Calculating the worksheet
  • Selecting/Activating a cell
  • ScreenUpdating = false, ScreenUpdating = true

Did a bit more searching and have found this question Excel ActiveX Combobox shows previous selection when losing focus with the same issue

3
excelvbauser-interfacecomboboxactivex
having trouble replication this, do you have any worksheet events or combo box events set up? – learnAsWeGo
I've replicated the issue in a totally fresh workbook. Note that I am using an ActiveX ComboBox, that has a linked cell whose value does not have this 'flicker' issue -- only the actual ComboBox has this issue. Another note is that the ComboBox uses a list range to populate its values. – ericauv
ok was able to replicate – learnAsWeGo

3 Answers

1
votes

Added a LostFocus event to the ComboBox that had a line that selected a cell. This removed the "flicker" to the previous value when another cell on the sheet was selected, and also caused the ComboBox value to "flicker" back to the new value after "flickering" to the old value when a command button was clicked after changing the ComboBox's value...

Therefore this solved my issue (mostly -- was unable to prevent the "flicker" from happening upon running another macro via command button, but at least the value does not remain stuck at the old value while the other macro runs)

Idea came from this thread: What event is triggered when user selects value from drop down ComboBox (ActiveX)?

1
votes

Will start from here:

I created a comboBox with various fruits as items inside.

I clicked on pear. Then pineapple. then clicked on cell A1, firing the lost focus event.

Private Sub ComboBox1_LostFocus()
    Debug.Print ComboBox1.Value
    Debug.Print ComboBox1.Value
    Debug.Print ComboBox1.Value
End Sub

This was text in my immediate window: pineapple pineapple pineapple

This makes me think that it is a rendering issue, not a value change, though maybe it is happening so fast that my debug.print doesn't catch it.

Interestingly if you make the calls from inside VBA, the flash does not occur:

Sub Main()
    ComboBox1.Value = "mango"
    Range("A1").Select
End Sub

Nor if you just run

    ComboBox1.Value = "mango"

then click on the worksheet and select a cell

0
votes

I have one solution that is sort of a work around.

I inserted an ActiveX Label to the worksheet (on the same location as the ComboBox) and set the labels visible state to False.

Everytime the drop-down box closes, the focus is set on the invisible label

First a global variable.

 Public DropDownBegin As Boolean

Then the sub-routine

Private Sub ComboBox1_DropButtonClick()
    DropDownBegin = Not DropDownBegin
    If Not DropDownBegin Then
        ActiveSheet.Shapes("Label1").OLEFormat.Object.TopLeftCell.Select
    End If
End Sub