userform controls

[natanz]

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 "(e-mail address removed)", 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.

 

[Jim Cone]

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
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 "(e-mail address removed)", 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.

 

[Leith Ross]

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.


Code:
--------------------
Public Sub pg_finish(ByRef MyForm As UserForm)
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 MyForm.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 "(e-mail address removed)", fname
ActiveWorkbook.ChangeFileAccess xlReadOnly
ActiveWorkbook.Close
Kill fname

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

Unload MyForm

End Sub

 

[natanz]

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?