PDA

View Full Version : Passing a Variable from a Form



GoBigRed
12-09-2006, 08:07 PM
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
12-09-2006, 08:42 PM
Let us see what you have....
is the value passed to the sheet too?
what event are you using in worksheet change?
etc.....

lucas
12-09-2006, 08:43 PM
How are you setting your global.
Are you dimming it outside of any subs or functions?
etc.

GoBigRed
12-09-2006, 08:56 PM
Here is the Work_SheetChange.....etc.
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

Here is one of the forms..... The global variable here does not work.

'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

GoBigRed
12-09-2006, 08:58 PM
ActiveCell.Value = "NET" is another way of placing the value at the current cell, but wondered if a global variable could be used..

lucas
12-09-2006, 09:10 PM
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
12-09-2006, 09:23 PM
Try putting Public stat As String in a standard module :)

GoBigRed
12-09-2006, 10:02 PM
Try putting Public stat As String in a standard module :)
Yea, I know that works, but why not in sheet code?

johnske
12-09-2006, 10:10 PM
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
12-09-2006, 10:59 PM
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 :thumb

Andy Pope
12-10-2006, 02:59 AM
If you want to access the public variable within a sheet module use

Sheet1.stat = "MCN"

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
ShowForm 'Run function and open form
ActiveCell.Value = StatusForm.stat

Bob Phillips
12-10-2006, 04:06 AM
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
12-10-2006, 05:59 AM
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.

Private pStat As String

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

Friend Property Get Stat() As String
Stat = pStat
End Property


Instead of implicitly creating an instance of StatusForm, explicitly create an instance and maintain a reference to it.

'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


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.