Passing Procuedure to Userform

[Nigel]

I have a userform that is called from a worksheet event. On initialising I
wish to run a procedure held in a standard module, the procedure changes
depending on the value of the worksheet event.

How do I pass the name of the procedure to the userform initialise event ?

--
Cheers
Nigel

[Mike Fogleman]

Nigel, I am having a little problem picturing a worksheet event value. Can
you provide the code you have and where each resides?

Mike F
"Nigel" <[email protected]> wrote in message
news:[email protected]...
>I have a userform that is called from a worksheet event. On initialising I
> wish to run a procedure held in a standard module, the procedure changes
> depending on the value of the worksheet event.
>
> How do I pass the name of the procedure to the userform initialise event ?
>
> --
> Cheers
> Nigel
>
>
>
>

[Norman Jones]

Hi Nigel,

One way, schematically,

Worksheet module
'----------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target > 30 Then
MyInstruction = "a"
ElseIf Target.Value > 20 Then
MyInstruction = "b"
ElseIf Target.Value > 10 Then
MyInstruction = "c"
Else
'Do something else
End If
End If
UserForm1.Show
End Sub
'-----------------------------

Userform Module
'------------------
Private Sub UserForm_Initialize()
Call TestIt
End Sub

Standard Module
--------------------
Option Explicit
Public MyInstruction As String

'---------------------------
Sub TestIt()
If MyInstruction = "a" Then
MacroA
ElseIf MyInstruction = "b" Then
MacroB
Else
'Do nothing
End If
End Sub

'---------------------------
Sub MacroA()
MsgBox "A"
End Sub

'--------------------------
Sub MacroB()
MsgBox "B"
End Sub

'---------------------------
Sub MacroC()
MsgBox "C"
End Sub


---
Regards,
Norman



"Nigel" <[email protected]> wrote in message
news:[email protected]...
>I have a userform that is called from a worksheet event. On initialising I
> wish to run a procedure held in a standard module, the procedure changes
> depending on the value of the worksheet event.
>
> How do I pass the name of the procedure to the userform initialise event ?
>
> --
> Cheers
> Nigel
>
>
>
>

[Tushar Mehta]

In article <[email protected]>, nigel-
[email protected] says...
> I have a userform that is called from a worksheet event. On initialising I
> wish to run a procedure held in a standard module, the procedure changes
> depending on the value of the worksheet event.
>
> How do I pass the name of the procedure to the userform initialise event ?
>
>
Check out the Application object's Run method. It accepts the name of
the procedure to call as a string argument.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

[Nigel]

Thanks everyone that has given me some ideas.

--
Cheers
Nigel



"Tushar Mehta" <[email protected]> wrote in message
news:[email protected]...
> In article <[email protected]>, nigel-
> [email protected] says...
> > I have a userform that is called from a worksheet event. On
initialising I
> > wish to run a procedure held in a standard module, the procedure changes
> > depending on the value of the worksheet event.
> >
> > How do I pass the name of the procedure to the userform initialise event
?
> >
> >
> Check out the Application object's Run method. It accepts the name of
> the procedure to call as a string argument.
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions