Form name as variable

Neal · September 25th, 2009, 06:45 AM

Hi,

I have a routine that is used within several forms and I would like to contain it in a separate module which is referenced by each form.to cut down on coding.

Is there a method that I could perform the following 'With' statement (and similar) using a variable?

Code:

Call MySub("MyFormName")

Code:

 
Public Sub MySub(strFormName as String)
Dim nextRow As Integer
nextRow = getGenericRows()
nextRow = nextRow + 1
With strFormName.fmeGeneric
    'MyCode
End With
 
End Sub

strFormName = The name of the form held as a string
fmeGeneric = A frame within the form

Maybe both the form and frame should be passed as the parameter?

Thanks in advance,

JP · September 25th, 2009, 10:26 AM

Pass a copy of your Form object to the procedure.

Code:

 
Public Sub MySub(strFormName as MSForms.UserForm)
Dim nextRow As Integer
nextRow = getGenericRows() + 1
With strFormName.fmeGeneric
    'MyCode
End With
 
End Sub

To call this procedure from the code module behind a form, just use

Call MySub(Me)

You can cut and paste this line into any form's code module without modification, since the Me keyword will always refer to the current form.

--JP

Quote:

Originally Posted by Neal

Hi,

I have a routine that is used within several forms and I would like to contain it in a separate module which is referenced by each form.to cut down on coding.

Is there a method that I could perform the following 'With' statement (and similar) using a variable?

Code:

Call MySub("MyFormName")

Code:

 
Public Sub MySub(strFormName as String)
Dim nextRow As Integer
nextRow = getGenericRows()
nextRow = nextRow + 1
With strFormName.fmeGeneric
    'MyCode
End With
 
End Sub

strFormName = The name of the form held as a string
fmeGeneric = A frame within the form

Maybe both the form and frame should be passed as the parameter?

Thanks in advance,