PDA

View Full Version : How do I take an output of a macro and place it into a single cell



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

GTO
03-02-2010, 09:25 PM
Greetings Heath,

Firstly, Welcome to vbaexpress, and a friendly 'Howdy' from Arizona! I'm sure you'll be happy you joined, as there's some great folks here that will go out of their way to be helpful.

In regards to your question, I am glad you were able to solve it so quickly :-) If you'll look under 'Thread Tools' located atop your first post, you'll see an option (displayed only to the originator of the thread) to mark the thread Solved.

Mark

lucas
03-02-2010, 09:32 PM
Hi Heath. Be sure to mark your thread solved as Mark pointed out. Additionally, you can select your code when posting and hit the green vba button to format it for the forum as I have done in your first post.

Is activecell the usage you were looking for or do you have a specific cell you would like to put the return in?

heath
03-03-2010, 01:48 PM
Thanks for the headsup as to formating VBA code in my posts :-)
I did use the activecell command to return my listbox selections to the required cell on the worksheet.
Thanks again for your reply.