ListBox (arrow keys pick each item)

[Guest]

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

 

[Tom Ogilvy]

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"

 

[Tom Ogilvy]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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]

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.

 

[Peter T]

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

 

[Guest]

Thank You: Tom, Peter

Finally the clouds went away.