UserForm Listbox Selected Item Manipulation

[AUCP03]

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

 

[Chip Pearson]

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)

 

[AUCP03]

Mr. Pearson,

Thank you for the response. I am trying to adapt what you have instructed
me with to fit my needs.

A better explanation as to what I am trying to do is this: This is an
inventory system. I have a series of WS. The first one "Complete Listing"
lists all my equipment in col A-F.
The other WS are named after employee's, ie "Brad." There is a cmdbtn that
they can select to pull up the listbox from there WS. This list box shows
all the items in "Complete Listing." When they select however many items
they need they then hit another cmdbtn on this userform. What I want this
cmdbtn to do is for all the items selected go back to WS "Complete Listing"
and rewrite the value in col D as the name of the employee(the WS name).
Then I want it to take the associated rows that were just changed and copy
them into the WS for that employee into Col A-F starting in row 9.

 

[AUCP03]

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

 

[Dave Peterson]

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]

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.