Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
use vb to determine last cell changed
Is it possible for VB to determine the last cell that was changed. I'm
not referring to a cell changed by a macro, just a cell that was changed by typing into the cell. I want a macro to then update a cell in the same row, column B. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
use vb to determine last cell changed
Hi,
Right click your sheet tab, view code and paste this in. the messagebox bit isn't necessary, it's to demonstrate the method Private Sub Worksheet_Change(ByVal Target As Range) MsgBox "Last changed cell was " & Target.Address Application.EnableEvents = False Cells(Target.Row, 2).Value = "Something" Application.EnableEvents = True End Sub Mike " wrote: Is it possible for VB to determine the last cell that was changed. I'm not referring to a cell changed by a macro, just a cell that was changed by typing into the cell. I want a macro to then update a cell in the same row, column B. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
use vb to determine last cell changed
The answer to your question is yes but, in order to give you code, we will
need more information. Is this functionality to be confined to a single worksheet or do you want to track changes on any worksheet and perform the Column B update on the worksheet where the change was made? Do you want the update in Column B to take place automatically as soon as the cell is changed (which would use an event procedure and not a macro) or only when you execute a macro manually? Are there certain columns that you want to track (if so, which ones) or do you want to track changes to any column (other than Column B, I would guess)? What is the update that you want to perform in Column B? -- Rick (MVP - Excel) wrote in message ... Is it possible for VB to determine the last cell that was changed. I'm not referring to a cell changed by a macro, just a cell that was changed by typing into the cell. I want a macro to then update a cell in the same row, column B. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
use vb to determine last cell changed
Hi Rick, out of curiosity, how do you detect the keystroke so that you can
tell that the change was not the result of code execution? Or did you have a different method in mind? "Rick Rothstein" wrote: The answer to your question is yes but, in order to give you code, we will need more information. Is this functionality to be confined to a single worksheet or do you want to track changes on any worksheet and perform the Column B update on the worksheet where the change was made? Do you want the update in Column B to take place automatically as soon as the cell is changed (which would use an event procedure and not a macro) or only when you execute a macro manually? Are there certain columns that you want to track (if so, which ones) or do you want to track changes to any column (other than Column B, I would guess)? What is the update that you want to perform in Column B? -- Rick (MVP - Excel) wrote in message ... Is it possible for VB to determine the last cell that was changed. I'm not referring to a cell changed by a macro, just a cell that was changed by typing into the cell. I want a macro to then update a cell in the same row, column B. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
use vb to determine last cell changed
I was thinking along these lines. Create a Public Boolean variable in a
Module, set it to True at the start of any macros, check the variable in the Change event and, at the end of the change event, set the variable back to False. Here is a very simple example... In a Module =========================== Public InModule As Boolean In a Macro =========================== Sub MyMacro() InModule = True Range("A1").Value = "Where did I come from?" ' ' Actual code goes here ' End Sub In Worksheet Change event =========================== Private Sub Worksheet_Change(ByVal Target As Range) If Not InModule Then MsgBox "Cell was **NOT** changed by a macro" ' ' Actual code goes here ' Else MsgBox "Cell changed by a macro" ' ' Actual code goes here ' End If InModule = False End Sub -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... Hi Rick, out of curiosity, how do you detect the keystroke so that you can tell that the change was not the result of code execution? Or did you have a different method in mind? "Rick Rothstein" wrote: The answer to your question is yes but, in order to give you code, we will need more information. Is this functionality to be confined to a single worksheet or do you want to track changes on any worksheet and perform the Column B update on the worksheet where the change was made? Do you want the update in Column B to take place automatically as soon as the cell is changed (which would use an event procedure and not a macro) or only when you execute a macro manually? Are there certain columns that you want to track (if so, which ones) or do you want to track changes to any column (other than Column B, I would guess)? What is the update that you want to perform in Column B? -- Rick (MVP - Excel) wrote in message ... Is it possible for VB to determine the last cell that was changed. I'm not referring to a cell changed by a macro, just a cell that was changed by typing into the cell. I want a macro to then update a cell in the same row, column B. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
use vb to determine last cell changed
Oops! I forgot (although I'm sure you figured it out)... alternate between
running the macro and typing or pasting data into cells and watch the MessageBox'es. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... I was thinking along these lines. Create a Public Boolean variable in a Module, set it to True at the start of any macros, check the variable in the Change event and, at the end of the change event, set the variable back to False. Here is a very simple example... In a Module =========================== Public InModule As Boolean In a Macro =========================== Sub MyMacro() InModule = True Range("A1").Value = "Where did I come from?" ' ' Actual code goes here ' End Sub In Worksheet Change event =========================== Private Sub Worksheet_Change(ByVal Target As Range) If Not InModule Then MsgBox "Cell was **NOT** changed by a macro" ' ' Actual code goes here ' Else MsgBox "Cell changed by a macro" ' ' Actual code goes here ' End If InModule = False End Sub -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... Hi Rick, out of curiosity, how do you detect the keystroke so that you can tell that the change was not the result of code execution? Or did you have a different method in mind? "Rick Rothstein" wrote: The answer to your question is yes but, in order to give you code, we will need more information. Is this functionality to be confined to a single worksheet or do you want to track changes on any worksheet and perform the Column B update on the worksheet where the change was made? Do you want the update in Column B to take place automatically as soon as the cell is changed (which would use an event procedure and not a macro) or only when you execute a macro manually? Are there certain columns that you want to track (if so, which ones) or do you want to track changes to any column (other than Column B, I would guess)? What is the update that you want to perform in Column B? -- Rick (MVP - Excel) wrote in message ... Is it possible for VB to determine the last cell that was changed. I'm not referring to a cell changed by a macro, just a cell that was changed by typing into the cell. I want a macro to then update a cell in the same row, column B. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
use vb to determine last cell changed
That's pretty slick. I was looking for a built-in constant that would either
detect the keystroke event or identify the change as automation, similar to the way it ignores calculations. I knew I had not seen anything previously that would discern the difference. Never even thought about using the absence of an event as a trigger. "Rick Rothstein" wrote: Oops! I forgot (although I'm sure you figured it out)... alternate between running the macro and typing or pasting data into cells and watch the MessageBox'es. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... I was thinking along these lines. Create a Public Boolean variable in a Module, set it to True at the start of any macros, check the variable in the Change event and, at the end of the change event, set the variable back to False. Here is a very simple example... In a Module =========================== Public InModule As Boolean In a Macro =========================== Sub MyMacro() InModule = True Range("A1").Value = "Where did I come from?" ' ' Actual code goes here ' End Sub In Worksheet Change event =========================== Private Sub Worksheet_Change(ByVal Target As Range) If Not InModule Then MsgBox "Cell was **NOT** changed by a macro" ' ' Actual code goes here ' Else MsgBox "Cell changed by a macro" ' ' Actual code goes here ' End If InModule = False End Sub -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... Hi Rick, out of curiosity, how do you detect the keystroke so that you can tell that the change was not the result of code execution? Or did you have a different method in mind? "Rick Rothstein" wrote: The answer to your question is yes but, in order to give you code, we will need more information. Is this functionality to be confined to a single worksheet or do you want to track changes on any worksheet and perform the Column B update on the worksheet where the change was made? Do you want the update in Column B to take place automatically as soon as the cell is changed (which would use an event procedure and not a macro) or only when you execute a macro manually? Are there certain columns that you want to track (if so, which ones) or do you want to track changes to any column (other than Column B, I would guess)? What is the update that you want to perform in Column B? -- Rick (MVP - Excel) wrote in message ... Is it possible for VB to determine the last cell that was changed. I'm not referring to a cell changed by a macro, just a cell that was changed by typing into the cell. I want a macro to then update a cell in the same row, column B. Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
use vb to determine last cell changed
Thanks. Back in my compiled VB days, I developed quite a few of these
cooperative, inter-event type procedures to solve problems which did not have a direct coded solution available. While this one was somewhat different from them, the underlying principle is the same... use one or more global variables as intermediaries between event procedures and have one or more events post a status condition of some kind and have other events check in with the variable(s) and take action according to its contents. -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... That's pretty slick. I was looking for a built-in constant that would either detect the keystroke event or identify the change as automation, similar to the way it ignores calculations. I knew I had not seen anything previously that would discern the difference. Never even thought about using the absence of an event as a trigger. "Rick Rothstein" wrote: Oops! I forgot (although I'm sure you figured it out)... alternate between running the macro and typing or pasting data into cells and watch the MessageBox'es. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... I was thinking along these lines. Create a Public Boolean variable in a Module, set it to True at the start of any macros, check the variable in the Change event and, at the end of the change event, set the variable back to False. Here is a very simple example... In a Module =========================== Public InModule As Boolean In a Macro =========================== Sub MyMacro() InModule = True Range("A1").Value = "Where did I come from?" ' ' Actual code goes here ' End Sub In Worksheet Change event =========================== Private Sub Worksheet_Change(ByVal Target As Range) If Not InModule Then MsgBox "Cell was **NOT** changed by a macro" ' ' Actual code goes here ' Else MsgBox "Cell changed by a macro" ' ' Actual code goes here ' End If InModule = False End Sub -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... Hi Rick, out of curiosity, how do you detect the keystroke so that you can tell that the change was not the result of code execution? Or did you have a different method in mind? "Rick Rothstein" wrote: The answer to your question is yes but, in order to give you code, we will need more information. Is this functionality to be confined to a single worksheet or do you want to track changes on any worksheet and perform the Column B update on the worksheet where the change was made? Do you want the update in Column B to take place automatically as soon as the cell is changed (which would use an event procedure and not a macro) or only when you execute a macro manually? Are there certain columns that you want to track (if so, which ones) or do you want to track changes to any column (other than Column B, I would guess)? What is the update that you want to perform in Column B? -- Rick (MVP - Excel) wrote in message ... Is it possible for VB to determine the last cell that was changed. I'm not referring to a cell changed by a macro, just a cell that was changed by typing into the cell. I want a macro to then update a cell in the same row, column B. Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
use vb to determine last cell changed
Rick,
Interesting options you brought up to consider, my need is very simple. See repsonse below. On Oct 4, 12:16*pm, "Rick Rothstein" wrote: Do you want the update in Column B to take place automatically as soon as the cell is changed or only when you execute a macro manually? Not automatically, only when the macro is invoked manually. Are there certain columns that you want to track (if so, which ones) No, I just need to identify the row where the last change was made. And this is all o the same worksheet. What is the update that you want to perform in Column B? I have a currently working macro to enter date and some other info. Right now I manually select the cell so I'm just looking to automate the process to identify the correct cel to update. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
use vb to determine last cell changed
Let's use the idea I proposed. Add the following to a module...
Public InModule As Boolean Put this procedure in the code window for the worksheet you want this functionality on... Private Sub Worksheet_Change(ByVal Target As Range) If Not InModule Then Call YourMacro(Target.Row) End If InModule = False End Sub Here I have assumed your macro is named YourMacro (change it as appropriate) and I further assumed it will be modified to take one argument, the row number of the cell just changed by your typing in an entry. To add the argument to your macro (it will cease to be a macro once you do this; instead, it will just be a plain subroutine), just create an argument for it between the parentheses where the macro is declared. For example, if you current macro is declared like this... Private Sub MyMacro() ' ' Your code is located here ' End Sub then simple make it look like this... Private Sub MyMacro(CurrentRow As Long) ' ' Your code is located here - wherever you now refer to the row ' for the active cell, just use the CurrentRow argument instead. ' End Sub -- Rick (MVP - Excel) "John" wrote in message ... Rick, Interesting options you brought up to consider, my need is very simple. See repsonse below. On Oct 4, 12:16 pm, "Rick Rothstein" wrote: Do you want the update in Column B to take place automatically as soon as the cell is changed or only when you execute a macro manually? Not automatically, only when the macro is invoked manually. Are there certain columns that you want to track (if so, which ones) No, I just need to identify the row where the last change was made. And this is all o the same worksheet. What is the update that you want to perform in Column B? I have a currently working macro to enter date and some other info. Right now I manually select the cell so I'm just looking to automate the process to identify the correct cel to update. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
use vb to determine last cell changed
On Oct 4, 12:15*pm, Mike H wrote:
Hi, Right click your sheet tab, view code and paste this in. the messagebox bit isn't necessary, it's to demonstrate the method Private Sub Worksheet_Change(ByVal Target As Range) MsgBox "Last changed cell was " & Target.Address Application.EnableEvents = False Cells(Target.Row, 2).Value = "Something" Application.EnableEvents = True End Sub Mike " wrote: Is it possible for VB to determine the last cell that was changed. I'm not referring to a cell changed by a macro, just a cell that was changed by typing into the cell. I want a macro to then update a cell in the same row, column B. Thanks- Hide quoted text - - Show quoted text - Mike, I've finally found some time to follow up on the suggestions in this string (Rick, I'll try your idea soon). For your suggestion I'm learning some more new stuff. I've never put code on a worksheet tab so I've got to understand that better. The message box indeed displays the cell just changed. That's cool! So now I have this Private Sub on a sheet (code) and my original macro1() on a module1 (code). I think the target.row will get updated everytime new data is entered in a cell. And your code went on to further enter something in column B of that row, but I don't want to update cell B just yet. I may have several cells in the row I want to change, and then when I am done invoke the macro, which first needs to check that it is in the right row, and then update the cell in column B. (The purpsoe of my original request was a form of an error check. When users enter data they can use the enter key or a tab key. In one case the active cell is left in the wrong row.) So, I think to use your method I need to understand how to pass the value of the target to my macro. is this possible? Did my description make any sense? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a formula to determine when the data was last changed | Excel Discussion (Misc queries) | |||
Determine if the sheet has been changed | Excel Programming | |||
determine if a cell changed value | Excel Worksheet Functions | |||
in vba what command is used to determine if a particular cell on a particular sheet changed? some kind of event? how to get the old and new value of the cell? | Excel Worksheet Functions | |||
How can I determine which cell changed as a result of a calculati. | Excel Worksheet Functions |