userform controls

I have an application that i am working on that has many userforms. at
the bottom of the userform there is one commandbutton, and i want all
the userforms to do the same procedure when that command button is
clicked.

here is the code:
Public Sub pg_finish()
Dim ctl As Control
Dim count As Integer
Dim fname As String

On Error Resume Next
fname = Environ("temp") & "\PG " & ActiveCell.Value & ".xls"
count = 3
ActiveCell.Offset(0, 1).Value = "sold"
For Each ctl In Me.Controls
If TypeName(ctl) = "ComboBox" Then
ActiveCell.Offset(0, count).Value = ctl.Name & ": " &
ctl.Value
count = count + 1
End If
Next ctl
ActiveCell.Offset(0, count).Value = "Total: " & TextBox1.Value
ActiveSheet.Copy
ActiveWorkbook.SaveAs fname
ActiveWorkbook.SendMail "[email protected]", fname
ActiveWorkbook.ChangeFileAccess xlReadOnly
ActiveWorkbook.Close
Kill fname

Application.Workbooks("dk design
macro.xls").Worksheets("sheet1").Activate
count = 0
Unload Me
End Sub

the code was working fine, when it was written in the code window of
the userform, but i tried to move it to a module, so that all the
userforms could refer to it, now the line that reads "For Each ctl In
Me.Controls" isn't working. I understand that me won't work, because
the code is not in the form's code window. how can i make that
reference more generic, so that it will work here.

thanks for any help.

Hello Natanz,

You need to pass the UserForm's object reference to the module. I have made the changes to code so you can copy and paste it.

Example Calling the Routine:
Call pg_finish (UserForm1)

Substitute the name of the userform for UserForm1 in the example. Do not put the user form's name in quotes.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

natanz,

Add an argument to the sub...
Public Sub pg_finish(ByRef objForm as Object)

When you call the sub supply the form as the argument...
Call pg_finish(Me)

Then use the objForm variable to replace all references to "me"
in the Public Sub code.

Jim Cone
San Francisco, USA


"natanz" <[email protected]>
wrote in message
news:[email protected]
I have an application that i am working on that has many userforms. at
the bottom of the userform there is one commandbutton, and i want all
the userforms to do the same procedure when that command button is
clicked.

here is the code:
Public Sub pg_finish()
Dim ctl As Control
Dim count As Integer
Dim fname As String

On Error Resume Next
fname = Environ("temp") & "\PG " & ActiveCell.Value & ".xls"
count = 3
ActiveCell.Offset(0, 1).Value = "sold"
For Each ctl In Me.Controls
If TypeName(ctl) = "ComboBox" Then
ActiveCell.Offset(0, count).Value = ctl.Name & ": " &
ctl.Value
count = count + 1
End If
Next ctl
ActiveCell.Offset(0, count).Value = "Total: " & TextBox1.Value
ActiveSheet.Copy
ActiveWorkbook.SaveAs fname
ActiveWorkbook.SendMail "[email protected]", fname
ActiveWorkbook.ChangeFileAccess xlReadOnly
ActiveWorkbook.Close
Kill fname

Application.Workbooks("dk design
macro.xls").Worksheets("sheet1").Activate
count = 0
Unload Me
End Sub

the code was working fine, when it was written in the code window of
the userform, but i tried to move it to a module, so that all the
userforms could refer to it, now the line that reads "For Each ctl In
Me.Controls" isn't working. I understand that me won't work, because
the code is not in the form's code window. how can i make that
reference more generic, so that it will work here.
thanks for any help.

thanks for all your help. One problem. I am trying to pass the
userform, along with the values input into it by the user. This is
passing the userform, but the values have been lost. Can i do this?

thanks for all your help. One problem. I am trying to pass the
userform, along with the values input into it by the user. This is
passing the userform, but the values have been lost. Can i do this?