Selected item in combobox at DropButtonClick

[RB Smissaert]

On a userform I have a combobox with a number of integer numbers that are
years.
On DropButtonClick I would like to have a certain item (the current year)
selected and
have that item at about the middle (height-wise) of the combobox.
The combobox is populated like this:

For i = 1 To 111
arrYears(i) = Year(Date) - (101 - i)
Next i

With ComboBox1
.List = arrYears
.ListIndex = 100
.ListWidth = 48
.ColumnWidths = 48
End With

How could I do this without using SendKeys or the Windows API?


RBS

 

[Rick Rothstein]

Try setting the ListIndex property to 101...

..ListIndex = 101

 

[RB Smissaert]

Where (at what event) to do that?
That will just cause the year 2010 to be selected and it will still be at
the top.

RBS

 

[Peter T]

If I follow, this worked for me

Private Sub UserForm_Click()
Dim nIdx As Long
Dim arrYears(1 To 111)
For i = 1 To 111
arrYears(i) = Year(Date) - (101 - i)
Next i
nIdx = 100
With ComboBox1
.List = arrYears
.ListIndex = IIf(nIdx - 4 > 0, nIdx - 4, 0)
.ListWidth = 48
.ColumnWidths = 48
.DropDown
.ListIndex = nIdx
End With
End Sub

Regards,
Peter T

 

[Peter T]

Actually I didn't quite follow, or rather I didn't read "On DropButtonClick
" carefully. Think this will require an Ontime macro -

Private Sub ComboBox1_DropButtonClick()
ComboBox1.ListIndex = 96
Application.OnTime Now, "aaa"
End Sub

Private Sub UserForm_Initialize()
Dim arrYears(1 To 111)
For i = 1 To 111
arrYears(i) = Year(Date) - (101 - i)
Next i

With ComboBox1
.List = arrYears
.ListIndex = 100
.ListWidth = 48
.ColumnWidths = 48
End With

End Sub

' in a normal module
Sub aaa()
UserForm1.ComboBox1.ListIndex = 100
End Sub

Regards,
Peter T

 

[RB Smissaert]

Hi Peter,

It works but it has some strange side-effects in that the years get
highlighted on mouse-over and
clicking the years then doesn't work as expected.
I have this combobox on a userform that also has a MonthView and on clicking
the year in the combobox
that particular year should show in the MonthView and that doesn't happen
anymore with this code.
All this is to achieve only minor cosmetic improvement and it looks it isn't
worth all this trouble.

RBS

 

[Peter T]

Hi Bart,

The "highlighted on mouse-over" is normal and should not be a problem.

Take changes from the Change event and not from the Click event. Include
some escapes to ignore "temporary" changes to the listindex

Public gbExitComboBox1 As Boolean

Private Sub ComboBox1_Change()
Static vLastValue
Static n As Long

If gbExitComboBox1 Then
gbExitComboBox1 = False
ElseIf vLastValue <> ComboBox1.Value Then
n = n + 1
vLastValue = ComboBox1.Value
Cells(n, 1) = vLastValue
End If
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim nIdx As Long
nIdx = ComboBox1.ListIndex
gbExitComboBox1 = True
ComboBox1.ListIndex = IIf(nIdx - 4 > 0, nIdx - 4, 0)
Application.OnTime Now, "'aaa " & nIdx & "'"
End Sub

Private Sub UserForm_Initialize()
Dim arrYears(1 To 111)
Range("A:A").ClearContents
For i = 1 To 111
arrYears(i) = Year(Date) - (101 - i)
Next i
'gbExitComboBox1 = True ' uncomment to disable initial change event
With ComboBox1
.List = arrYears
.ListIndex = 100
.ListWidth = 48
.ColumnWidths = 48
End With

End Sub

'''''''' normal module
Sub aaa(idx As Long)
gbExitComboBox1 = True
UserForm1.ComboBox1.ListIndex = idx
End Sub

Regards,
Peter T