1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by matelot2, Apr 17, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. matelot2

    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.
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
     
  3. matelot2

    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.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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.
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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.
     
  6. matelot2

    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.
     
  7. matelot2

    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.
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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.
     
  9. matelot2

    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.
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I don't understand the # that you're talikng about.

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

    matelot2 Thread Starter

    Joined:
    Nov 25, 2009
    Messages:
    228
    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.
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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)
     
  13. matelot2

    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.
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Good to hear that my idea helped you guys.
    Thanks for teh compliment :)
     
  15. Sponsor

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 733,556 other people just like you!

Loading...
Similar Threads - Solved possible date
  1. kramerica
    Replies:
    2
    Views:
    559
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1146728

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice