using input box data from one sub procedure to another

Question

Need a little bit of help with a problem and I hope this all makes sense.

I have a sub procedure that sorts a form and then has an input box display asking for the users name.

I have the following code that does this then adds it to a specific cell on the form :

Dim UserName as string

UserName = Application.InputBox(Prompt:="Please Enter your Name", Title:="Name Of User", Type:=2)

Range("A" & LastRow).Select

ActiveCell.FormulaR1C1 = "Name      " & UserName

This works perfectly but I also have another workbook open with some other data that gets sorted from the sub procedure after this one, which also works fine but what I would like is for the UserName variable to be passed to this other sub procedure so I don't have to have the input box display a second time.

I have tried declaring the variable as public and placing it both inside the sub procedure and outside all sub procedures with no luck. I have tried assigning the UserName variable inside the sub procedure to a public variable UserName2 outside all other sub procedures and then trying to use the UserName2 variable again with no luck.

So any help would be greatly appreciated

Thanks Gareth

Store your workbooks in a variable and then fully qualify the ranges and then directly write to them. For example `wb1.Sheets("Sheet1").Range("A" & LastRow).Value = "Name " & UserName` and `wb2.Sheets("Sheet1").Range("A" & LastRow).Value = "Name " & UserName` If the procedures are in the same workbook then you can declare the public variable in a module. — Siddharth Rout, Aug 09 '13 at 11:16
Also please avoid the use of `.Select`, `Activecell` etc... See this http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select/10718179#10718179 — Siddharth Rout, Aug 09 '13 at 11:17

score 1 · Accepted Answer · answered Aug 09 '13 at 11:14

Place the following in a Standard Module:

Dim UserName As String

Sub MAIN()
    Call FirstSub
    Call SecondSub
End Sub

Sub FirstSub()
    Dim LastRow As Long
    LastRow = 3
    UserName = Application.InputBox(Prompt:="Please Enter your Name", Title:="Name Of User", Type:=2)
    Range("A" & LastRow).Select
    ActiveCell.FormulaR1C1 = "Name      " & UserName
End Sub

Sub SecondSub()
    MsgBox UserName
End Sub

then run MAIN

using input box data from one sub procedure to another

1 Answers1