Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selected item in combobox at DropButtonClick
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selected item in combobox at DropButtonClick
Try setting the ListIndex property to 101...
..ListIndex = 101 -- Rick (MVP - Excel) "RB Smissaert" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selected item in combobox at DropButtonClick
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 "Rick Rothstein" wrote in message ... Try setting the ListIndex property to 101... .ListIndex = 101 -- Rick (MVP - Excel) "RB Smissaert" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selected item in combobox at DropButtonClick
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 "RB Smissaert" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selected item in combobox at DropButtonClick
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 "Peter T" <peter_t@discussions wrote in message ... 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 "RB Smissaert" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selected item in combobox at DropButtonClick
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" <peter_t@discussions wrote in message ... 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 "Peter T" <peter_t@discussions wrote in message ... 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 "RB Smissaert" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selected item in combobox at DropButtonClick
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 "RB Smissaert" wrote in message ... 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" <peter_t@discussions wrote in message ... 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 "Peter T" <peter_t@discussions wrote in message ... 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 "RB Smissaert" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to display the first Item in a combobox as the default item | Excel Programming | |||
How to display the first Item in a combobox as the default item | Excel Programming | |||
UserForm Combobox DropButtonClick Event Bug? | Excel Programming | |||
use selected value from one combobox to populate another combobox | Excel Programming | |||
ComboBox fails when last item selected first | Excel Programming |