ListBox repeat selection fails

[Billy Liddel]

Hello

The user wants an easy way to select names. He is unhappy with Data
Validation as it is too slow. I added a list box to the Sheet which when
clicked will add the selected name to the active cell.

However, on testing I've noticed that if he selects Joe Bloggs and then
moves to the the next cell and selects Joe for the next column or row then
nothing happens. I tried adding EnableEvents = True to no effect. You can
select another name successfuly and then pick the name again but it seems a
bit stupid to do this. Can anyone help? the code is:

Private Sub obNamesListBox_Click()
EnableEvents = True
Addr = ActiveCell.Address
With Sheets("Duty Roster")
Set nRng = .Range("Names")
End With
VRange = Sheets("Week View").Range("InputRange").Address
Set isect = Application.Intersect(Range(Addr), Range(VRange))
'On Error Resume Next
If isect Is Nothing Then
'do nothing
Else
'Linked Cell
ActiveCell = Cells(2, 13)
With ActiveCell.Font
.Name = "Times New Roman"
.Size = 10
End With
End If
ActiveCell.Activate
Range("M2").ClearContents
EnableEvents = True
End Sub

M2 is the linked cell from which the activecell takes its value.

Hope someone can help

Peter

 

[Dave Peterson]

This worked ok for me:

Option Explicit
Private Sub obNamesListBox_Click()

If Intersect(ActiveCell, Me.Range("inputRange")) Is Nothing Then
Exit Sub
End If

With ActiveCell
.Value = Me.obNamesListBox.Value
With .Font
.Name = "Times New Roman"
.Size = 10
End With
End With

'come down a row for testing
ActiveCell.Offset(1, 0).Activate

End Sub
Private Sub obNamesListBox_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Me.obNamesListBox.ListIndex = -1
End Sub

You may want to use _MouseUp instead of the _MouseDown event.

Private Sub obNamesListBox_MouseUp(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Me.obNamesListBox.ListIndex = -1
End Sub

The difference is how the selected item is shaded. You want the current
selection to be highlighted, then use the _MouseDown. You want the current
selection to be "normal", then use the _MouseUp event.

Ps. I dropped the linked cell stuff and just assigned the value to the
activecell.

And EnableEvents isn't really doing anything for you in your code. First,
you'll want to use application.enableevents to toggle that setting.

But clicking on the listbox doesn't get monitored by the application events.