Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-20-2019, 04:46 AM
vapulabis vapulabis is offline Minor Problem with List Box Selection Windows 10 Minor Problem with List Box Selection Office 2013
Novice
Minor Problem with List Box Selection
 
Join Date: Apr 2019
Posts: 4
vapulabis is on a distinguished road
Default Minor Problem with List Box Selection

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.
Reply With Quote
  #2  
Old 11-20-2019, 09:25 AM
p45cal p45cal is offline Minor Problem with List Box Selection Windows 10 Minor Problem with List Box Selection Office 2019
Expert
 
Join Date: Apr 2014
Posts: 342
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

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.
Reply With Quote
  #3  
Old 11-20-2019, 10:28 AM
vapulabis vapulabis is offline Minor Problem with List Box Selection Windows 10 Minor Problem with List Box Selection Office 2013
Novice
Minor Problem with List Box Selection
 
Join Date: Apr 2019
Posts: 4
vapulabis is on a distinguished road
Default

works perfectly thank you so much p45cal
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mutliple Selection List Box tbrynard01 Word 0 09-10-2019 12:56 PM
Minor Problem with List Box Selection 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


All times are GMT -7. The time now is 12:30 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft