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

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" wrote in message
...
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

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" wrote in message
...
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

In article , nigel-
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

Thanks everyone that has given me some ideas.

--
Cheers
Nigel



"Tushar Mehta" wrote in message
om...
In article , nigel-
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