Search code examples
excelvbauserform

VBA: Passing a variable between functions for UserForm


I am creating a vba UserForm that after completion of the form, a string variable with the form details will paste into another sheet in the workbook.

I want to paste the value dStr into a new sheet after the use clicks on the addBtn. Would appreciate any help on this. I've read elsewhere that you can set the variable public, but not exactly sure how to do that either.

Sub UserForm_Initialize()

Dim valueUSD, name, ric, pStr, sitchStr As String
Dim i, lRow As Long

i = 2
ric = Worksheets("Tester").Range("H" & i)
name = Worksheets("Tester").Range("B" & i)
valueUSD = Worksheets("Tester").Range("C" & i)
sitchStr = ""
dStr = ""

pStr = ric & "   " & name & "   " & valueUSD & "   "

Label1.Caption = pStr

TextBox2.Value = ""

If activeCheck.Value = True Then
    sitchStr = sitchStr + activeCheck.Caption
ElseIf itwCheck.Value = True Then
    sitchStr = sitchStr + itwCheck.Caption
Else
    sitchStr = ""
End If

dStr = pStr & vbNewLine & sitchStr & ", " & TextBox2.Value

End Sub

Sub addBtn_Click()

Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim myData As DataObject

Dim lastRow As Long


End Sub

Solution

  • Setting the variable public works as Pluatian mentioned in the comment. To insert it into your desired worksheet you can use:

    set pasteSheet = Application.Worksheets("Name of Paste Sheet") '<- input name of Sheet you want to paste it in
    
    pasteSheet.Range("A1").Value = dstr '<- input cell you want to paste it in in Range
    

    Or instead of the Range property

    pasteSheet.Cells(1, 2) = dstr '<- 1 is the line number and 2 the Column Number, this example refers to Cell B1