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 ?

 

[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

 

[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

 

[Tushar Mehta]

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.