Worksheet_Change event

[Joanne]

Can you have several different little routines run, say in some kind of
sequence or other, in the Worksheet_Change (ByVal Target as Range)
Event.
I am trying to get a better handle on how VBA works in Excel as versus
in Access. Whew, lots of differences.
Thanks for your input
Joanne

 

[Gary''s Student]

I usually put the little routines in a standard module and then call them in
sequence from the Event code.

 

[Mike H]

Yes you can do that. Right click a sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
macro1
macro2
macro3
End Sub
Sub macro1()
MsgBox "This is macro1"
End Sub
Sub macro2()
MsgBox "This is macro2"
End Sub
Sub macro3()
MsgBox "This is macro3"
End Sub

 

[Joanne]

Thank you, thank you guys
That clears up a mass of confusion in my head.
I think I read somewhere in the forums or online resources that in
Excell you should not use the "Call" command word when calling routines.
I don't remember what it said the reason for this was, but am I correct
in this?
Thank you

 

[Bob Phillips]

Absolutely not correct.

I always use Call, it helps me to instantly differentiate between my custom
methods and the VBA built-in methods.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JLGWhiz]

Using the Worksheet_Change as the control procedure you
can see below how several procedures can be run by the
change event. If call is used, the arguments must be
included in parentheses for passing to the procedure.

Worksheet Module Proj Module1 XLM Module

Pvt Sub WS_Chng() Sub A() Sub C()
Sub 1() 'Run Sub B()
A 'Calls Sub A
B 'Calls Sub B
Sub 2() 'Run
Call C(arglist) 'Calls Sub C
End Sub

Here is the kicker:
If you use either Call syntax to call any intrinsic or
user-defined function, the function's return value is
discarded.

 

[JLGWhiz]

I don't want to mislead you there, The Sub 1 and Sub 2 are actually not
actual subs but are procedures as part of the Worksheet change Sub.

 

[Joanne]

So could you give me an example of how to use Call

Do I simply say Call SubName?

Thanks a lot for your help
Joanne

 

[Bob Phillips]

Yes, if the procedure has no arguments, it is simply

Call proc_name

If it hasrgumnets, the values are passed in brackets

Call proc_name(value1, "Text2")

and if it is a function returning a value then any parameters are in
brackets , using call or not

myVar = my_func(param1, param2)

or

myVar = Call my_func(param1, param2)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)