I'd like to automatically record a date stamp of when anything on an individual sheet -Not workbook- was last edited.
It will be used on my macro-enabled template, which will be used repeatedly throughout a macro-enabled workbook. I don't need/want the time. "mmmm dd yyyy" would be ideal.
Seems like this should be easy!
Where do you want the date stamp to be recorded ??I'd like to automatically record a date stamp
- Battle without fear gives no glory - Just try
Cell F3 of that sheet
In the sheets'code put
Please Login or Register to view this content.
hmm... It doesn't seem to do anything for me. I'll try playing with it a bit later on to see if I can get it to workOriginally Posted by PCI
In the sheets'code put
Please Login or Register to view this content.
Update: Ignore that. Got it working and its running perfect. Thank you! For some reason all VBA had stopped executing when I tried it the first time.
Last edited by number9; 01-16-2020 at 12:15 AM.
A reason could be that you do not exit properly from the macro and did not finish it and did not restaure eventsFor some reason all VBA had stopped executing when I tried it the first time.
Next statement not done
To solve it:Please Login or Register to view this content.
Restart Excel
or
Use next sub in a module
Please Login or Register to view this content.
Yeah, I keep having issues with VBA not running. I think my issue might be that I'm not combining it with the following code properly:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("A4")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub
What is the best way for me to combine these?
Last edited by number9; 01-16-2020 at 09:11 PM.
number9, do not forget code tags when posting code .. see forum's rules ...!
Try next code else post an Excel sample
Please Login or Register to view this content.
thanks! will do. I still can't get it to work.
Example is attached(Saved as workbook because uploading template returned "invalid file type")
What do you want to do with next code
See file attachedPlease Login or Register to view this content.
Last edited by PCI; 01-23-2020 at 03:41 PM.
A reason could be that you do not exit properly from the macro and did not finish it and did not restaure events
Next statement not done
To solve it...Please Login or Register to view this content.
Restart Excel
Or, simply type
in the immediate windowPlease Login or Register to view this content.
Tim
Last edited by harrisonland; 01-23-2020 at 04:12 PM.
The idea behind both sets of code is to;What do you want to do with next code
See file attachedPlease Login or Register to view this content.
1) -cell F3- Record or update the date, any time a change is made to that sheet
2) Automatically name the sheet by combining the cell value of cell F3 with the cell value of cell B9. (To achieve the renaming in the past, I combined cell F3 and B9 into cell A4, then referenced A4 in the macro
I cant see any changes to the attached file other than there being an error and comments have been removed.Please Login or Register to view this content.
I think it's not a good idea to set the "Target" manually. Best to leave that alone, and define another range instead. Note I'd run this on a change, not a selection change otherwise it'll be running willy-nilly all the time even if the actual data on the sheet haven't changed.
It is also debatable whether it's a good idea to run this on every change. Won't make a difference if it's only run every so often, but aside from the load there are side effects. You are aware, I assume, that each time VBA runs on such an event it resets the "undo" feature in Excel, for example. Would it not be more circumspect to trigger the update if one or more specific cells are changed? Anyhow...
How about this:Please Login or Register to view this content.
Tim
Never stop learning!
<--- please consider *-ing !
Thanks Tim. That does work well.I think it's not a good idea to set the "Target" manually. Best to leave that alone, and define another range instead. Note I'd run this on a change, not a selection change otherwise it'll be running willy-nilly all the time even if the actual data on the sheet haven't changed.
It is also debatable whether it's a good idea to run this on every change. Won't make a difference if it's only run every so often, but aside from the load there are side effects. You are aware, I assume, that each time VBA runs on such an event it resets the "undo" feature in Excel, for example. Would it not be more circumspect to trigger the update if one or more specific cells are changed?
Tim
VBA is all new for me so had no idea that it resets the undo feature... and thank you for the explanation.
If it could just run whenever there were changes to B9 and/or named cell "TotalCharged", that should would work as well. Any chance you could recommend how I'd code that?
To narrow it down to specific cells, you can use the "intersect" function. That checks to see if two or more ranges have cells in common.
"Target" is the range (here: cell) that's just been changed, so we intersect "Target" with the range(s) we want the code to run from. If the two have cells in common we know to run the code:I tried to build in at least some explanation with the comments. Does that make sense to you?Please Login or Register to view this content.
Tim
NB: Forgot to say, I've bloated that a bit for explanation purposes. In practice, I'd probably just use this:Please Login or Register to view this content.
Last edited by harrisonland; 01-24-2020 at 06:02 PM.
That's great, thanks!
Unfortunately I'm still having issues though. It works great when values are manually entered in those cells, but doesn't seem to respond to a vlookup function I have in "B9", or sum function in "TotalCharged". I didn't post those in the example I uploaded above for simplification purposes. I'm guessing its seeing the equation(s) as being unchanged, not the resulting data? Is there a work around for this?
I suspect you're probably right about that - the cell's been recalculated but it hasn't changed, the worksheet_change event only fires when the cell's changed manually.
I think you'd have to modify the code so it fires on cells where the user inputs their data, instead of where your formulae are.
Tim
Hmm... that's going to be a problem. The idea is to get the entire sheet automated.
(and although the sheet/book data is entered onto is technically macro-enabled, Data will be entered on a mobile device through a version of excel that doesn't currently support VBA)
Maybe I could create a button and link it to that...
I've turned it into a selection change for an easy way to make it work for the time being.
You mentioned above that you wouldn't use a selection change. Is there other downsides to this other than the undo option turned off and slower speed?
Last edited by number9; 02-04-2020 at 07:08 PM.
Sorry for late reply - been away for a bit.
I wouldn't use selection change because the event will fire every time a user moves from one cell to the next. It would fire if the target cell were selected, irrespective of whether the value there had changed or not.
If a user opens the worksheet, pokes around then closes it again, the event could fire and update the date stamp... if that doesn't both you then it's fine, but it wasn't what you asked for in the description!
Hope this was all of some help, anyway.
Tim
PS - Please don't forget to mark teh question as SOLVED using the thread tools at the top of the page!
PPS - thanks for rep!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks