CODEWITHSUNDEEP
excelvbauserform
Nicole Lee
Nicole Lee

Reputation: 37

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

Upvotes: 0

Views: 190

Answers (2)

Warmaker.be
Warmaker.be

Reputation: 11

You can use a private variable in your UserForm project (set your declaration(s) outside the Sub with private instead of dim)

if the variable needs to be used in other projects/functions: public variable or Property

Upvotes: 0

GWD
GWD

Reputation: 3988

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

Upvotes: 1

Related Questions