Write Listbox selection to Multiple cells

  • Thread starter Thread starter eklarsen
  • Start date Start date
E

eklarsen

I have created a list box, I would like the selection the user clicks on
to then be copied to multiple cells.

If i could get it to write to one cell I am sure I can figure out the
rest.

When the user selects the item it is in listbox1.value and
listbox1.text but I have been unsuccesfull so far.

here is the code:

Sub RemoveDuplicates1()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection

On Error Resume Next
For Each Cell In Range("Data")
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0
UserForm1.Show

End Sub

I defined the worksheet and range in the listbox properties.
 
Hello eklarsen,

Here is an example of how to set multiple cells equal to the list bo
value. The example uses a list box named ListBox1. The code in th
click event will transfer the selected list item to 3 cells o
Worksheet "Sheet1". Cells A2, B6, and C8 will equal what was seelcte
in ListBox1. You can change the ListBox and Worksheet names to what yo
are using. Just place the code in the list box's Click event.


Code
-------------------
Private Sub ListBox1_Click()

Dim Data As String

With ListBox1
Data = .List(.ListIndex)
End With

With Worksheets("Sheet1")
.Range("A2").Value = Data
.Range("B4").Value = Data
.Range("C6").Value = Data
End With

End Sub
 
Leith Ross said:
Hello eklarsen,

Here is an example of how to set multiple cells equal to the list bo
value. The example uses a list box named ListBox1. The code in th
click event will transfer the selected list item to 3 cells o
Worksheet "Sheet1". Cells A2, B6, and C8 will equal what was seelcte
in ListBox1. You can change the ListBox and Worksheet names to what yo
are using. Just place the code in the list box's Click event.


Code
-------------------
Private Sub ListBox1_Click()

Dim Data As String

With ListBox1
Data = .List(.ListIndex)
End With

With Worksheets("Sheet1")
.Range("A2").Value = Data
.Range("B4").Value = Data
.Range("C6").Value = Data
End With

End Sub

-------------------

Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=48782
Click to expand...

Is it possible to select different ranges at any given time instead of predefined ranges as above?
 
Back
Top