View Full Version : Solved: Excel Userform textbox's

09-21-2009, 06:24 AM

I have a user form with two text fields that I do not want to be entered into a spreadsheet, but I want what is entered from the text boxes to be transferred into a variable for vba to use.

I am wondering how to assign the textboxes to variables.

Can I use this

Private sub click_ok(var1 as string, var2 as string)

textbox1.text = Var1
textbox2.text = Var2

End Sub

However this is in the the user form area and I will need to have this transfer to the vba.

Thanks in advance!

Bob Phillips
09-21-2009, 06:31 AM
Just declare the variables as public variables in the module declarations area.

09-21-2009, 06:39 AM
Sorry, I am sure how to do that. I would do that in the main module as a public sub?

Bob Phillips
09-21-2009, 07:02 AM
No, as separate variable declations, but scope Public, b efore any code, after any Option statements.

09-21-2009, 07:29 AM
Ok, how would I reference the public strings to the userform? Sorry i've been searching stuff on this but can't seem to find anything.

Bob Phillips
09-21-2009, 08:00 AM
Just remove the procedure arguments.

So the standard code module would be

Option Explicit

Public var1 As String
Public var2 As String

'... some real procedure code

and that procedure in the userform would be

Private Sub click_ok()

textbox1.text = Var1
textbox2.text = Var2

End Sub

09-21-2009, 08:03 AM
Thanks Xld,

I also found that in the private function I could use

Dim Var1, Var2

Var1 = userform1.textbox1.value
Var2 = userform1.textbox2.value

Thanks for all your help!