Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execute a macro when the contents of A1 changes
I知 trying to get a marco embedded in a worksheet to run when A1
changes, I知 using Private Sub Worksheet_Change(ByVal Target As Range) To run the code with If ActiveCell = Cells(2, 1) Or ActiveCell = Cells(1, 2) Then To test if the current cell is an adjacent cell to A1 has been selected. This seems to be an inefficient method and not 100% reliable has anyone a better suggestion ------------------------------------------------ Message posted from http://www.ExcelTip.com/ -- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum -- Hundreds of free MS Excel tips, tricks and solutions at http://www.ExcelTip.com/ ------------------------------------------------ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execute a macro when the contents of A1 changes
What you have written compares the VALUE in the active cell with the VALUE in the cell below and
the VALUE in the cell to the right. You want to know whether two object variables point to the same object. For that, the syntax is If ActiveCell Is Cells(2, 1) Or ActiveCell Is Cells(1, 2) Then Note "Is", not "=". All of that said, "Is" doesn't work. It should, but it doesn't. Target is the cell that was just changed. If changing A1 is the important event, your code should refer to Target, not to ActiveCell. (Remember, the user may have the worksheet set to move the cell pointer in any of the 4 directions or not move it at all, when he/she presses Enter, so you can't count on the active cell being A2 or B1. It could still be A1.) Assuming that you want the code to run when the user puts new data into A1, then write it as Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then OTOH, you talk about "has been selected". If you are concerned with the user selecting a new cell, rather than changing the contents of A1, then you should use the Worksheet_SelectionChange event, not Worksheet_Change. The former is triggered by just selecting a new cell. The latter is triggered only when the user enters/edits a value in a cell. On Sat, 23 Aug 2003 17:48:45 -0400, paul.nielson wrote: I知 trying to get a marco embedded in a worksheet to run when A1 changes, I知 using Private Sub Worksheet_Change(ByVal Target As Range) To run the code with If ActiveCell = Cells(2, 1) Or ActiveCell = Cells(1, 2) Then To test if the current cell is an adjacent cell to A1 has been selected. This seems to be an inefficient method and not 100% reliable has anyone a better suggestion ------------------------------------------------ Message posted from http://www.ExcelTip.com/ -- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum -- Hundreds of free MS Excel tips, tricks and solutions at http://www.ExcelTip.com/ ------------------------------------------------ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execute a macro when the contents of A1 changes
Myrna:
Thanks for your comments. You are truly teaching here; better than most excel books I own. When I see your name (as a responder) I automatically "jump in" as I know I will learn something new and helpful (that I previoulsy didn't understand). Thanks, JMay "Myrna Larson" wrote in message ... What you have written compares the VALUE in the active cell with the VALUE in the cell below and the VALUE in the cell to the right. You want to know whether two object variables point to the same object. For that, the syntax is If ActiveCell Is Cells(2, 1) Or ActiveCell Is Cells(1, 2) Then Note "Is", not "=". All of that said, "Is" doesn't work. It should, but it doesn't. Target is the cell that was just changed. If changing A1 is the important event, your code should refer to Target, not to ActiveCell. (Remember, the user may have the worksheet set to move the cell pointer in any of the 4 directions or not move it at all, when he/she presses Enter, so you can't count on the active cell being A2 or B1. It could still be A1.) Assuming that you want the code to run when the user puts new data into A1, then write it as Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then OTOH, you talk about "has been selected". If you are concerned with the user selecting a new cell, rather than changing the contents of A1, then you should use the Worksheet_SelectionChange event, not Worksheet_Change. The former is triggered by just selecting a new cell. The latter is triggered only when the user enters/edits a value in a cell. On Sat, 23 Aug 2003 17:48:45 -0400, paul.nielson wrote: I'm trying to get a marco embedded in a worksheet to run when A1 changes, I'm using Private Sub Worksheet_Change(ByVal Target As Range) To run the code with If ActiveCell = Cells(2, 1) Or ActiveCell = Cells(1, 2) Then To test if the current cell is an adjacent cell to A1 has been selected. This seems to be an inefficient method and not 100% reliable has anyone a better suggestion ------------------------------------------------ Message posted from http://www.ExcelTip.com/ -- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum -- Hundreds of free MS Excel tips, tricks and solutions at http://www.ExcelTip.com/ ------------------------------------------------ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execute a macro when the contents of A1 changes
Thank you for that comprehensive response to my enquiry. I copied your
line of code If Target.Address = "$A$1" Then And the whole macro worked a treat. I couldn稚 find anything in the documentation I have to explain what you put together and I now understand how the Private Sub Worksheet_Change(ByVal Target As Range) statement works. Just to explain a little more about the project. It痴 an attendance register with 31 date columns with personnel names in the first column, these are printed off several months in advance. The date is entered in cell A1 (Hence the test for A1 value change) and the macro hides the end columns for the months that are less than 31. Conditional formatting within the worksheet colours the weekends and a lookup statement finds public holidays for a second condition. Once again thanks for the help. ------------------------------------------------ Message posted from http://www.ExcelTip.com/ -- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum -- Hundreds of free MS Excel tips, tricks and solutions at http://www.ExcelTip.com/ ------------------------------------------------ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Execute Macro | Excel Discussion (Misc queries) | |||
Auto Execute Macro | Excel Worksheet Functions | |||
Auto-execute macro.... | Excel Discussion (Misc queries) | |||
Execute Macro automatically after hyperlink | Excel Discussion (Misc queries) | |||
Execute Macro | Excel Discussion (Misc queries) |