userform to override a variable in a module?

[losmi8]

I am wondering if it is possible to have a textbox in a UserForm and upon pressing a Run button, for the UserForm to go to a certain macro (specified in the button Run code) and override a selected variable with the value entered in the userform textbox?

[losmi8]

Example: I have a macro that runs automatically when I press a button on my worksheet, and it uses "10" for a variable called "period".

Can I run this same macro, and override that "10" and set the variable "period" to a value entered in a UserForm textbox?

[Richard Schollar]

Hi

You need to set period as an optional argument to the macro you call eg:

Please Login or Register  to view this content.

Then when you call this routine from the userform, you can insert the value from the textbox:

Please Login or Register  to view this content.

Richard

[losmi8]

Please Login or Register  to view this content.

How does it know we're talking about "Period" variable? I get everything, I just want to make sure how we're passing the "Period" here. I see that we're passing something from Textbox1, but to what? Does it automatically guess it's to "Period" because it is an "Optional" variable?

Thank you very much for your help! This will save me some serious time!

[Richard Schollar]

In my example, MyMacro only takes at most one argument so the VBA compiler knows that any argument to the sub is only ever going to be a value for Period. You do have to pass a value of the required type (in this instance a Long) but the Val() function should ensure this is the case.

[romperstomper]

Val actually returns a Double, so technically you should CLng it...

[Richard Schollar]

Yeah but VBA's internal conversions handle that point as you well know. It's a way of handling if text has been entered too.

[romperstomper]

You do have to pass a value of the required type (in this instance a Long) but the Val() function should ensure this is the case.

I'm just being pedantic (for a change) since it's actually the implicit conversion, not Val, that ensures you get the right type. And as we all know, even if we ignore it most of the time, an explicit conversion is better than an implicit one.

Only a couple of hours until beer now though...

[Richard Schollar]

Only a couple of hours until beer now though...

Deleted and PM sent

[NBVC]

Hope you get better soon, Richard.

Hope the little ones are all doing well

[losmi8]

so how do i pass two variables then?

[Richard Schollar]

You just need to include them in the Sub (or Function) declaration eg:

Please Login or Register  to view this content.

Then call that:

Please Login or Register  to view this content.

Any optional arguments to the MyMacro must be listed at the end of the arguments list.