PDA

View Full Version : Sleeper: Userform transfer variables?



russkie
08-31-2005, 09:25 AM
hey ppl,
this seems like a silly qestion to me but i cant find out what the problem is....
i open up a userform through a macro. when all the information is entered and submit is hit, i want all the info in the text boxes to be transfered to variables that the rest of the macro can use and manipulate...
how do ya do that?

i have this in the code of the submit button, it doesnt work:


If (txtstate = "") Then
MsgBox "You didn't enter a state, Try Again."
Exit Sub
End If
state = txtstate.Text


the if statement is cool, but the variable transfer isn't.
thnx fer any help.

Zack Barresse
08-31-2005, 09:54 AM
Hi there,

Where exactly are you trying to transfer this variable to? Can you zip/upload a sample file?

russkie
08-31-2005, 10:04 AM
eh, its a bit of a pain to get to send only part of the file becuase it contains alot of bussines "passwords" so im gonna have to start deleting stuff. heres the jist of the code:

the form has lets say one text field with the name: "shmuck"
and the form name is: "bfform"
i call the useform through a macro:


'Check if the bookfair already has a School/org name and info
Worksheets("Sheet1").Select
yesno = Range("M2").Value
'if yesno = 1 then the information is already in
If (yesno = 0) Then
'Load Form for bookFair Information
Load bfform
bfform.Show


Now... the text field has been filled out. i want to call it with the current macro, and, for example:


MsgBox (shmuck)

if i were to do that right after the form is closed/hidden, it would return an empty msgbox becuase that variable doesnt have any information...
am i making sense?: pray2:

BlueCactus
08-31-2005, 10:11 AM
Your current problem is that the textbox does not exist once you unload the form.

The easiest way to code it (there are several other possibilities) would be to declare a Public variable in the parent module, and set that variable to the textbox contents before unloading the form.

Zack Barresse
08-31-2005, 10:20 AM
Or call the routine before the form is unloaded, passing the variable through to the next routine. BUT, I agree with BC here, use a Public variable.

ALe
08-31-2005, 10:21 AM
BlueCactus,

I read what you wrote: "there are several possibilities".

A part of declaring the variable as public or writing its value in an object (range of a sheet, another textbox,etc.), can you tell me which are the other possibilities, please?

Thank you very much

russkie
08-31-2005, 10:23 AM
ahhh, didnt think that it would clear the variables if the form was unloaded... very cool thanks!

ALe
08-31-2005, 10:24 AM
firefytr, other methods?

Zack Barresse
08-31-2005, 10:27 AM
Yes, using a blank worksheet and storing variables in this location is a definite possibility. Especially when using add-ins. The sheets are not visible, but you can still have as many sheets as you want in them, and they can store data as any other worksheet and be queried in the same fashion (although only via code).

There are public variables, static variables, passing through routines, storing in the windows registry, etc. Many ways to skin the cat here. :)

russkie
08-31-2005, 10:30 AM
ehm, nope, doesnt work.

i declared "shmuck" as public string.
in the code of the submit button on the form i have:


shmuck = txtshmuck.Text

and it gives me an error.
or do i need to call "txtshmuck" as public variable and use THAT in the macro?

ALe
08-31-2005, 10:31 AM
:cool: Thank you very much!

BlueCactus
08-31-2005, 10:33 AM
BlueCactus,

I read what you wrote: "there are several possibilities".

A part of declaring the variable as public or writing its value in an object (range of a sheet, another textbox,etc.), can you tell me which are the other possibilities, please?

Thank you very much
Other possibilities are (trying to be brief):
i) like you mention, writing it out to a sheet
ii) writing in a non-Private Sub into the userform code which you call after Load Userform and before Userform.Show. You call that to pass a variable to the form: e.g., Call MyForm.PassingVars(myvar1, myvar2) etc... Then at some point you set these variables to the control values, and finally....
ii-a) If the variable were passed ByRef (default method), their parent variables in the calling module are already set to what you want, or
ii-b) before unloading the form, call a Sub in the parent module with the variable values
iii) Don't pass any variables to the userform. Just pass the control values back to the parent module with a Call ParentModule.PassingVars(TextBox1.Text, Listbox1.ListIndex) etc before unloading the form.

BlueCactus
08-31-2005, 10:38 AM
ehm, nope, doesnt work.

i declared "shmuck" as public string.
in the code of the submit button on the form i have:


shmuck = txtshmuck.Text

and it gives me an error.
or do i need to call "txtshmuck" as public variable and use THAT in the macro?
You want something like:

Public schmuck as String
Sub testSchmuck()
schmuckForm.Show
MsgBox schmuck
End Sub
Exit code in UserForm:
schmuck = txtschmuck.Text
Unload Me

You could also set schmuck in txtschmuck_Click() or txtschmuck_Change() rather than at the end.

russkie
08-31-2005, 01:43 PM
... muchos graciuos amingo compadre`!... no really, thanks :)

Zack Barresse
08-31-2005, 02:35 PM
The Public variables need to be declared in a Standard Module, btw.

geekgirlau
08-31-2005, 05:25 PM
Rather than using public variables, why not just hide the form rather than unload it? All values in the form remain available to you, and you leave the unload until the process is completed.



MsgBox bfform.shmuck

mdmackillop
08-31-2005, 05:33 PM
Hi Zack,
I've never saved information in xla files. Is the data stored available to any workbook which incorporates that xla?
Regards
Malcolm

Zack Barresse
09-01-2005, 09:11 AM
MD: Yes. As long as that xla file is installed in the current session, it can be accessed anywhere via VBA. The only exception is if the xla file is password protected.

mdmackillop
09-01-2005, 12:46 PM
Thanks Zack,
Could be useful.
Regards
Malcolm

Zack Barresse
09-02-2005, 09:47 AM
I find it quite useful, I use it daily. :yes