Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox (arrow keys pick each item)
Hi:
The following sub is used to populate a listBox of course. The problem is that after it is activated and an item is picked if an arrow key is pressed each item is displayed in the ActiveCell as the pointer moves up or down the list. How can I avoid that? Private Sub ListBox1_Click() Dim SourceRange As Range Dim Val1 As String Dim Val2 As String ' RowSource created with Insert - Name - Define ' Range Name is Data so set ListBox1's Properties RowSource = Data Set SourceRange = Range(ListBox1.RowSource) Val1 = ListBox1.Value Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value ActiveCell.Value = Val1 ActiveCell.Offset(0, 3) = Val2 ActiveCell.Offset(1, 0).Activate End Sub Thanks TK |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox (arrow keys pick each item)
Doing the action you describe is making a selection. I am not sure there is
a way to have your code differentiate whether the user is making a selection or "making a selection" -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: The following sub is used to populate a listBox of course. The problem is that after it is activated and an item is picked if an arrow key is pressed each item is displayed in the ActiveCell as the pointer moves up or down the list. How can I avoid that? Private Sub ListBox1_Click() Dim SourceRange As Range Dim Val1 As String Dim Val2 As String ' RowSource created with Insert - Name - Define ' Range Name is Data so set ListBox1's Properties RowSource = Data Set SourceRange = Range(ListBox1.RowSource) Val1 = ListBox1.Value Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value ActiveCell.Value = Val1 ActiveCell.Offset(0, 3) = Val2 ActiveCell.Offset(1, 0).Activate End Sub Thanks TK |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox (arrow keys pick each item)
you could use the keydown event to cancel the arrow keys, but that would not
allow the user to use them. Is that an option. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: The following sub is used to populate a listBox of course. The problem is that after it is activated and an item is picked if an arrow key is pressed each item is displayed in the ActiveCell as the pointer moves up or down the list. How can I avoid that? Private Sub ListBox1_Click() Dim SourceRange As Range Dim Val1 As String Dim Val2 As String ' RowSource created with Insert - Name - Define ' Range Name is Data so set ListBox1's Properties RowSource = Data Set SourceRange = Range(ListBox1.RowSource) Val1 = ListBox1.Value Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value ActiveCell.Value = Val1 ActiveCell.Offset(0, 3) = Val2 ActiveCell.Offset(1, 0).Activate End Sub Thanks TK |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox (arrow keys pick each item)
Hi Tom
That could be an option if I can't find an alternative but it would make the listBox completely Mouse driven and I would prefer arrow keys to be active.. I worked out the procedure with the ListBox on a sheet and it didn't respond to the arrow keys but in the finish product I wanted the ListBox on a UserForm and then this problem came up. There is quite a bit more to what I'm doing than this sub and you have contributed significantly to my efforts. Thanks for your help. This just happens to be the latest problem. "Tom Ogilvy" wrote: you could use the keydown event to cancel the arrow keys, but that would not allow the user to use them. Is that an option. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: The following sub is used to populate a listBox of course. The problem is that after it is activated and an item is picked if an arrow key is pressed each item is displayed in the ActiveCell as the pointer moves up or down the list. How can I avoid that? Private Sub ListBox1_Click() Dim SourceRange As Range Dim Val1 As String Dim Val2 As String ' RowSource created with Insert - Name - Define ' Range Name is Data so set ListBox1's Properties RowSource = Data Set SourceRange = Range(ListBox1.RowSource) Val1 = ListBox1.Value Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value ActiveCell.Value = Val1 ActiveCell.Offset(0, 3) = Val2 ActiveCell.Offset(1, 0).Activate End Sub Thanks TK |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox (arrow keys pick each item)
Correction:
I just checked out the sheet ListBox and focus stays on the sheet. So I quess it is completely mouse driven. So, how do I code the keydown event to cancel the arrow keys Or kept focus on the active cell in the sheet Thanks "TK" wrote: Hi Tom That could be an option if I can't find an alternative but it would make the listBox completely Mouse driven and I would prefer arrow keys to be active.. I worked out the procedure with the ListBox on a sheet and it didn't respond to the arrow keys but in the finish product I wanted the ListBox on a UserForm and then this problem came up. There is quite a bit more to what I'm doing than this sub and you have contributed significantly to my efforts. Thanks for your help. This just happens to be the latest problem. "Tom Ogilvy" wrote: you could use the keydown event to cancel the arrow keys, but that would not allow the user to use them. Is that an option. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: The following sub is used to populate a listBox of course. The problem is that after it is activated and an item is picked if an arrow key is pressed each item is displayed in the ActiveCell as the pointer moves up or down the list. How can I avoid that? Private Sub ListBox1_Click() Dim SourceRange As Range Dim Val1 As String Dim Val2 As String ' RowSource created with Insert - Name - Define ' Range Name is Data so set ListBox1's Properties RowSource = Data Set SourceRange = Range(ListBox1.RowSource) Val1 = ListBox1.Value Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value ActiveCell.Value = Val1 ActiveCell.Offset(0, 3) = Val2 ActiveCell.Offset(1, 0).Activate End Sub Thanks TK |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox (arrow keys pick each item)
Assuming listbox from the control toolbox toolbar:
When I select an item in a listbox on a worksheet the focus is on the item selected. I can use the arrow keys to move down or up through the list. Anyway, this killed that capability Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 38 Or KeyCode = 40 Then KeyCode = 0 End If End Sub -- Regards, Tom Ogilvy "TK" wrote in message ... Correction: I just checked out the sheet ListBox and focus stays on the sheet. So I quess it is completely mouse driven. So, how do I code the keydown event to cancel the arrow keys Or kept focus on the active cell in the sheet Thanks "TK" wrote: Hi Tom That could be an option if I can't find an alternative but it would make the listBox completely Mouse driven and I would prefer arrow keys to be active.. I worked out the procedure with the ListBox on a sheet and it didn't respond to the arrow keys but in the finish product I wanted the ListBox on a UserForm and then this problem came up. There is quite a bit more to what I'm doing than this sub and you have contributed significantly to my efforts. Thanks for your help. This just happens to be the latest problem. "Tom Ogilvy" wrote: you could use the keydown event to cancel the arrow keys, but that would not allow the user to use them. Is that an option. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: The following sub is used to populate a listBox of course. The problem is that after it is activated and an item is picked if an arrow key is pressed each item is displayed in the ActiveCell as the pointer moves up or down the list. How can I avoid that? Private Sub ListBox1_Click() Dim SourceRange As Range Dim Val1 As String Dim Val2 As String ' RowSource created with Insert - Name - Define ' Range Name is Data so set ListBox1's Properties RowSource = Data Set SourceRange = Range(ListBox1.RowSource) Val1 = ListBox1.Value Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value ActiveCell.Value = Val1 ActiveCell.Offset(0, 3) = Val2 ActiveCell.Offset(1, 0).Activate End Sub Thanks TK |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox (arrow keys pick each item)
Tom: I tried "Your" KeyDown in various locations in "My" Sub without sucess. I couldn't trap the arrow keys within the sub, any suggestions? Tom Ogilvy" wrote: Assuming listbox from the control toolbox toolbar: When I select an item in a listbox on a worksheet the focus is on the item selected. I can use the arrow keys to move down or up through the list. Anyway, this killed that capability Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 38 Or KeyCode = 40 Then KeyCode = 0 End If End Sub -- Regards, Tom Ogilvy "TK" wrote in message ... Correction: I just checked out the sheet ListBox and focus stays on the sheet. So I quess it is completely mouse driven. So, how do I code the keydown event to cancel the arrow keys Or kept focus on the active cell in the sheet Thanks "TK" wrote: Hi Tom That could be an option if I can't find an alternative but it would make the listBox completely Mouse driven and I would prefer arrow keys to be active.. I worked out the procedure with the ListBox on a sheet and it didn't respond to the arrow keys but in the finish product I wanted the ListBox on a UserForm and then this problem came up. There is quite a bit more to what I'm doing than this sub and you have contributed significantly to my efforts. Thanks for your help. This just happens to be the latest problem. "Tom Ogilvy" wrote: you could use the keydown event to cancel the arrow keys, but that would not allow the user to use them. Is that an option. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: The following sub is used to populate a listBox of course. The problem is that after it is activated and an item is picked if an arrow key is pressed each item is displayed in the ActiveCell as the pointer moves up or down the list. How can I avoid that? Private Sub ListBox1_Click() Dim SourceRange As Range Dim Val1 As String Dim Val2 As String ' RowSource created with Insert - Name - Define ' Range Name is Data so set ListBox1's Properties RowSource = Data Set SourceRange = Range(ListBox1.RowSource) Val1 = ListBox1.Value Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value ActiveCell.Value = Val1 ActiveCell.Offset(0, 3) = Val2 ActiveCell.Offset(1, 0).Activate End Sub Thanks TK |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox (arrow keys pick each item)
If you are not selecting the Keydown event from the dropdown in the top of
the module, then you are probably in the wrong place. If the listbox is on a worksheet, then you would do it in the sheet module for that worksheet (right click on the sheet tab, select view code). In the left dropdown at the top, select the listbox and in the right Keydown in in a userform, then use the userform module. Same of the left and right dropdowns at the top of the module. -- Regards, Tom Ogilvy "TK" wrote in message ... Tom: I tried "Your" KeyDown in various locations in "My" Sub without sucess. I couldn't trap the arrow keys within the sub, any suggestions? Tom Ogilvy" wrote: Assuming listbox from the control toolbox toolbar: When I select an item in a listbox on a worksheet the focus is on the item selected. I can use the arrow keys to move down or up through the list. Anyway, this killed that capability Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 38 Or KeyCode = 40 Then KeyCode = 0 End If End Sub -- Regards, Tom Ogilvy "TK" wrote in message ... Correction: I just checked out the sheet ListBox and focus stays on the sheet. So I quess it is completely mouse driven. So, how do I code the keydown event to cancel the arrow keys Or kept focus on the active cell in the sheet Thanks "TK" wrote: Hi Tom That could be an option if I can't find an alternative but it would make the listBox completely Mouse driven and I would prefer arrow keys to be active.. I worked out the procedure with the ListBox on a sheet and it didn't respond to the arrow keys but in the finish product I wanted the ListBox on a UserForm and then this problem came up. There is quite a bit more to what I'm doing than this sub and you have contributed significantly to my efforts. Thanks for your help. This just happens to be the latest problem. "Tom Ogilvy" wrote: you could use the keydown event to cancel the arrow keys, but that would not allow the user to use them. Is that an option. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: The following sub is used to populate a listBox of course. The problem is that after it is activated and an item is picked if an arrow key is pressed each item is displayed in the ActiveCell as the pointer moves up or down the list. How can I avoid that? Private Sub ListBox1_Click() Dim SourceRange As Range Dim Val1 As String Dim Val2 As String ' RowSource created with Insert - Name - Define ' Range Name is Data so set ListBox1's Properties RowSource = Data Set SourceRange = Range(ListBox1.RowSource) Val1 = ListBox1.Value Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value ActiveCell.Value = Val1 ActiveCell.Offset(0, 3) = Val2 ActiveCell.Offset(1, 0).Activate End Sub Thanks TK |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox (arrow keys pick each item)
Hi TK,
Picking up on Tom's suggestions in this thread, this seems to work for me with a Listbox in a Userform: 'Top of Userform module Private bFlag As Boolean Private Sub ListBox1_KeyDown(ByVal KeyCode _ As MSForms.ReturnInteger, ByVal Shift As Integer) bFlag = False ''enable all keys except up/down arrows If KeyCode = 38 Or KeyCode = 40 Then bFlag = True Else: ListBox1_Click End If ''enable only Enter & Space 'If KeyCode = 13 Or KeyCode = 32 Then 'ListBox1_Click 'Else: bFlag = True 'End If End Sub Private Sub ListBox1_Click() Dim SourceRange As Range Dim Val1 As String Dim Val2 As String ' RowSource created with Insert - Name - Define ' Range Name is Data so set ListBox1's Properties RowSource = Data If bFlag Then bFlag = False Exit Sub End If Set SourceRange = Range(ListBox1.RowSource) Val1 = ListBox1.Value Val2 = SourceRange.Offset(ListBox1.ListIndex, 1). _ Resize(1, 1).Value ActiveCell.Value = Val1 ActiveCell.Offset(0, 3) = Val2 ActiveCell.Offset(1, 0).Activate End Sub Regards, Peter -----Original Message----- Hi: The following sub is used to populate a listBox of course. The problem is that after it is activated and an item is picked if an arrow key is pressed each item is displayed in the ActiveCell as the pointer moves up or down the list. How can I avoid that? Private Sub ListBox1_Click() Dim SourceRange As Range Dim Val1 As String Dim Val2 As String ' RowSource created with Insert - Name - Define ' Range Name is Data so set ListBox1's Properties RowSource = Data Set SourceRange = Range(ListBox1.RowSource) Val1 = ListBox1.Value Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize (1, 1).Value ActiveCell.Value = Val1 ActiveCell.Offset(0, 3) = Val2 ActiveCell.Offset(1, 0).Activate End Sub Thanks TK . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox (arrow keys pick each item)
Thank You: Tom, Peter
Finally the clouds went away. "Peter T" wrote: Hi TK, Picking up on Tom's suggestions in this thread, this seems to work for me with a Listbox in a Userform: 'Top of Userform module Private bFlag As Boolean Private Sub ListBox1_KeyDown(ByVal KeyCode _ As MSForms.ReturnInteger, ByVal Shift As Integer) bFlag = False ''enable all keys except up/down arrows If KeyCode = 38 Or KeyCode = 40 Then bFlag = True Else: ListBox1_Click End If ''enable only Enter & Space 'If KeyCode = 13 Or KeyCode = 32 Then 'ListBox1_Click 'Else: bFlag = True 'End If End Sub Private Sub ListBox1_Click() Dim SourceRange As Range Dim Val1 As String Dim Val2 As String ' RowSource created with Insert - Name - Define ' Range Name is Data so set ListBox1's Properties RowSource = Data If bFlag Then bFlag = False Exit Sub End If Set SourceRange = Range(ListBox1.RowSource) Val1 = ListBox1.Value Val2 = SourceRange.Offset(ListBox1.ListIndex, 1). _ Resize(1, 1).Value ActiveCell.Value = Val1 ActiveCell.Offset(0, 3) = Val2 ActiveCell.Offset(1, 0).Activate End Sub Regards, Peter -----Original Message----- Hi: The following sub is used to populate a listBox of course. The problem is that after it is activated and an item is picked if an arrow key is pressed each item is displayed in the ActiveCell as the pointer moves up or down the list. How can I avoid that? Private Sub ListBox1_Click() Dim SourceRange As Range Dim Val1 As String Dim Val2 As String ' RowSource created with Insert - Name - Define ' Range Name is Data so set ListBox1's Properties RowSource = Data Set SourceRange = Range(ListBox1.RowSource) Val1 = ListBox1.Value Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize (1, 1).Value ActiveCell.Value = Val1 ActiveCell.Offset(0, 3) = Val2 ActiveCell.Offset(1, 0).Activate End Sub Thanks TK . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arrow Keys | Excel Discussion (Misc queries) | |||
Using arrow keys | Excel Discussion (Misc queries) | |||
Arrow Keys | Excel Discussion (Misc queries) | |||
Arrow Keys | Excel Discussion (Misc queries) | |||
arrow keys | Excel Discussion (Misc queries) |