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
– Siddharth Rout, Commented Aug 9, 2013 at 11:16
Also please avoid the use of .Select, Activecell etc... See this stackoverflow.com/questions/10714251/… — Siddharth Rout
– Siddharth Rout, Commented Aug 9, 2013 at 11:17

Gary's Student · Accepted Answer · 2013-08-09 11:14:33Z

1

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

answered Aug 9, 2013 at 11:14

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

Collectives™ on Stack Overflow

using input box data from one sub procedure to another

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related