Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Macro save button

  1. #1
    New Member
    Join Date
    May 2010
    Location
    Canada
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Macro save button

    Hello everyone!!!!

    Here is my situation. I have been trying to learn macros but have not made one yet. Need your help. Here at work, I have some incompentant computer workers who claim they KNOW MS sooo well and have certificates etc... *COUGH COUGH* ya ok... LMAO

    We have a "shared" work book we all work in. It's nothing fancy, just use it as a PO Book but since it is shared some people continue to not save before they add in more work so that they see a "refreshed" copy of the workbook before they start writing in cells that already contain information.

    How to I create a macro button at the top of the page that is literally a SAVE button.. instead of them saving by going to File - Save or cntrl+s ??? I just thought that MAYBE having a big button in their face will remind them.

    Secondly as back up I was wondering if you can create a macro to do a refresh of everyones screens automatically - say every 2 or 5 mins??? I don't mean a save... but a refresh so the screen actually updates in front of you. (although I will keep this little piece of magic out of their knowledge so they don't rely on it, just though it would be a secondary back up to helping eliminate these mishaps cause we are loosing alot of information by people saving overtop of other peoples work and not caring.)

    Thanks so much!!!!! Muchly appriciated.

  2. #2
    Board Regular pedie's Avatar
    Join Date
    Apr 2010
    Location
    INDIA
    Posts
    3,875
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro save button

    Go to deveoper tab > insert the button > double click on it >>
    enter this code
    Code:
    Private Sub CommandButton1_Click()
    ThisWorkbook.Saved = True
    End Sub
    then click on design mode to exit the design mode. then click on it whenever you want to save
    Last edited by pedie; Aug 19th, 2010 at 07:52 PM. Reason: edit
    Regards,
    Pedie
    MS OFFICE 2016/EXCEL 2016: Window7: 64BIT
    Visit YouTube.com/VBAa2z


  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    16,236
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro save button

    Firstly
    Using the Drawing toolbar, draw a nice big circle at the top of the page and colur it in
    (Red is nice), it gets their attention
    Right click on the sheet tab and select view code.
    Copy this into the code window
    Code:
    Sub Macro1()
        ActiveWorkbook.Save
    End Sub
    right click on your big circle and select assign Macro and select this one.

    Secondly, from what you have stated , I wouldn't do a refresh every 5 mins, because some people in the org might think it has been saved.
    Either save it or don't would be my recommendation!!
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #4
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    16,236
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro save button

    If you want to consider an Autosave every 5 mins or so, have a look here.
    from MS Help
    Code:
    On the Tools menu, click Options, and then click the Save tab. 
    Select the Save AutoRecover info every check box. 
    In the minutes box, specify how often you want your file save.
    BTW the above is from Excel 03
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  5. #5
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,534
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Macro save button

    Just a heads up Pedie,

    ThisWorkbook.Saved = True will make Excel think that the workbook has been saved, and will allow closing without saving.

    You can set this property to True if you want to close a modified workbook without either saving it or being prompted to save it.

  6. #6
    New Member
    Join Date
    May 2010
    Location
    Canada
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro save button

    Thank You Micheal, it seems to work!!!

    Quote Originally Posted by Michael M View Post
    Firstly
    Using the Drawing toolbar, draw a nice big circle at the top of the page and colur it in
    (Red is nice), it gets their attention
    Right click on the sheet tab and select view code.
    Copy this into the code window
    Code:
    Sub Macro1()
        ActiveWorkbook.Save
    End Sub
    right click on your big circle and select assign Macro and select this one.

    Secondly, from what you have stated , I wouldn't do a refresh every 5 mins, because some people in the org might think it has been saved.
    Either save it or don't would be my recommendation!!

  7. #7
    New Member
    Join Date
    May 2010
    Location
    Canada
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro save button

    Do you know if this will refresh everyone's screen automatically though??? without them hitting save??? That is what I ultimately would like. Although I will keep this option a secret so they don't get used to it. They aren't that savey that they will clue in. It's mean to say I know, I don't mean to be mean but its true. I just want to reduce my headaches, lol. I get enough migraines as it is. Hopefully next year the boss will by a proper management system but as of now I am told it won't happen this year.

    Quote Originally Posted by Michael M View Post
    If you want to consider an Autosave every 5 mins or so, have a look here.
    from MS Help
    Code:
    On the Tools menu, click Options, and then click the Save tab. 
    Select the Save AutoRecover info every check box. 
    In the minutes box, specify how often you want your file save.
    BTW the above is from Excel 03

  8. #8
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    16,236
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro save button

    Hi Cait
    No, every machine would need the settings changed to reflect this.
    Another option might be to put an ONTIME method in a macro that saves every XXXX minutes
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •