ComboBox display incorrect text

[nvu]

I have a ComboBox which display the text of the cell underneath it. However, it seems that the Combobox just refuse to display the correct text. As you can see in the screenshot


The Text property is different from the displaying text. It's the previous value.
ScreenUpdating is True
The combobox is enabled
There is only 1 combobox, no other objects/shapes/buttons/forms. And a single table in this sheet.

29950

Other information:


Problematic combobox is in Sheet LinhKien in the file attachment. other comboboxes work fine.
The combobox is hidden when user is not selecting column 1 or select more than 1 cell. It becomes visible when a cell in column 1 is selected.
I have 2 other sheets with Comboboxes behave the exact same way (hidden when not in certain column, text comes from underneath cell) but they don't have this problem.


I think there is something wrong with my settings/set up. If the code is of relevant, here it is.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
DoEvents
If Selection.Count > 1 Then Exit Sub
If Application.CutCopyMode Then
searchBoxAccessories.Visible = False
Exit Sub
End If

If searchBoxAccessories Is Nothing Then
Set searchBoxAccessories = ActiveSheet.OLEObjects("SearchCombBoxAccessories")
End If


If Target.Column = 1 And Target.Row > 3 Then
Dim isect As Range
Set isect = Application.Intersect(Target, ListObjects(1).Range)
If isect Is Nothing Then GoTo DoNothing
isInitializingComboBox = True
GetSearchAccessoriesData
searchBoxAccessories.Activate

isInitializingComboBox = True 'This prevent "_Change" fires up when something changes'

searchBoxAccessories.Top = Target.Top
searchBoxAccessories.Left = Target.Left
searchBoxAccessories.Width = Target.Width + 15
searchBoxAccessories.Height = Target.Height + 2
Application.EnableEvents = False 'Another attempt to prevent "_Change" fires up when something changes'
searchBoxAccessories.Object.text = Target.text
Application.EnableEvents = True
searchBoxAccessories.Object.SelStart = 0
searchBoxAccessories.Object.SelLength = Len(Target.text)
searchBoxAccessories.Visible = True
isInitializingComboBox = False 'Screenshot is taken here'
Set workingCell = Target
Else
DoNothing:

If searchBoxAccessories Is Nothing Then
Set searchBoxAccessories = ActiveSheet.OLEObjects("SearchCombBoxAccessories")
End If

If searchBoxAccessories.Visible Then searchBoxAccessories.Visible = False
End If

End Sub

[p45cal]

I don't know what's going on!
Try adding the red to:
Private Sub SearchCombBoxAccessories_LostFocus()
If Not isInitializingComboBox Then
If SearchCombBoxAccessories.ListIndex < 0 Then
MsgBox "Not allowed"
' MsgBoxUni 7, vbOKOnly, 4
SearchCombBoxAccessories.Activate
Exit Sub
End If

If workingCell Is Nothing Then Set workingCell = Selection
workingCell.Value = SearchCombBoxAccessories.Value
End If
End Sub

and moving the .Visible lines as well as add the line before each:
searchBoxAccessories.Top = Target.Top
searchBoxAccessories.Left = Target.Left
searchBoxAccessories.Width = Target.Width + 15
searchBoxAccessories.Height = Target.Height + 2
Application.EnableEvents = True
searchBoxAccessories.Object.text = Target.text
Application.EnableEvents = True
searchBoxAccessories.Object.SelStart = 0
searchBoxAccessories.Object.SelLength = Len(Target.text)
isInitializingComboBox = True
searchBoxAccessories.Visible = False
isInitializingComboBox = True
searchBoxAccessories.Visible = True
isInitializingComboBox = False
Set workingCell = Target
Set unitPriceCell = workingCell.Cells(1, 4)
Set unitTypeCell = workingCell.Cells(1, 2)
but it worked here.

[nvu]

I also don't know what's going on but it still display wrong here, before and after I added codes per your suggestion. So you run the same workbook I uploaded right? You did not create new one then recreate the code, right? Does all the comboboxes work as expected to you? I ONLY have problem of not updating with the one in sheet LinhKien.

[p45cal]

Made those changes to a fresh download of your workbook - see if the attached works.

[snb]

I can only say it's horribly overcomplicated spaghetti-code

I adapted the Combobox's property boundcolumn to 3, and style to 2.
I reduced the code in sheet22 to:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count > 1 Then Exit Sub

If Not Intersect(Target, ListObjects(1).ListColumns(1).Range) Is Nothing Then
With OLEObjects(1)
.Object.List = Sheet33.ListObjects(1).Range.Value
.Top = Target.Top
.Left = Target.Left + 15
.Width = Target.Width + 2
.Object.Value = Target.Value
.Visible = True
End With
End If
End Sub

Private Sub C_01_Change()
With C_01
If .ListIndex > -1 Then
.TopLeftCell.Offset(, 2) = .Column(4)
.TopLeftCell.Offset(, 3) = .Column(4)
End If
End With
End Sub

[nvu]

I finally found out what went wrong!!! It's the Freeze panes. After I remove that, there are a lot of things could be simpler and the ComboBox display properly, timely, and correctly (with whole text highlighted). No need to turn on/off visibility. I suspect that the freezed column are updated at less frequent interval to save resource by Excel. I found out because I moved the combobox out of column 1 (in an attempt to enhance the visual) and it suddenly looks normal. After some more fidgeting, I finally nail down the cause. That's why my different sheets behave correctly while this one will not.


Made those changes to a fresh download of your workbook - see if the attached works.

[arnelgp]

:clap:

[nvu]

I appreciate the criticism, I will implement some of your suggestions. But I feel not naming things will make it harder to understand later.


I can only say it's horribly overcomplicated spaghetti-code

I adapted the Combobox's property boundcolumn to 3, and style to 2.
I reduced the code in sheet22 to:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count > 1 Then Exit Sub

If Not Intersect(Target, ListObjects(1).ListColumns(1).Range) Is Nothing Then
With OLEObjects(1)
.Object.List = Sheet33.ListObjects(1).Range.Value
.Top = Target.Top
.Left = Target.Left + 15
.Width = Target.Width + 2
.Object.Value = Target.Value
.Visible = True
End With
End If
End Sub

Private Sub C_01_Change()
With C_01
If .ListIndex > -1 Then
.TopLeftCell.Offset(, 2) = .Column(4)
.TopLeftCell.Offset(, 3) = .Column(4)
End If
End With
End Sub

[p45cal]

I finally found out what went wrong!!! It's the Freeze panes.Thanks for telling us!

[nvu]

Thanks for helping.

Thanks for telling us!

Since this "feature" could be version specific, for future searcher, my system is Win 10 pro, Excel 16 pro plus.