| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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!! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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!! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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!! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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!! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| transfering data in another column entering more sorted data | Excel Worksheet Functions | |||
| Entering Data into Column C based on word from Column A | Excel Programming | |||
| how to move the cursor to column A after entering data column F | New Users to Excel | |||
| add data to combo box in excel then invoke macro | Excel Worksheet Functions | |||
| How do you Stop Entering Duplicate Data in a Column? | Excel Worksheet Functions | |||
Linear Mode
