nvu
07-12-2022, 02:56 AM
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
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