Sleeper: Userform transfer variables?

[russkie]

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]

Hi there,

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

[russkie]

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?

[BlueCactus]

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]

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]

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]

ahhh, didnt think that it would clear the variables if the form was unloaded... very cool thanks!

[ALe]

firefytr, other methods?

[Zack Barresse]

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]

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]

Thank you very much!

[BlueCactus]

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]

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]

... muchos graciuos amingo compadre`!... no really, thanks

[Zack Barresse]

The Public variables need to be declared in a Standard Module, btw.

[geekgirlau]

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]

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]

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]

Thanks Zack,
Could be useful.
Regards
Malcolm

[Zack Barresse]

I find it quite useful, I use it daily.