Create Macro With Several Variables

Question

I have several worksheets with similar code, so I'd like to turn it into a macro. My only problem is that there are several variables. So at certain points the code looks like this:

Dim Msg1 As String
Dim Msg2 As String

Public Sub ListBox1_LostFocus()
ListBox1.Height = 15
With ListBox1
Msg1 = "'"
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            Msg1 = Msg1 & .List(i) & "','"
        End If
    Next i
End With
Msg1 = Left(Msg1, Len(Msg1) - 2)
Sheets("Sheet1").Range("R3", "R3") = Msg1
End Sub

and so on. How can I pass in a new value for Msg1, Msg2, Msg3 for each worksheet?

Answer 1

Create a module and put gathermessage below into that module

public function gathermessage(list as listbox) as string
    'generate your message
    gathermessage = msg
end function

to use:

Sheets("Sheet1").Range("R3", "R3") = gathermessage(listbox1)

I believe it would be a good exercise for you to work out the details.