Search code examples
excelvbacombobox

Excel ComboBox - typed text not displaying until focus lost from combobox

I have a Combobox where as you type, the dropdown list dynamically populates with results that match the input that was typed.

The issue that i am facing is that as you type in the combobox you cannot see what you have typed. in the image below you can see that there is a cell with "Build" - this is the Linked cell i have temporarily set to show whats been typed.

The white box between the above cell and the list is where i am typing. But as you can see there is no text visible.

enter image description here

After clicking elsewhere on the sheet the typed text appears

enter image description here

What i want to happen (and im sure used to) is that while i am typing the text in the combobox should show what is being typed as it is typed.

On the assumption that this is related to the vba code i have set up i have copied that below and the properties of the combobox.

Having searched for hours, i havent come across a relevent post or solution to this issue.

enter image description here

    Private Sub OccupationComboBox_Change()
    Call OccupationComboBox_Populate
    OccupationComboBox.DropDown
End Sub
Private Sub OccupationComboBox_DropButtonClick()
    Call OccupationComboBox_Populate
    OccupationComboBox.DropDown
End Sub
Private Sub OccupationComboBox_Click()
    Call OccupationComboBox_Populate
    OccupationComboBox.DropDown
End Sub

Private Sub OccupationComboBox_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Call OccupationComboBox_Populate
    OccupationComboBox.DropDown
End Sub

Sub OccupationComboBox_Populate()
    
    Dim arrIn As Variant, arrOut As Variant
    Dim i As Long, j As Long

    arrOccupation = Sheets("Occupation classes").Range("B2:B2000")
    arrOccupationKeys = Sheets("Occupation classes").Range("C2:C2000")
    arrOccupationCode = Sheets("Occupation classes").Range("A2:A2000")
    ReDim arrCodeOut(1 To UBound(arrOccupation), 1 To 1)
    ReDim arrOut(1 To UBound(arrOccupation), 1 To 1)

    For i = 1 To UBound(arrOccupationKeys)
        If LCase(CStr(arrOccupationKeys(i, 1))) Like "*" & LCase(OccupationComboBox.Text) & "*" Then
            'If CStr(arrIn(i, 1)) Like OccupationComboBox.Text & "*" Then
            j = j + 1
            arrOut(j, 1) = arrOccupation(i, 1)
            arrCodeOut(j, 1) = arrOccupationCode(i, 1)
        End If
    Next
    OccupationComboBox.List = arrOut
    Range("G18") = arrCodeOut(1, 1)
    If Range("D18").Value = "" Then
        Range("G18") = ""
    End If
    ActiveSheet.Calculate
    
End Sub

As an update, it appears that the same thing happens with a text box. after selecting the text box i start typing but i am unable to see what has been typed until i select another cell in the worksheet . nor in eithercase can a highlight the text in either the combobox or text box

Solution

  • It turns out that there is sometimes a bug relating to multiple monitors.

    after changing my pc's projection mode to this screen only everything worked as intended.

    If your having this same issue try

    • dragging the s/s you are working on to your number 1 monitor or
    • changing your projection mode to this screen only