PDA

View Full Version : Pass a variable to a user form



Djblois
10-26-2007, 12:42 PM
Is it possible to pass a variable to a userform? this is what I tried:

Private Sub UserForm_Initialize(ByVal objUserForm As Object)

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

Bob Phillips
10-26-2007, 02:15 PM
A form module is just a special tye of class, so use class properties.

In the form



Public Property myProp as String


and in the form open, use



With Userform1
.myProp = "Testing"
.Show
End With


as an example

Djblois
10-26-2007, 07:52 PM
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
10-27-2007, 02:01 AM
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
10-29-2007, 07:38 AM
xld,

Whet I typed this in the form:

public property objFirstUserForm as Object

I get this compile error:

Expected: Get or Let or Set

I tried these:

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

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

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

Daniel

jwise
10-29-2007, 07:53 AM
Let's assume the UserForm is named "frmUser1", and the field you want to change (from its default value is "txtField1".
...
frmUser1.txtField1.Value = 100
frmUser1.Show
...

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
10-29-2007, 07:56 AM
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
10-29-2007, 08:06 AM
I tried this:

Public objFirstUserForm() As Object

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:

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

Here is the code under the form:

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

rory
10-29-2007, 08:20 AM
Public objFirstUserForm As Object
rather than:
Public objFirstUserForm() As Object

should work.

rory
10-29-2007, 08:23 AM
PS You will also need:

Set .objFirstUserForm = objUserForm

rather than:
.objFirstUserForm = objUserForm

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
10-29-2007, 08:47 AM
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
10-29-2007, 09:22 AM
This is what I have now simplified and with more comments:

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

and the form:

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


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

Djblois
10-31-2007, 08:20 AM
I also realized that all this is a public varialbe, which I have right now and I am trying to get rid of it:

Public objFirstUserForm As Object

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
10-31-2007, 08:31 AM
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:

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

Bob Phillips
10-31-2007, 09:15 AM
What ways?

What are you goig to do without those variables?

Djblois
10-31-2007, 10:44 AM
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
10-31-2007, 12:05 PM
Well you have been shown so what is the problem now?

Djblois
10-31-2007, 12:21 PM
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 (http://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:



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


and the form:

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

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
10-31-2007, 04:04 PM
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
11-01-2007, 06:02 AM
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

Bob Phillips
11-01-2007, 06:12 AM
But Mike is right. All of these things have to be taken into context. It IS a good idea to reduce the number of public variables as a programming practice (but not the be all and end all), but this means doing it in your design, not carving the code up afterwards. And if you don't understand how to do it, why you do it,a nd the pros and cons of doing it, I just wouldn't bother.

You know what they say, if it ain't broke, don't fix it.

Scope is far more relevant in not making module variable public if not necessary, not making procedure variables as module variables if not necessary.

Djblois
11-01-2007, 06:19 AM
xld,

I know that already. That isn't a be all, end all for me. Looking at some of my variables I don't see any way possible to get rid of them because they are used throughout the whole program. This one here I am only using in One place and that is in the error form, to recall the procedure with the same form that ran before the error was produced. If it can't be done I won't sweat it but it seems possible. If I can pass a variable to a module, shouldn't there be a similar ability to pass a variable to a form.

Bob Phillips
11-01-2007, 06:24 AM
Yes you can, and I have showed you how, so I am lost as to what the problem is.

Djblois
11-01-2007, 06:29 AM
You originally told me to do this:

Public Property myProp As String

and when I told you there was an error you told me to remove the Property which gives me this:

Public myProp As String

Isn't that just a public variable again?? The same thing I am trying to get rid of? If I am wrong please tell me but it looks like the same thing I am doing now.

Daniel

Djblois
11-01-2007, 09:07 AM
xld,

Please, I am not trying to argue with you or anyone else. From looking at what you told me to use and my original code there is no difference that I can see? If I am wrong please tell me.