Macro save button

cait2002

New Member
Joined
May 3, 2010
Messages
33
Hello everyone!!!! :biggrin:

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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,171
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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!!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,171
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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.
 

cait2002

New Member
Joined
May 3, 2010
Messages
33
Thank You Micheal, it seems to work!!!

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!!
 

cait2002

New Member
Joined
May 3, 2010
Messages
33
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.

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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,171
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,606
Messages
5,765,387
Members
425,281
Latest member
tmoreira001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top