ComboBox display wrongly
Vu Nguyen
41
Reputation points
I have a code 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.
Other information:
- Problematic combobox is in sheet LinhKien, other ComboBoxes works fine. I don't know how to upload file here, so it's a 7 days link valid begin from 20220712 (YYYYMMDD)
- 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
Developer technologies | Visual Basic for Applications
Developer technologies | Visual Basic for Applications
An implementation of Visual Basic that is built into Microsoft products.
Sign in to answer