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.
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