Passing a Variable from a Form

[GoBigRed]

Okay, I've searched the other threads and still can't figure this out.
How do you pass a variable from a form in VB? I have about five option buttons (1-5) on a form and if a user selects 1, a string variable is set to lets say "one". (2 is two, 3 is three, etc.)
Once selecting a button, close the form but also pass the variable to worksheet_change and use it. I've tried Global variable, but not sure I'm doing it right.

[lucas]

Let us see what you have....
is the value passed to the sheet too?
what event are you using in worksheet change?
etc.....

[lucas]

How are you setting your global.
Are you dimming it outside of any subs or functions?
etc.

[GoBigRed]

Here is the Work_SheetChange.....etc.
[vba]Public stat As String

Private Sub Worksheet_Change(ByVal Target As Range)
Dim loc

loc = Target.Address
If Target.Column <> 4 Then Exit Sub 'Runs if a change is made in column D (4)
If Target.Cells.Count <> 1 Then Exit Sub 'Runs if only one cell is selected
If Target.Value = "C" Or Target.Value = "c" Then 'If value does not equal C or c then...
Range(loc).Select 'Get location of entered value C or c.
ActiveCell.Offset(rowOffset:=0, columnOffset:=6).Activate 'Move 6 columns to right.
HyperLinkDef 'Run function and return Hyperlink
Target.Offset(0, 3) = Date 'Date is added to column G, 3 cells to right of original column.
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate

ShowForm 'Run function and open form
ActiveCell.Value = stat
End If

End Sub
'This function gets the file from the user, parses it and displays it as a Hyperlink.
Sub HyperLinkDef()
Dim HL As String, TTD As String, t As Integer

ChDir ("C:\Windows") 'Set this as the default directory
HL = Application.GetOpenFilename("Documents (*.doc; *.ima), _
*.doc; *.ima", , "Insert Hyperlink") 'Get Document

t = InStrRev(HL, "\") 'Look for \ in HL and get position
If t > 0 Then TTD = Mid(HL, t + 1) 'If t is greater than zero, set TextToDisplay

If TTD = "" Then Exit Sub 'If no file is selected then exit function.
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:=HL, _
TextToDisplay:=TTD 'Add Hyperlink to active cell.

End Sub
'Function brings up status form and prompts user to select a radio button.
Sub ShowForm()
StatusForm.Show
End Sub
[/vba]
Here is one of the forms..... The global variable here does not work.

[vba]'Public stat As String 'Global variable
'No Exceptions Taken selected
Private Sub OptionButton1_Click()
ActiveCell.Value = "NET"
Me.Hide
End Sub
'Make Corrections Noted selected
Private Sub OptionButton2_Click()
stat = "MCN"
Me.Hide
End Sub
'Revise and Resubmit selected
Private Sub OptionButton3_Click()
stat = "R&R"
Me.Hide
End Sub
'Cost Option selected
Private Sub OptionButton4_Click()
stat = "Cost Op"
Me.Hide
End Sub
'CM Review selected
Private Sub OptionButton5_Click()
stat = "CM Rev"
Me.Hide
End Sub
'Not Applicable selected
Private Sub OptionButton6_Click()
stat = "N/A"
Me.Hide
End Sub
'If no status is selected then give the user a chance to exit status form.
Private Sub SubCancelButton_Click()
Unload Me
End Sub
[/vba]

[GoBigRed]

ActiveCell.Value = "NET" is another way of placing the value at the current cell, but wondered if a global variable could be used..

[lucas]

Only a guess at this point but I don't think so if you use this in the worksheet change:
ActiveCell.Value = stat
You could probably use some case or If else statements instead of all of the subs for the option buttons....are they in a frame so that only one can be selected?

[johnske]

Try putting Public stat As String in a standard module

[GoBigRed]

Try putting Public stat As String in a standard module

Yea, I know that works, but why not in sheet code?

[johnske]

Because a worksheet module is a class module, and public modules in a class module are only 'public' within that module. If you want to make a public module truly global it should go in a standard module

[GoBigRed]

Because a worksheet module is a class module, and public modules in a class module are only 'public' within that module. If you want to make a public module truly global it should go in a standard module

Thanks, John

[Andy Pope]

If you want to access the public variable within a sheet module use

[vba]Sheet1.stat = "MCN"[/vba]

If you are only using stat in the worksheetchange event then when not use a public variable in the userform.

Put the declaration in the userform code and the in the event you can use
[vba]ShowForm 'Run function and open form
ActiveCell.Value = StatusForm.stat[/vba]

[Bob Phillips]

Because a worksheet module is a class module, and public modules in a class module are only 'public' within that module. If you want to make a public module truly global it should go in a standard module

More accurately, Public variables in a class module are read/write properties of that class and can be accessed globally jsut as easily as long as the class name is also used, such as

Sheet1.myVariable

[tstom]

You should create an accessible property in your userform. Basically a quasi dialog.

In your userform, create a property named "Stat" and store it in a private varaiable to be accessed later.
[VBA]
Private pStat As String

Private Sub OptionButton1_Click()
pStat = "NET"
Me.Hide
End Sub

Friend Property Get Stat() As String
Stat = pStat
End Property
[/VBA]

Instead of implicitly creating an instance of StatusForm, explicitly create an instance and maintain a reference to it.
[VBA]
'this code should be changed...
ShowForm 'Run function and open form
ActiveCell.Value = Stat

'... to this instead
Dim sf As New StatusForm
'show the form modally
sf.Show vbModal
ActiveCell.Value = sf.Stat
Unload sf
Set sf = Nothing
[/VBA]

If you do not wish to unload your userform, place the reference variable at the proper scope. Is this form only used for a specific worksheet? Place it in the worksheet class. Workbook or across projects ? Workbook class.