Minor Problem with List Box Selection

vapulabis · #1 11-20-2019, 04:46 AM

Hello

I have a workbook with 2 sheets ( default names), data is stored on Sheet1 in row A ( DOG1 - DOG10), I have created a list box on Sheet2 that pulls in that information and populates the list box. I have the following code

Code:

Private Sub ListBox1_Click()
'Select first empty cell in column C
Cells(Rows.Count, "C").End(xlUp).Offset(1).Select
'Paste in the value from the listbox
ActiveCell.Formula = ListBox1.Value
End Sub

its job is to paste the selected DOG from the listbox into the next row down in Sheet 2 column C, it works fine as long as each DOG selection is different, if I select the same DOG more than once it only inputs the first click and ignores all others until I change to a different DOG, if the same DOG wins twice, I would like it to appear in the list on Sheet2 column C, twice.

p45cal · #2 11-20-2019, 09:25 AM

try:

Code:

Dim Blocked As Boolean 'this line should be outside the macro and at the top of the code-module.

Private Sub ListBox1_Click()
If Blocked Then Exit Sub
With Me.ListBox1
  If .ListIndex > -1 Then
  Blocked = True
    Cells(Rows.Count, "C").End(xlUp).Offset(1).Value = .Value
    .MultiSelect = fmMultiSelectMulti
   .Selected(.ListIndex) = False
    .MultiSelect = fmMultiSelectSingle
  End If
End With
Blocked = False
End Sub

This could be in the _change event instead, but not both.

vapulabis · #3 11-20-2019, 10:28 AM

works perfectly thank you so much p45cal

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mutliple Selection List Box	tbrynard01	Word	0	09-10-2019 12:56 PM
Multiple Selection Dropdown list	ajanson	Word VBA	36	07-15-2019 08:16 PM
Multiple-Selection List in Word 2016	jamboersma	Word	1	05-24-2018 06:44 PM
block selection in dropdown list	Intruder	Excel	2	01-10-2013 10:20 AM
How do you add to a selection list?	bryanarn	Excel	2	03-05-2012 05:04 PM