Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox repeat selection fails
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox repeat selection fails
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. Billy Liddel wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox selection | Excel Discussion (Misc queries) | |||
Listbox selection | Excel Programming | |||
Don't want to repeat a selection | Excel Discussion (Misc queries) | |||
Don't want to repeat a selection | Excel Discussion (Misc queries) | |||
ListBox selection | Excel Programming |