Pass a variable to a user form

[Djblois]

Is it possible to pass a variable to a userform? this is what I tried:

[VBA]Private Sub UserForm_Initialize(ByVal objUserForm As Object)[/VBA]

However, it keeps giving me a compile error "Procedure declartaion does not match description of event or procedure having the same name"

[Bob Phillips]

A form module is just a special tye of class, so use class properties.

In the form

[vba]

Public Property myProp as String
[/vba]

and in the form open, use

[vba]

With Userform1
.myProp = "Testing"
.Show
End With
[/vba]

as an example

[Djblois]

Thank you Xld, I will test it on monday. I am still a complete beginner when it comes to classes. I need to learn how to work with them better but I haven't found a book that deals with VBA classes more than one small chapter. Does anybody have any recommendation?

Thank you,
Daniel

[Bob Phillips]

You don't have to worry about classes here. Although a form is a class, it is an implicit one, and you use it without worrying about classes.

[Djblois]

xld,

Whet I typed this in the form:

[vba]public property objFirstUserForm as Object[/vba]

I get this compile error:

Expected: Get or Let or Set

I tried these:

[VBA]Public Property Get objFirstUserForm() as Object
Set objThisForm = objfirstuserfrom 'It Crashes on this line
End Property[/VBA]

[VBA]Public Property Set objFirstUserForm() as Object 'This Line Gets a Compile Error
Set objThisForm = objfirstuserfrom 'It Crashes on this line
End Property[/VBA]

The Compile Error is "Argument Required for Property Let or Property Set"

Daniel

[jwise]

Let's assume the UserForm is named "frmUser1", and the field you want to change (from its default value is "txtField1".

[vba] ...
frmUser1.txtField1.Value = 100
frmUser1.Show
... [/vba]

I think this does what you want. I also think the error message is telling you that the event code you are trying to use expects only certain parameters as being passed to it, and you can not change this without doing some additional work.

[Bob Phillips]

Drop the property (that was my error) unless you want it to be read or write only, or you want to take specific read/write actions.

[Djblois]

I tried this:

[vba]Public objFirstUserForm() As Object[/vba]

and I get this Compile Error:

"Constants, Fixed-Length Strings, Arrays, User-Defined Types, and declare statements are not allowed as public members of object modules"

Here is exactly what I am trying to do:

1) I call a procedure that I use to name all my Sales Reports:

[vba]Sub StartSalesReport(ByVal lngPivotData As Long, Optional ByVal objUserForm As Object, _
Optional ByVal stgReportName As String)
CreateTab:

If Not objUserForm Is Nothing Then
objUserForm.Show
'If firstUserForm Is Nothing Then - This is a Public Variable I am trying to get rid of
' Set firstUserForm = objUserForm
'End If
End If

If stgReportName = "" Then
stgMyInput = objUserForm.Reportname
'Test if user left name blank
If (stgMyInput) = "" Then
If Not objUserForm Is Nothing Then
objUserForm.Hide
DoEvents
End If
With frmError00_00_03 'This is where I need the Property because objUserForm is different based on
.objFirstUserForm = objUserForm 'Where I call it from and after frmError00_00_03 shows and a user
.Show 'Clicks a button then objUserform needs to show again
End With
Exit Sub
End If
Else
stgMyInput = stgReportName
End If

'Test if another sheet with the same name exists
If SheetExists(stgMyInput) Then
If Not objUserForm Is Nothing Then
objUserForm.Hide
'firstUserForm.Hide
DoEvents
End If
frmError00_00_02.Show ' I need the property here also
Exit Sub
End If
'Test if user selected at least one data value
If Not objUserForm Is Nothing Then
If i = 0 Then
MsgBox "You need to select at least one value to view!"
GoTo CreateTab
End If
End If

TurnOffFeatures
Set wsWorking = Worksheets.Add(, wsData, 1)
wsWorking.Name = stgMyInput
ptBegin lngPivotData
Exit Sub

errorhandler:
Error00_00_00code

End Sub[/vba]

Here is the code under the form:

[vba]Public objFirstUserForm() As Object
Private Sub CommandButton1_Click()
Unload Me
End
End Sub
Private Sub CommandButton2_Click()
Me.Hide
DoEvents
emBugReport " - Error 00-00-03"
End
End Sub
Private Sub CommandButton3_Click()
Unload Me
DoEvents
StartSalesReport finalrow(wsData) - 1, objFirstUserForm 'Here it recalls that procedure telling it
End Sub' which form to recall
Private Sub UserForm_Click()
End Sub[/vba]

[rory]

Public objFirstUserForm As Object
rather than:
Public objFirstUserForm() As Object

should work.

[rory]

PS You will also need:
[VBA]
Set .objFirstUserForm = objUserForm
[/VBA]
rather than:
[VBA].objFirstUserForm = objUserForm [/VBA]

I'm not sure this is the most efficient way to do what you are after, but I am having trouble following the structure!

[Bob Phillips]

You seem to be setting object variables all over the pace, but I don't see where you actually initialise them with a real object.

[Djblois]

This is what I have now simplified and with more comments:

[VBA]Sub StartSalesReport(ByVal lngPivotData As Long, Optional ByVal objUserForm As Object, _
Optional ByVal stgReportName As String)
'Testing Version - Trying to simplify sub
'***If objUserForm is used then stgReportName needs to be blank***
'***If stgReportName is used then objUserForm needs to be = Nothing***
'***Both stgReportName = "" and objUserForm is nothing can't happen at the same time***

CreateTab:


If Not objUserForm Is Nothing Then 'stgReportName = "" and not objUserForm is Nothing
objUserForm.Show
stgMyInput = objUserForm.ReportName
If (stgMyInput) = "" Then 'Test if user left name blank
objUserForm.Hide
DoEvents
With frmError00_00_03 'Show error form and restart sub if user clicks Name Report
Set .objFirstUserForm = objUserForm
.Show
End With
Exit Sub
End If
If i = 0 Then 'Test if user selected at least one data value
MsgBox "You need to select at least one value to view!"
GoTo CreateTab 'If i = 0 then restart the sub
End If
Else 'objUserForm is Nothing and Not stgReportName = ""
stgMyInput = stgReportName
End If

'Test if another sheet with the same name exists
If SheetExists(stgMyInput) Then
If Not objUserForm Is Nothing Then
objUserForm.Hide
DoEvents
End If
frmError00_00_02.Show 'Show error form and restart sub if user clicks Rename Report
Exit Sub
End If

TurnOffFeatures 'Turns off screen updating and sets formulas to manual
Set wsWorking = Worksheets.Add(, wsData, 1) 'Creates Worksheet for Pivot Table
wsWorking.Name = stgMyInput 'Names Pivot Table tab from Variable stgMyInput
ptBegin lngPivotData 'Creates PivotTable with Data from

End Sub[/VBA]

and the form:

[VBA]Public objFirstUserForm As Object
Private Sub cmdEmailDeveloper_Click()
'If User Clicks this button it will email developer about the error recieved
Me.Hide
DoEvents
emBugReport " - Error 00-00-03"
End
End Sub
Private Sub cmdFormCancel_Click()

Unload Me
End

End Sub
Private Sub cmdNameReport_Click()
'If User Clicks this button then it will rerun the StartSalesReport sub
Unload Me
DoEvents
StartSalesReport finalrow(wsData) - 1, objFirstUserForm
End Sub
[/VBA]

for some reason after the frmError00_00_03 shows and I click on the cmdNameReport button and StartSalesReport restarts objFirstUserForm = Nothing

[Djblois]

I also realized that all this is a public varialbe, which I have right now and I am trying to get rid of it:

[vba]Public objFirstUserForm As Object [/vba]

I am in the process of trying to get ride of as many Public Variables as I can because I read you should keep them to a minimum. I used to use Public Variables for everything. I started with close to 50 public Variables but now I am down to about 20. I want to get it down to about 5.

[Djblois]

Here are all my Public Variables with the ways that I think I might be able to get rid of them, so everyone know why I am doing this:

[VBA]Public wbRef As Workbook, wbWorking As Workbook
Public wsData As Worksheet, wsWorking As Worksheet
Public i As Long, j As Long
Public PT As PivotTable
Public stgDate As String
'Haven't figured it out yet
Public Total1 As String, Total2 As String, Total3 As String, Total4 As String, Total5 As String
Public blnProdLast As Boolean
'Create Error Class
Public stgErrorNum As String, FileNumber As Long
'Create Reference Class
Public blnIsOpen As Boolean
'Pass Variable to a Form
Public aSummary As Variant, aSummary2 As Variant, currentYear As Variant
Public firstuserform As Object, stgMyInput As String[/VBA]

[Bob Phillips]

What ways?

What are you goig to do without those variables?

[Djblois]

xld,

I haven't figured that out completely yet. I just put that on here to explain my future plans. Right now I am concerned with this thread - passing a variable to a user form. If I can figure this with all your help then I can get rid of about 3 public variables.

Thank you,
Daniel

[Bob Phillips]

Well you have been shown so what is the problem now?

[Djblois]

well first if you read this:

I also realized that all this is a public varialbe, which I have right now and I am trying to get rid of it:

VBA:
Public objFirstUserForm As Object

VBA tags courtesy of www.thecodenet.com

I am in the process of trying to get ride of as many Public Variables as I can because I read you should keep them to a minimum. I used to use Public Variables for everything. I started with close to 50 public Variables but now I am down to about 20. I want to get it down to about 5

The answer actually looped me back to the way I was originally doing it with a Public Variable. I don't want to use a Public Variable if I don't have to. I sent a cleaned up version of my code also here:

[VBA]

Sub StartSalesReport(ByVal lngPivotData As Long, Optional ByVal objUserForm As Object, _
Optional ByVal stgReportName As String)
'Testing Version - Trying to simplify sub
'***If objUserForm is used then stgReportName needs to be blank***
'***If stgReportName is used then objUserForm needs to be = Nothing***
'***Both stgReportName = "" and objUserForm is nothing can't happen at the same time***

CreateTab:


If Not objUserForm Is Nothing Then 'stgReportName = "" and not objUserForm is Nothing
objUserForm.Show
stgMyInput = objUserForm.ReportName
If (stgMyInput) = "" Then 'Test if user left name blank
objUserForm.Hide
DoEvents
With frmError00_00_03 'Show error form and restart sub if user clicks Name Report
Set .objFirstUserForm = objUserForm
.Show
End With
Exit Sub
End If
If i = 0 Then 'Test if user selected at least one data value
MsgBox "You need to select at least one value to view!"
Goto CreateTab 'If i = 0 then restart the sub
End If
Else 'objUserForm is Nothing and Not stgReportName = ""
stgMyInput = stgReportName
End If

'Test if another sheet with the same name exists
If SheetExists(stgMyInput) Then
If Not objUserForm Is Nothing Then
objUserForm.Hide
DoEvents
End If
frmError00_00_02.Show 'Show error form and restart sub if user clicks Rename Report
Exit Sub
End If

TurnOffFeatures 'Turns off screen updating and sets formulas to manual
Set wsWorking = Worksheets.Add(, wsData, 1) 'Creates Worksheet for Pivot Table
wsWorking.Name = stgMyInput 'Names Pivot Table tab from Variable stgMyInput
ptBegin lngPivotData 'Creates PivotTable with Data from

End Sub

[/VBA]

and the form:

[VBA]Public objFirstUserForm As Object 'This is what I am trying to get rid of
Private Sub cmdEmailDeveloper_Click()
'If User Clicks this button it will email developer about the error recieved
Me.Hide
DoEvents
emBugReport " - Error 00-00-03"
End
End Sub
Private Sub cmdFormCancel_Click()

Unload Me
End

End Sub
Private Sub cmdNameReport_Click()
'If User Clicks this button then it will rerun the StartSalesReport sub
Unload Me
DoEvents
StartSalesReport finalrow(wsData) - 1, objFirstUserForm
End Sub[/VBA]

anyway doing it this way, I set up a watch and for some reason after the frmError00_00_03 shows and I click on the cmdNameReport button and StartSalesReport restarts objFirstUserForm = Nothing

[mikerickson]

If it does the job, why get rid of the Public variable?
"Public variables are bad, don't use them!" is a gross oversimplification.
Reading the posts, it looks like a Public variable is a much easier way to pass a variable to a userform than writting properties, especially to the person who inherits this spreadsheet from you.

[Djblois]

Because I have read, including on this website, to try and keep your scope as small as possible. Also, since I have been reducing the scope of variables, whenever possible, it has made it easier to debug. I have close to 25 public variables, which I am trying to reduce to between 5 and 10. If it isn't possible to remove this one then I will have to work on others.

Daniel