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