ComboBox display wrongly

Vu Nguyen 41 Reputation points
2022-07-12T04:24:44.327+00:00

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.

219741-combobox-bad.png

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
{count} votes

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.