VBA: Passing a variable between functions for UserForm

Question

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

Where did you define dstr? Also, the line Dim valueUSD, name, ric, pStr, sitchStr As String does only declare sitchStr as a string. All other variables will be of type variant, have a look here. — Storax
– Storax, Commented Nov 5, 2019 at 9:22
To set a variable to public, you can dim it with Public instead of Dim. You can do this outside of the sub it runs in. Public var As String. — Plutian
– Plutian, Commented Nov 5, 2019 at 9:23

GWD · Accepted Answer · 2019-11-05 09:33:57Z

1

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

edited Nov 5, 2019 at 9:33

answered Nov 5, 2019 at 9:27

GWD

4,1481 gold badge27 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Warmaker.be · Accepted Answer · 2019-11-05 09:38:08Z

0

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

edited Nov 5, 2019 at 9:38

answered Nov 5, 2019 at 9:31

Warmaker.be

113 bronze badges

Collectives™ on Stack Overflow

VBA: Passing a variable between functions for UserForm

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related