Macrol to select mutiple items from a list box and paste selected items in horizontally consecutive cells

[Tanner_2004]

I am using 2010 and want to select nultiple states (locations) from a list box (would that have to be an Active X control?), then paste the selections along a row in adjacent consecutive cells.

For example, my list box would present the user with 51 states. The user would select muluple states by holding down the control key and left clicking, the selected states would change color (click again to unselect), then each selected state would be pasted started in J10, H10, I10 etc.


Any ideas?

Thank you for reading!

Best,

Tony

 

[Case_Germany]

Hi,

maybe that leads you in the right direction:

here is a sample file.

 

[Tanner_2004]

Wow...get me started. You did it. Thank you! Exactly what I needed!

I am a huge dog fan. I own two golden retrievers and we foster some to help people with speacial needs. I will send you my LinkedIn emal address is you wou want to connect. Thank you very much!

Very handsome dog. My good friend breeds the real German shepards from Germany (not the American versions). I never knew their were two versions.

Tony

Hi,

maybe that leads you in the right direction:

here is a sample file.

 

[Tanner_2004]

I just sent you a PM. but I tjought I would post my additional question for others to benefit.

I just want to clear G27 to BD27 - not the entire row 27.

How woudl you change this please?
Private Sub CommandButton1_Click()
Dim intCount As Integer
Dim lngTMP As Long
On Error GoTo Fin
' This deposits the value in column G or the 7the column
lngTMP = 7
'This deposits the first value in row 27
Me.Rows(27).ClearContents
With Me.ListBox1
For intCount = 0 To .ListCount - 1
If .Selected(intCount) = True Then
Me.Cells(27, lngTMP).Value = .List(intCount)
lngTMP = lngTMP + 1
End If
Next intCount
End With
Fin:
If Err.Number <> 0 Then MsgBox "Error: " & _
Err.Number & " " & Err.Description
End Sub

Hi,

maybe that leads you in the right direction:

here is a sample file.

 

[Case_Germany]

Hi,

just write...

Me.Range("G27:BD27").ClearContents

Or - the better way - defining a name like "MyRange" and then write:

Me.Range("MyRange").ClearContents

 

[Tanner_2004]

Thank you. I was missing the "Range" term. I can't tell you how many books (just bought another one last night) and DVDs I own and I still can't get my arms around this. Thank you again!

Hi,

just write...

Me.Range("G27:BD27").ClearContents

Or - the better way - defining a name like "MyRange" and then write:

Me.Range("MyRange").ClearContents

 

[Tanner_2004]

Is it easy to write a macro that will allow the user to clear all of the locations that he/she selected, in addition to the option of deselecting the locations by clicking on the selectiosn again? Thank you.

 

[Case_Germany]

Hi,

here are two ways to remove the marking of the entries in the ListBox. Once a mouse double click. In the second example, you have to press 0 on the keyboard - it also does the 0 on the numeric keypad.

Sample 1

Sample 2

 

[Case_Germany]

Hi,

and the third example with CommandButton:

Sample 3

 

[Tanner_2004]

You are really quite a master! Thank you.