excellistboxitemsvba

Excel Listbox listindex does not highlight


Could someone with a better level of excel capability please assist me with the below code? I have set this up in the Worksheet_Activate event. I have code to set an ActiveX listbox to a default value, as shown below. For whatever reason, the listbox is not showing the default value as a highlighted item. All the other logic seems to work fine but it's driving me crazy that the below code won't highlight the stupid first item in my first listbox. What am I doing wrong?

With CTOverview.ListBox1
    .IntegralHeight = True
    .Height = 114.75
    .Width = 125.25
    .IntegralHeight = False
    .ListIndex = 0
    .Selected(0) = True
    .Value = "Entire Division"
End With

CTData.Range("Overview_RegionSelected").Value = CTOverview.ListBox1.Value

With CTOverview.ListBox2
    .IntegralHeight = True
    .Height = 114.75
    .Width = 150
    .IntegralHeight = False
    .ListIndex = -1
End With

Thanks for any help.


Solution

  • I know. I have seen this weird behavior in the past with absolutely no explanation. Sometime it works and sometimes it doesn't. Try this. This will work.

    With CTOverview.ListBox1
        If .ListCount > 1 Then .Selected(1) = True
        .IntegralHeight = True
        .Height = 114.75
        .Width = 125.25
        .IntegralHeight = False
        .ListIndex = 0
        .Selected(0) = True
        .Value = "Entire Division"
    End With
    

    FOLLOWUP

    Here is a better code than the code that I gave you earlier. The above code was restricted to the fact that we needed to have more than 1 Listcount. The below will work for 1 Listcount as well.

    Dim rng As Range, aCell As Range
    
    With CTOverview.ListBox1
        .IntegralHeight = True
        .Height = 114.75
        .Width = 125.25
        .IntegralHeight = False
        .ListIndex = 0
        .Selected(0) = True
        .Value = "Entire Division"
    
        Set rng = Range(.ListFillRange)
        For Each aCell In rng
            aCell.Formula = aCell.Formula
        Next
    End With