PDA

View Full Version : Solved: Excel Userform textbox's



rob0923
09-21-2009, 06:24 AM
Hi,

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.

rob0923
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.

rob0923
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

rob0923
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!