ComboBox last selection

[donwb]

Excel 2003
I have a ComboBox loaded with 30 rows and a dropdown list set to display 9
rows.
When the DropDown button is clicked it displays the first 8 rows in the
list.
How can I make it highlight the last selection when the dropdown button is
clicked and display
the 4 rows either side.
donwb

 

[OssieMac]

I think that this is what you are looking for. I tested it on a worksheet but
if you are using a Userform then it will probably work if you replace the
Sheet1 with the userform name (or or alias Me.)

Private Sub ComboBox1_GotFocus()
Sheet1.ComboBox1.DropDown
If Sheet1.ComboBox1.ListIndex > 3 Then
Sheet1.ComboBox1.TopIndex = _
Sheet1.ComboBox1.ListIndex - 4
End If
End Sub

 

[OssieMac]

Just so you don't have to wait for another answer if you get into trouble if
the combo box is on a userform here is the code. On userform it is the Enter
Event while on worksheet it is GotFocus event.

Private Sub ComboBox1_Enter()
Me.ComboBox1.DropDown
If Me.ComboBox1.ListIndex > 3 Then
Me.ComboBox1.TopIndex = _
Me.ComboBox1.ListIndex - 4
End If
End Sub

 

[donwb]

Thanks Ossiemac for the responses.
I will try the code you suggest.
What I don't understand though, is that On my UserForm,
apart from the "troublesome" ComboBox (populated with Year values
from 1900 to 2050 but limited to displaying 9 rows),
I have an almost identical one populated with Month names
from January to December also only displaying 9 rows and the latter
does exactly what I require.
I can see no difference in any settings between them.
Peculiar.
donwb

 

[OssieMac]

Hi again,

My apologies for not getting back to you sooner but the Olympics on TV are
playing havoc with my time.

Something you need to realize is that with a combo box displaying 9 rows, if
the existing value is within the last 4 values in the list then the combo
must display 9 rows. It will not display the existing value in the middle and
leave blank rows at the bottom. It will display the last 9 rows of the list
and the previously selected value will be where ever it falls in the
sequence. Conversely with the first rows in the list. it can't display blank
rows at the top. The code has to handle the top rows or an error occurs
trying to set the TopIndex but it does not appear to generate any errors for
the bottom rows so I did not bother trying to do anything special with them.

My previous code was not quite correct and I have had another look at it.
From your last post, I am assuming that you are trying to pick dates from 3
combo boxes for Year, Month and Day. Is my assumption correct? If so then the
following code might help.

Private Sub ComboYear_Enter()
'ListBox RowSource set to Sheet1.Range("A1:A151)
'which is 1900 to 2050

Me.ComboYear.DropDown
If Me.ComboYear.ListIndex - 4 >= 0 Then
Me.ComboYear.TopIndex = _
Me.ComboYear.ListIndex - 4
Else
Me.ComboYear.TopIndex = 0
End If
End Sub

Private Sub ComboMonth_Enter()
'ListBox RowSource set to Sheet1.Range("B1:B12)
'which is January to December

'Because the ComboBox has 9 ListRows,
'if the value is within the last 4 rows
'then it must display the last 9 rows of the list.

Me.ComboMonth.DropDown
If Me.ComboMonth.ListIndex - 4 >= 0 Then
Me.ComboMonth.TopIndex = _
Me.ComboMonth.ListIndex - 4
Else
Me.ComboMonth.TopIndex = 0
End If
End Sub

Private Sub ComboDay_Enter()

'Must select year first to determine if leap year.
If Me.ComboYear.Value >= 1900 And Me.ComboYear <= 2050 Then

Select Case Me.ComboMonth.Value
Case "January", "March", "May", "July", _
"August", "October", "December"

Me.ComboDay.RowSource = _
"Sheet1!C1:C31"

Case "April", "June", "September", _
"November"

Me.ComboDay.RowSource = "Sheet1!C1:C30"

Case "February"
If Me.ComboYear.Value Mod 4 = 0 Then
'Leap year
Me.ComboDay.RowSource = _
"Sheet1!C1:C29"
Else
'Not a leap year
Me.ComboDay.RowSource = _
"Sheet1!C1:C28"
End If
Case Else
'Must select month to determine number of
'days in the dropdown.
MsgBox "Require Month selection first"
Me.ComboMonth.SetFocus
End Select

Me.ComboDay.DropDown
Else
MsgBox "Require Year selection first"

End If

If Me.ComboDay.ListIndex - 4 >= 0 Then
Me.ComboDay.TopIndex = Me.ComboDay.ListIndex - 4
Else
Me.ComboDay.TopIndex = 0
End If

End Sub

 

[OssieMac]

Hi yet again,

I still did not have the code correct. Missed a couple if Exit Sub's after
the MsgBox's if Year or Month not selected. Try this one. Also feel free to
get back to me again if you are still having problems with it.


Private Sub ComboYear_Enter()
'ListBox RowSource set to Sheet1.Range("A1:A151)
'which is 1900 to 2050

Me.ComboYear.DropDown
If Me.ComboYear.ListIndex - 4 >= 0 Then
Me.ComboYear.TopIndex = _
Me.ComboYear.ListIndex - 4
Else
Me.ComboYear.TopIndex = 0
End If
End Sub

Private Sub ComboMonth_Enter()
'ListBox RowSource set to Sheet1.Range("B1:B12)
'which is January to December

'Because the ComboBox has 9 ListRows,
'if the value is within the last 4 rows
'then it must display the last 9 rows of the list.

Me.ComboMonth.DropDown
If Me.ComboMonth.ListIndex - 4 >= 0 Then
Me.ComboMonth.TopIndex = _
Me.ComboMonth.ListIndex - 4
Else
Me.ComboMonth.TopIndex = 0
End If
End Sub

Private Sub ComboDay_Enter()

'Must select year first to determine if leap year.
If Me.ComboYear.Value >= 1900 And Me.ComboYear <= 2050 Then

Select Case Me.ComboMonth.Value
Case "January", "March", "May", "July", _
"August", "October", "December"

Me.ComboDay.RowSource = _
"Sheet1!C1:C31"

Case "April", "June", "September", _
"November"

Me.ComboDay.RowSource = "Sheet1!C1:C30"

Case "February"
If Me.ComboYear.Value Mod 4 = 0 Then
'Leap year
Me.ComboDay.RowSource = _
"Sheet1!C1:C29"
Else
'Not a leap year
Me.ComboDay.RowSource = _
"Sheet1!C1:C28"
End If
Case Else
'Must select month to determine number of
'days in the dropdown.
MsgBox "Require Month selection first"
Me.ComboMonth.SetFocus
Exit Sub
End Select

Me.ComboDay.DropDown
Else
MsgBox "Require Year selection first"
Me.ComboYear.SetFocus
Exit Sub
End If

If Me.ComboDay.ListIndex - 4 >= 0 Then
Me.ComboDay.TopIndex = Me.ComboDay.ListIndex - 4
Else
Me.ComboDay.TopIndex = 0
End If

End Sub