listing items from listbox

[Gerry]

I have a listbox, the rowsource is a range on a worksheet.
When this listbox is brought up and names picked, I would like to have these
names entered starting on A4, going downward.
The names are always first and last name, maybe I can extract and copy, but
not sure how to do this.

Any ideas?

TIA
Gerry

Private Sub OKButton_Click()
msg = ""
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then msg = msg & ListBox1.List(i) & vbCrLf
Next i
MsgBox "You selected: " & msg & vbCrLf
Unload Me
With Worksheets("PrintJob").Range("A4")
.Value = msg
.WrapText = True
End With
End Sub

 

[icemonkeysteve]

I'm only new to programming in vba but I think I may have the answer to
your query.

You should use the 'offset' function. There is an excellent example
included in Martin Green's User Form Tutorial on exceltips.com
(http://www.exceltip.com/st/Create_User_Forms_in_Microsoft_Excel/629.html).


The relevent part of the code is about halfway down the page relating
to the ok button that copies the data put into the form onto the
spreadsheet, much the same as what you are trying to do. The contexts
and explanations are all there so I strongly recommend you to follow
the link.

Happy vba-ing

Steve Flanagan
icemonkeysteve

 

[keepitcool]

This function will return an array with the selected items.
(works for single and multiselect)

Optionally indicate which column to return. (1=second column)

If you have excel2000 or newer then THIS will produce a msgbox with the
selected items in listbox1:


Private Sub CommandButton1_Click()
MsgBox Join(GetSelected(Me.Controls("listbox1")), vbNewLine)
End Sub


Function GetSelected(mylst As MSForms.ListBox, _
Optional colNum As Integer)

Dim aRes As Variant
Dim i, n As Integer

With mylst
If .ListCount > 0 Then
ReDim aRes(1 To .ListCount)
For i = 0 To .ListCount - 1
If .Selected(i) Then
n = n + 1
aRes(n) = .List(i, colNum)
End If
Next
End If
End With

If n > 0 Then
ReDim Preserve aRes(1 To n)
GetSelected = aRes
End If
End Function



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >