PDA

View Full Version : Solved: variables carrying over from userform to Module



bdsii
10-30-2009, 08:40 AM
All - I am using Excel 2007 on a PC and am trying userforms to enter data into a spreadsheet.

I have 4 variables that are specified in a Userform. I am able to write code to do what I want as long as the code is inside the Userform I created.

What I would like to do is to execute a specific Macro placed in a Module so the working parts of this effort would be under Module rather than with the Userform. For the code I want, I can make it more efficient and cleaner by placing it into a Module.

I would have code similar to this below in the Userform to run the Macro I want:


Application.Run "Filename.xlsm'!ModuleName.MacroName"


When I write the code in MacroName, I use info from the user that was completed in the UserForm. The variable names below are the names of the Listbox or Textboxes which are used for the variable names of the data:
shtname which is a worksheet name
BName which came from a Listbox
Calendar1 which came from a Calender on the Userform
Amount which came from a TextBox
Comments which came from a TextBox

I can code this to work correctly as long as it is inside the Userform code but I cannot use these variables inside the Macro. It appears as if the variables are not recognized.

I did not close the Userform but did Unload the userform to remove it from view of the user.

Do I have to Dim the variables in the Macro ?

Any help or suggestions would be appreciated!

thanks!

lucas
10-30-2009, 08:45 AM
I can code this to work correctly as long as it is inside the Userform code but I cannot use these variables inside the Macro. It appears as if the variables are not recognized.


You may want to rethink doing what you are suggesting. I'm not sure why you think it will help to begin with.

To do what you want, you will have to declare all of your variables publicly, in a standard module and you will have to reference every userform, control, etc. specifically.

Just my two cents.

bdsii
10-30-2009, 09:02 AM
thanks for the advice lucas. I am continuing with the project you helped with earlier. The help you provided earlier is really helping.

I thought it may be easier to keep the main part of the code in a Module but it is looking like it is not that easy to do that.

I am still learning all the ins and outs here so advice like this is helpful.

To declare the variables publicly specifying the userforms and controls is that done through the Dim statement or some other method ?

A sample of that might be informative here if one is easily available ?

Thanks!

lucas
10-30-2009, 09:23 AM
On public variables from the help file:

Public Statement Example
This example uses the Public statement at the module level (General section) of a standard module to explicitly declare variables as public; that is, they are available to all procedures in all modules in all applications unless Option Private Module is in effect.
Public Number As Integer ' Public Integer variable.
Public NameArray(1 To 5) As String ' Public array variable.
' Multiple declarations, two Variants and one Integer, all Public.
Public MyVar, YourVar, ThisVar As Integer

to reference a textbox from a standard module you would have to fully qualify it. For example:
Sub a()
UserForm1.Show
UserForm1.TextBox1.Text = "Test"
End Sub

mdmackillop
10-30-2009, 09:41 AM
Something like this
Userform code

Option Explicit
Private Sub CommandButton1_Click()
Number = TextBox1
Call Test(Number)
End Sub


Standard Module Code

Option Explicit
Public Number As Integer

Sub Test(Num As Integer)
MsgBox 3 * Num
End Sub

bdsii
10-30-2009, 11:34 AM
thanks lucas and mdmackillop! I am learning much from both of you and I appreciate it! :-)

mdmackillop
10-30-2009, 04:29 PM
No problem. If you need help with the specifics, let know.

Tinbendr
10-30-2009, 05:19 PM
I did not close the Userform but did Unload the userform to remove it from view of the user.Don't unload it, just Hide it.

For example:
Private CommandButton1_click

userform1.Hide
Application.Run "Filename.xlsm'!ModuleName.MacroName"
Unload Me
End SubThen you have access to the contents of the userform.

bdsii
10-30-2009, 06:50 PM
Thanks Tinbendr - will give that a whirl. :-)

Tantrik
07-08-2010, 12:36 AM
Hey,
I'm kinda new to VBA and was reading these forums to get help with some of my code.

I'm running a code containing 3 standard modules. Till now they were independant, in the sense that no variables needed to be passes from one module to another. Now I have a requirement to pass a few variables between modules.

Declaring variables as public in a standard module allows it to be accessed only in that module and doing the same in the ThisWorkbook module doesn't allow access to it from any standard module .

I was initially trying to use the "friend" variable declaration but was completely lost.

Any help will be greatly appreciated.

paragrc
07-08-2010, 01:15 AM
hey i m also looking for same code
as i hve to enter data in excel
for that i hv made userform and entry will go through userfrom to excel sheet

Bob Phillips
07-08-2010, 04:32 AM
hey i m also looking for same code
as i hve to enter data in excel
for that i hv made userform and entry will go through userfrom to excel sheet

Start your own thread, do not hijack others.