Mr. Peterson,

Thank you very much, especially for the notes in the code. The code now
puts all the columns where I want them to go. I am now working on another
macro to get it to change the values in column D to be the same as the name
of the WS. Then it is going to be interesting trying to get it to update the
WS I am pulling this data from with the new values for column D. I will post
things as I figure things out. Again thank you.

"Dave Peterson" wrote:

I made a couple of changes to the _initialize procedure, too.


Option Explicit
Private Sub CommandButtonRI_Click()

Dim DestCell As Range
Dim iCtr As Long
Dim oCol As Long

With Worksheets("Brad")
Set DestCell = .Range("A9")
End With

With Me.ListBox1
'clear out all the columns--not just the first.
DestCell.Resize(.ListCount, .ColumnCount).ClearContents
'loop through the columns
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
For oCol = 0 To .ColumnCount - 1
DestCell.Offset(0, oCol).Value _
= .List(iCtr, oCol)
Next oCol
'get ready for the next one.
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
' Identifying elements
Dim ListBoxRange As Range
Dim LastRow As Long
' Identifying which sheet to pull info from
With Worksheets("Complete Listing")
'Telling it to count to the last used cell in column "F"
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
'Telling it to set the range from A4 to F(LastRow)
Set ListBoxRange = .Range("A4:F" & LastRow)
End With

'Operations on the item checkout listbox
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
'Clearing data from the list
.Clear
'counting the number of columns
.ColumnCount = ListBoxRange.Columns.Count
'telling it to use the range set above for the listbox
.List = ListBoxRange.Value
End With
End Sub


AUCP03 wrote:

This is the code I started with trying to get it to do what I want. Now all
it will do is take the first col of the selected items and put it in the WS
"Brad".

Private Sub CommandButtonRI_Click()

Dim DestCell As Range
Dim iCtr As Long

With Worksheets("Brad")
Set DestCell = .Range("A9")
End With

With Me.ListBox1
DestCell.Resize(.ListCount, 1).ClearContents

For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With
End Sub
"Chip Pearson" wrote:

I don't completely follow what you are trying to do, but to identify
the selected rows in the list box, use the Selected property. E.g,

Dim RowNum As Long
RowNum = 2
If Me.ListBox1.Selected(RowNum) = True Then
Debug.Print "Row " & CStr(RowNum) & " is selected."
Else
Debug.Print "Row " & CStr(RowNum) & " is not selected."
End If

Since the List property of a ListBox is 0 indexed, RowNum ranges from
0 to ListCount - 1. You can test all rows in a loop like:

Dim RowNum As Long
With Me.ListBox1
For RowNum = 0 To .ListCount - 1
If .Selected(RowNum) = True Then
Debug.Print CStr(RowNum), "selected"
Else
Debug.Print CStr(RowNum), "not selected"
End If
Next RowNum
End With

To get the individual items in a listbox with more than one column,
use code like

Dim RowNum As Long
Dim ColNum As Long
Dim S As String
With Me.ListBox1
For RowNum = 0 To .ListCount - 1
If .Selected(RowNum) = True Then
For ColNum = 0 To .ColumnCount - 1
S = .List(RowNum, ColNum)
Debug.Print "Row: " & CStr(RowNum) & _
" Column: " & CStr(ColNum) & _
" Value: " & S
Next ColNum
End If
Next RowNum
End With

Beyond that, I'm not sure what your are trying to accomplish.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)







On Mon, 31 Aug 2009 07:11:01 -0700, AUCP03
wrote:

I have a userform with a MultiSelect listbox. How do I write a code to use
the item(s) selected by clicking a CommandButton on the same userform?

What I am trying to make happen is for the selcted items replace the entry
in the D column of the origin WS("Complete Listing") with the name of the WS
from which the UserFrom is activated. Then it would copy the rows A-F and
paste them in the WS it was activated from starting in row 9.

Here is what the code looks like now.

Private Sub UserForm_Initialize()
' Identifying elements
Dim ListBoxRange As Range
Dim LastRow As Long
' Identifying which sheet to pull info from
With Worksheets("Complete Listing")
'Telling it to count to the last used cell in column "F"
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
'Telling it to set the range from A4 to F(LastRow)
Set ListBoxRange = .Range("A4:F" & LastRow)
End With

'Operations on the item checkout listbox
With Me.ListBox1
'Clearing data from the list
.Clear
'counting the number of columns
.ColumnCount = ListBoxRange.Columns.Count
'telling it to use the range set above for the listbox
.List = ListBoxRange.Value
End With
End Sub



--

Dave Peterson