heath
03-02-2010, 04:06 PM
I am Using Microsoft Office Excel 2003 SP3
I used this script from a previous post by Ken Puls 2003: Identify Selected Item in a multi selection Listbox.
'** The following code goes in a userform **
Option Explicit
Private Sub cmdCancel_Click()
'Unload the userform
Unload Me
End Sub
Private Sub cmdOkay_Click()
Dim i As Long, msg As String, Check As String
'Generate a list of the selected items
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .List(i) & vbNewLine
End If
Next i
End With
If msg = vbNullString Then
'If nothing was selected, tell user and let them try again
MsgBox "Nothing was selected! Please make a selection!"
Exit Sub
Else
'Ask the user if they are happy with their selection(s)
Check = MsgBox("You selected:" & vbNewLine & msg & vbNewLine & _
"Are you happy with your selections?", _
vbYesNo + vbInformation, "Please confirm")
End If
If Check = vbYes Then
'Unload the userform since user is happy with selection(s)
Unload Me
Else
'User wants to try again, so clear listbox selections and
'return user to the userform
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = False
Next
End If
End Sub
Private Sub UserForm_Initialize()
Dim cl As Range
'Load the combobox with a variety of household pets
With Me.ListBox1
'Clear the rowsource in case it has been set
.RowSource = ""
'Add the items from Cell A10 to the last used row in column A
For Each cl In Worksheets("Sheet1").Range("A10:A" & _
Worksheets("Sheet1").Range("A65536").End(xlUp).Row)
.AddItem cl.Value
Next cl
End With
End Sub
'** The following code goes in a standard module **
Option Explicit
Sub Launch()
'This code will launch the userform
UserForm1.Show
End Sub
This script is fantastic and works well. My problem is that once I have selected multiple items from my listbox within Userform1 (triggered by the macro "launch") they go nowhere. I would like to output my multiple selections into a single cell within my worksheet.
Any assistance with this would be greatfully recieved.
Kind regards
Heath Gullery
UPDATE - I have just solved this using ActiveCell command
TKS.
Edit: VBA tags added to code
I used this script from a previous post by Ken Puls 2003: Identify Selected Item in a multi selection Listbox.
'** The following code goes in a userform **
Option Explicit
Private Sub cmdCancel_Click()
'Unload the userform
Unload Me
End Sub
Private Sub cmdOkay_Click()
Dim i As Long, msg As String, Check As String
'Generate a list of the selected items
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .List(i) & vbNewLine
End If
Next i
End With
If msg = vbNullString Then
'If nothing was selected, tell user and let them try again
MsgBox "Nothing was selected! Please make a selection!"
Exit Sub
Else
'Ask the user if they are happy with their selection(s)
Check = MsgBox("You selected:" & vbNewLine & msg & vbNewLine & _
"Are you happy with your selections?", _
vbYesNo + vbInformation, "Please confirm")
End If
If Check = vbYes Then
'Unload the userform since user is happy with selection(s)
Unload Me
Else
'User wants to try again, so clear listbox selections and
'return user to the userform
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = False
Next
End If
End Sub
Private Sub UserForm_Initialize()
Dim cl As Range
'Load the combobox with a variety of household pets
With Me.ListBox1
'Clear the rowsource in case it has been set
.RowSource = ""
'Add the items from Cell A10 to the last used row in column A
For Each cl In Worksheets("Sheet1").Range("A10:A" & _
Worksheets("Sheet1").Range("A65536").End(xlUp).Row)
.AddItem cl.Value
Next cl
End With
End Sub
'** The following code goes in a standard module **
Option Explicit
Sub Launch()
'This code will launch the userform
UserForm1.Show
End Sub
This script is fantastic and works well. My problem is that once I have selected multiple items from my listbox within Userform1 (triggered by the macro "launch") they go nowhere. I would like to output my multiple selections into a single cell within my worksheet.
Any assistance with this would be greatfully recieved.
Kind regards
Heath Gullery
UPDATE - I have just solved this using ActiveCell command
TKS.
Edit: VBA tags added to code