Pass form variable to module in Excel VBA

[bmquiroz]

Hi all,

I have a form that uses option buttons to store parameters. I need to access these parameters as variables from a module then pass them to a procedure. Problem is that I lose the variable when UserForm1 gets unloaded. I have declared the variable as Public but nothing. Can someone shed some light?

Thanks.

-B

Heres an example:

'UserForm1

Option Explicit
Public myOption As String 'Need to pass this to Module1

Private Sub cmdSubmit_Click()
    'MsgBox myOption
    Unload Me
End Sub

Private Sub optButton1_Click()
    myOption = "1"
End Sub

Private Sub optButton2_Click()
    myOption = "2"
End Sub

'Module1

Option Explicit
Public myOption As String

Sub Option_Test()

MsgBox myOption 'No variable, MsgBox is blank

End Sub

 

[PHV]

'Need to pass this to Module1
so, define it as Public in Module1 instead.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fumei]

Module 1

Option Explicit

Public myOption As String


Sub test()
MsgBox myOption
End Sub

Userform - nothing on it but a commandbutton. It sets the variable and unloads.

Private Sub CommandButton1_Click()
myOption = "hello world"
Unload Me
End Sub

The userform must be run FIRST. It sets the variable value. Then the Sub test() in Module 1 will return "hello world".

Look up Scope in Help.

Gerry
My paintings and sculpture

 

[PHV]

OOps, sorry for the typo:
so, define it as Public in Module1 only.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fumei]

yes.......the word "only" was rather important, n'est pas?

I didn't want to really say anything....

<grin>

Gerry
My paintings and sculpture

 

[bmquiroz]

I'm getting a compile error "Ambiguous name: myOption" in UserForm1.

Anyway, what I've done as a workaround is written the variable to a cell in the worksheet under cmdSubmit_Click then retrieving it by checking the cells value in my module procedure. Messy but it works.

 

[PHV]

"Ambiguous name: myOption"
myOption should be defined only ONCE, in Module1.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fumei]

As PH mentions, ambiguous name usually means a name has been used twice. In your code you had myOption declared in both the userform, and the module.

It does not have to be messy. There is a correct way to do it.

Again, look up Scope in Help.

Gerry
My paintings and sculpture