Solved Solved: is it possible to date each page as it is saved?

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

matelot2

Thread Starter
Joined
Nov 25, 2009
Messages
228
I have been working recently trying to find a macro that will alter a date on each page as it is altered, maybe by only re-dating the page is I save whilst on it or re-dating only the pages that have been altered.
The reason for this is - as pages are updated if the date is altered another user who has printed that page can see if it is the same as his/her print of been altered without reading the page.
It will also let us know how long it is since the page was last updated.
I have
Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("cable 1c").Range("G1").Value = Date
Sheets("cable 1d").Range("G1").Value = Date
Sheets("cable master").Range("D54").Value = Date
Sheets("box master").Range("D54").Value = Date
Sheets("cable 5").Range("B1").Value = Date
End Sub
(for some reason if I select the # (code tags) above nothing happens, I don't get tags?)
This almost works, it changes all the pages in quotes. What I want is to just change the pages as I mentioned above.
Does anyone know if this is possible? Perhaps by having different macros, say one that asks me what page I want to update?
Any help would be appreciated.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
You did not mention anything about what you're using, seeing the code you supplied is is excel, Version(?)
You should use a Boolean variable for each worksheet
This variable will be False when you start and only be set to True the moment something is actually changed in that particular sheet.
You trigger this using the on change for each worksheet and you can even modify the date at that moment too.
As goes for printing I also suggest you place the date AND time in de sheet's foorter when printing than you know which printout is the latest.
If you require some more help just holler
 

matelot2

Thread Starter
Joined
Nov 25, 2009
Messages
228
Thank you Hans,
I am using excel 2003. I have no experience with macros and took the bones of the macro shown from a forum thread that someone else had started a long time ago. I have altered it to change a number of pages, the original just had one page and so worked as required.
If it is possible to do it as I am wanting would you mind helping me further?
thanks.
Bob.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
Hi Bob,
No problem.
Just to make sure, do you want the date updated for any change made or just in one or more particular columns?
I'll try and put together a simple sample file to illustrate what I mean.
I'm using 2010 but will save it as 2003 file, no problem.
If you have a sample file with non-private non-sensitive data you can attach it.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
Hi Bob,

For starterts you could place the code below in one of the worksheets where the call with the date is G1

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$1" Then Exit Sub
If Range("G1").Value <> Date Then Range("G1").Value = Date
End Sub
Anytime you change something in the sheet and the date is not equal today's date the date is changed except when the change is in the date field itsself.
 

matelot2

Thread Starter
Joined
Nov 25, 2009
Messages
228
thanks so much Hans I will take a look at that , let it work for a few days and see what happens. I will certainly let you know.
Bob.
 

matelot2

Thread Starter
Joined
Nov 25, 2009
Messages
228
I am slowly adding that macro you gave me to each of my pages, (there are 56 sheets at present).
It looks as if it is going to work perfectly.

More for my interest and experience of writing macros I have been experimenting with the one you gave me and one I got originally. The one I got originally

Code:
Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 Sheets("cable 1d").Range("G1").Value = Date
 Sheets("cable 5").Range("B1").Value = Date
 End Sub
(clicking on # above doesn't work for some reason?)
is in the workbook 'page', it looks as if it alters each page mentioned, but alters them all.
is it possible to do something like

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("cable1d").If Target.Address = "$G$1" Then Exit Sub
Sheets("cable1d").If Range("G1").Value <> Date Then Range("G1").Value = Date
End Sub
(just adding the Sheets(). to your macro and putting each line in the workbook 'page')?
If I do just this it says there is a compile error, even if I change worksheet on the first line to workbook.
Do you know of a good publication that would help me learn the macro commands?
thanks,
Bob.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
The workbook_BeforeSave cannot be executed by clicking since it expects parameters to be passed.
This is wat is called an event driven macro
Simply said, the event is pressing the Save button and then the macro BeforeSave is invoked and the code in this module is executed
In you example the values in the mentioned ranges are set to the current date without you knwoing if the sheet has actually been changed.

The second code you placed:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR="Red"] Sheets("cable1d").[/COLOR]If Target.Address = "$G$1" Then Exit Sub
[COLOR="red"] Sheets("cable1d").[/COLOR]If Range("G1").Value <> Date Then Range("G1").Value = Date
 End Sub
The text in red is what is causing the error, invalid syntax thus causing a compilation error

Below is the correct code for each sheet where you want the event to work, you will have to change the G1 to the corresponding cell reference
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$1" Then Exit Sub
If Range("G1").Value <> Date Then Range("G1").Value = Date
End Sub
To make it a little easier you could do the following

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
dim myAddress as string
myAddress = "$G$1"
If Target.Address = myAddress Then Exit Sub
If Range(myAddress).Value <> Date Then Range(myAddress).Value = Date
End Sub
Then all you have to do is paste the macro in each of the worksheets and just change the value in the myAddress variable:

e.g. myAddress="$B"1" or whatever

Hope my explanation helps you a little.
 

matelot2

Thread Starter
Joined
Nov 25, 2009
Messages
228
Thank you Hans that helps, I had suspected something like that.
I am entering your first macro into each sheet and altering the target address and then the ranges as I go.
I am quite confident it is what I was wanting and will check that some of them alter the date tomorrow.
Bob.
ps. I am curious why the # doesn't work for me? It says 'puts code around highlighted text' but when I highlight the text or just press it as you do with other forums nothing happens? Odd.
Code:
I think I should get something like
HA if I enter it manually 'square bracket - code - end square bracket' etc it works.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
I don't understand the # that you're talikng about.

Anyway I'm curious to hear if the macro works tomorrow
 

matelot2

Thread Starter
Joined
Nov 25, 2009
Messages
228
I don't understand the # that you're talikng about.
if I press 'go advanced' I get a bigger box to reply in, the one that I write a new thread in. Along the top of the box are various icons that I can use
# wrap code tags around text
<> wrap HTML tags around text
etc
they don't work from the computer I have in my bedroom running windows 7.
I am using the computer in my study running XP for the first time and they work from here?
The excel program came from the same disk for both computers.

I am also getting problems loading the excel file on here.
I have it in dropbox so I can access it from this computer but it comes up with a box saying the macro doesn't have a digitally signed trusted certificate. I would like other members of the club that the excel file is for to access it in the same way hence the dropbox.
If I lower my security to medium I can accept it but it is not recommended.
I have found out how to sign it but can't see how to make it trusted, is this possible?




After writing this I have been looking at the possibility and come to the conclusion that it is for the safety of files that I can't produce trusted digitally signed certificates without authority and permission. I will just have to discuss with my colleagues if it is viable to lower the security to allow the macros to run.
Bob.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
That's a little beynd my knowledge.
I get the message to enable macros and I do that and it works (In DropBox and OneDrive)
 

matelot2

Thread Starter
Joined
Nov 25, 2009
Messages
228
Well your knowledge is well in advance of mine.
Your macro works perfectly and my colleagues have decided that the only one, besides myself, who need to save the file is happy to leave the security in normal as opposed to high, all the others will pass any corrections to me to implement. In which case your macro is perfect for my purposes and I thank you for your help.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
Good to hear that my idea helped you guys.
Thanks for teh compliment :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top