Invoke Macro on entering data in column
 

Hi all
Getting a bit frustrated with this. Using Excel 2007.
I have a worksheet "CallList" with Column P named "Lead"
I am attempting to invoke a "Sort"macro automatically that would be
triggered by the insertion of data into Column P. Unsure of code to call
macro from VBA. I think its should be invoked from the worksheet module. This
macro will be stored in Personal workbook but I need to be able to import it
into a number of different workbooks each week.
Any help would be appreciated.

Thanks very much!!

Invoke Macro on entering data in column
 

Insert the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set p = Range("P:P")
If Intersect(t, p) Is Nothing Then Exit Sub
Application.EnableEvents = False
Call mySort
Application.EnableEvents = True
End Sub


mySort can be in either the worksheet code area or a public module.
--
Gary''s Student - gsnu200827


"GeorgeR" wrote:

Hi all
Getting a bit frustrated with this. Using Excel 2007.
I have a worksheet "CallList" with Column P named "Lead"
I am attempting to invoke a "Sort"macro automatically that would be
triggered by the insertion of data into Column P. Unsure of code to call
macro from VBA. I think its should be invoked from the worksheet module. This
macro will be stored in Personal workbook but I need to be able to import it
into a number of different workbooks each week.
Any help would be appreciated.

Thanks very much!!

Invoke Macro on entering data in column
 

Hi GS
Thanks for the input. However after pasting your code I get a variable not
defined at "t" "set t as target.

"Gary''s Student" wrote:

Insert the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set p = Range("P:P")
If Intersect(t, p) Is Nothing Then Exit Sub
Application.EnableEvents = False
Call mySort
Application.EnableEvents = True
End Sub


mySort can be in either the worksheet code area or a public module.
--
Gary''s Student - gsnu200827


"GeorgeR" wrote:

Hi all
Getting a bit frustrated with this. Using Excel 2007.
I have a worksheet "CallList" with Column P named "Lead"
I am attempting to invoke a "Sort"macro automatically that would be
triggered by the insertion of data into Column P. Unsure of code to call
macro from VBA. I think its should be invoked from the worksheet module. This
macro will be stored in Personal workbook but I need to be able to import it
into a number of different workbooks each week.
Any help would be appreciated.

Thanks very much!!

Invoke Macro on entering data in column
 

Thanks Gary's Student I removed option Explicit and it works great

"Gary''s Student" wrote:

Insert the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set p = Range("P:P")
If Intersect(t, p) Is Nothing Then Exit Sub
Application.EnableEvents = False
Call mySort
Application.EnableEvents = True
End Sub


mySort can be in either the worksheet code area or a public module.
--
Gary''s Student - gsnu200827


"GeorgeR" wrote:

Hi all
Getting a bit frustrated with this. Using Excel 2007.
I have a worksheet "CallList" with Column P named "Lead"
I am attempting to invoke a "Sort"macro automatically that would be
triggered by the insertion of data into Column P. Unsure of code to call
macro from VBA. I think its should be invoked from the worksheet module. This
macro will be stored in Personal workbook but I need to be able to import it
into a number of different workbooks each week.
Any help would be appreciated.

Thanks very much!!

Invoke Macro on entering data in column
 

Better yet would have been to leave the Option Explicit statement in and
just declare the variables the Gary''s Student left out; namely, Dim both t
and p as Range.

--
Rick (MVP - Excel)


"GeorgeR" wrote in message
...
Thanks Gary's Student I removed option Explicit and it works great

"Gary''s Student" wrote:

Insert the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set p = Range("P:P")
If Intersect(t, p) Is Nothing Then Exit Sub
Application.EnableEvents = False
Call mySort
Application.EnableEvents = True
End Sub


mySort can be in either the worksheet code area or a public module.
--
Gary''s Student - gsnu200827


"GeorgeR" wrote:

Hi all
Getting a bit frustrated with this. Using Excel 2007.
I have a worksheet "CallList" with Column P named "Lead"
I am attempting to invoke a "Sort"macro automatically that would be
triggered by the insertion of data into Column P. Unsure of code to
call
macro from VBA. I think its should be invoked from the worksheet
module. This
macro will be stored in Personal workbook but I need to be able to
import it
into a number of different workbooks each week.
Any help would be appreciated.

Thanks very much!!

Invoke Macro on entering data in column
 

Thanks Rick
Will give it a try.

"Rick Rothstein" wrote:

Better yet would have been to leave the Option Explicit statement in and
just declare the variables the Gary''s Student left out; namely, Dim both t
and p as Range.

--
Rick (MVP - Excel)


"GeorgeR" wrote in message
...
Thanks Gary's Student I removed option Explicit and it works great

"Gary''s Student" wrote:

Insert the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set p = Range("P:P")
If Intersect(t, p) Is Nothing Then Exit Sub
Application.EnableEvents = False
Call mySort
Application.EnableEvents = True
End Sub


mySort can be in either the worksheet code area or a public module.
--
Gary''s Student - gsnu200827


"GeorgeR" wrote:

Hi all
Getting a bit frustrated with this. Using Excel 2007.
I have a worksheet "CallList" with Column P named "Lead"
I am attempting to invoke a "Sort"macro automatically that would be
triggered by the insertion of data into Column P. Unsure of code to
call
macro from VBA. I think its should be invoked from the worksheet
module. This
macro will be stored in Personal workbook but I need to be able to
import it
into a number of different workbooks each week.
Any help would be appreciated.

Thanks very much!!