Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Excel Forum

Disable Save But Allow Macro To Save

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am using the code below to disable the save function very successafully. However, is there a work around to allow a macro to save?
----------------------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'this disables the save function on the XLS

MsgBox "****Save is Disabled****"
' Following line will prevent all saving
Cancel = True
' Following line will prevent the Save As Dialog box from showing
If SaveAsUI Then SaveAsUI = False
End Sub

View Answers     

Similar Excel Tutorials

Overwrite an Existing File Using VBA in Excel
How to allow your macro/vba code to overwrite an existing Excel file. This allows you to do things like, export a w ...
Close Excel Workbook using VBA Macros
How to close an Excel workbook using VBA and macros, including how to save the file before you close it or discard ...
Disable/Enable Buttons in UserForms
How to have a button in a UserForm disabled until the desired event occurs. For instance, you could prevent a "subm ...
Disable Calculation of Cells when Running a Macro in Excel
How to stop formulas and functions from updating in Excel when running a macro. This can save you a lot of time if ...
Export an Excel File to a CSV File
How to export an Excel file to a CSV file (comma separated values file).  This allows you to turn any Excel spread ...
Create UserForm Control Templates
How to create a UserForm Controls template that will allow you to quickly add pre-formatted buttons, input areas, c ...

Helpful Excel Macros

Disables the "Save As" Feature in Excel
- This macro will disable the Save As feature in excel. This means that a user will not be able to resave the workbook un
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Save the Current Worksheet as a New File in the Current Folder
- This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from
Close a Workbook after a Time Limit is Reached
- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai

Similar Topics







I did a bit of browsing on this problem. Found others suffering the same but haven't found any conclusive answer yet.

Every so often when I attempt to save a file, (including save as), Excel won'r let me. By won't let me I mean:

using Save doesn't appear to do anything using Save As doesn't either do anything, the dialog is not displayed and if I am doing via the File menu then the File menu is exited and the previous ribbon tab is displayed (i.ethe one I was on before clicking 'File') if I close the workbook I am prompted to save, close without saving or cancel. Clicking save just invokes the same msgbox again. I can't work out when it goes into this mode. Some days I can work without this problem, other days I encounter this 2 or 3 times.

The only thing I could suspect was I think this started around about the time I installed xlDennis' code library. I have uninstalled the addin and so far so good, but I cannot categorically say that this was the cause.

Anyone have any idea?

Cheers
Jon

Edit: I have read this: http://support.microsoft.com/kb/271513
Doesn't seem to cover the issue I describe


I was wondering if there is a way of getting a macro to open the "save as" dialogue box and then have it fill in the "file name" field.

I dont want it to actually make the save though as I want the user to have the chance to check that the save location is correct and the option to change the filename if required.

Cheers.


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.


Hello,

I have a number of different files that I often need to run a macro on. In order for me to do it on the 75-100 files I have at any given time, I need to open one, run the macro, close and save, then open the next one.

Is it possible to write a macro that will start with the first file in a folder, open it and update links, run a macro, save and close, and open the next file in the folder until it has open all the files in the folder.

I have experience with creating macros that reference different workbooks, but not sure how to go about opening files with different filenames (without referencing the exact filename).

I'd like to be able to have basic code for opening, saving and closing, opening next file, saving and closing, etc. and input the macro I'd need to run in each file in the appropriate location. Is this possible? Any help is greatly appreciated!!

Thanks,
Jason


Arcangelo from Italy asks: How can I write an Excel VBA macro to save the current Excel file with a filename derived from cell A1?
This macro is amazingly straight-forward:

Public Sub SaveAsA1()
ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub
I'm totally lost on this "amazingly straight-forward" macro!!!! Could someone help if I tell you the SAVE location? It's T:\COMMISSIONING\IJT\TIMELOG project\Staff#1. I'm just not certain what value I'm replacing in the Macro above.

Any help appreciated..



Can anyone help me with creating a macro that will save the worksheet as a pdf in a specific folder with the value of a cell and the date. This is what I have so far.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("H10").Value & Format(Date, "mmdd")

This saves it as the correct cell value + the date, but it saves it to my documents. I need it to save to a shared folder on a network drive.

Any help would be greatly appreciated.
THANKS


Hey guys,

I'm trying to write a macro which prints to PDF and saves the file name as the contents of a cell. I've been looking through all the posts currently on this forum to get something working. I'm using the following code -

Sub PrintPDF()
Filename = "C:\Documents and Settings\samb\My Documents\" & ActiveSheet.Range("Z1").Value
SendKeys Filename & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF:", Collate:=True
End Sub

The macro prints to PDF, but then it stops at the Save As stage, where I have to manually enter the name of the file and click Save. I want the macro to automatically name the file with the contents of cell Z1. I then want it to then automatically press enter. Any ideas where I'm going wrong? Any help would be much appreciated!


We have a large (4,000+ rows) excel worksheet from a prior employee that I
would like to be able to use, but it is password protected and no one has the
password. Is there a way to copy or save it as a different file that will
not copy the password protection? It would save me a couple days of work if
it is possible.
Thanks



I know this question has been asked a bajillion times, so I apologize for the redundancy.

I am working with an Excel spreadsheet and saving it as a .csv file in order to upload to an application that parses out the .csv data as transactions. The system requires .csv files, so this is how I need to save my doc (with this extension). I have been successful at preventing Excel from coverting that long number into scientific format. I have saved as a TXT file, pasted the longer number and it displays correctly. That is all good. But I have to save as a .csv. So if I do that, close the Excel window, and then open again (as the .csv file), the numbers are back to being displayed in scientific format. I have tried creating an Excel doc from scratch and entering text in Text format, to see if this created a cleaner file. But again, the second I save as .csv, close the window and then open that file up again, that dang scientific format is back.

Does anyone have any idea of how to work around this? Once I have successfully gotten the numbers to display as the long-chain number, how can I get them to "stick" so that they don't revert back to scientific format when I reopen the file?

Thanks so much for your help!


I have an excel form, I want my users to be able to open the form, fill in appropriate data, then click the save button. When the button is clicked, the data in the form should be saved to a new file and then the original form should be reset (clear all values) for a new set of data entry. When the data is saved, I want the new excel sheet to be saved to a location specified within the macro so the user doesn't have any options and I want the title of the new file to be the value of one of the cells.

I'm new to macros and would appreciate any help this board can offer. Thanks...


I have a VBMacro Excel file loaded on a Server that numerous people access. A Macro in this file creates a Copy of a specific Sheet within the Active Workbook and I want to Save it to the individual's Desktop.

How do I find out what the current User's desktop folder path is each time the Marco is run by a different User?

Example User's path: 'C:\Documents and Settings\jfarc\Desktop'

Where 'jfarc' is the name of the current User which, will of course change with every different User that runs the Macro.

Also, is there a way to pull out of Excel what is the current User's 'Options | General | Default File Location' entry? Which may differ from the above directory.

I am familiar with and use the following coding for Opening/Saving files to the current directory of the opened workbook, but it only gives the path of the existing Excel workbook and not the current User's Directory Path:

Dim wbThis As Workbook
Set wbThis = ThisWorkbook
ChDir wbThis.Path


I have workbook in which I want to save a specific worksheet to a new file with only the values saved - all data in this worksheet are references to cells on another worksheet, which is using VLOOKUP to pull data from a database.

Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference.

I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN).

What modifications do I make to this to get this to work per above requirements?

Sub CopyMe()
Dim SaveMeAs As String
SaveMeAs = Sheets("Sheet1").Range("B2").Text
Sheets("Sheet3").Copy
ActiveWorkbook.SaveAs Filename:="C:\My Documents\" & SaveMeAs
End Sub


Hi guys,

Looking for some help and would appreciate your help. I want to prevent people cutting/copy/pasting on a spreadsheet I have developed. A couple of users keep doing so which in turn knackers my formulas. There will be occasions where I will need to be able to use these functions for maintenance and updates. Ideally I would like have a private marcro which I can run to enable these features as and when needed.

I've been at this all morning and feel like I'm going round in circles. I've tried various bits of code (sourced via google etc) & have ended up with a whole manner of outcomes but not the one I want.

To summaraise what I am looking for is some vba code which will do the following Disable cut,copy and paste when sheet is opened Enable cut,copy and paste when closed Have a macro which when run will allow me to cut/copy paste so as to implement updates when necessary.
Can anyone help?

Thanks in advance


Is there any way to stop a macro from running when it gets hung up and my excel is "Not Responding"? Now I have to blow it up which is not a desirable method especially when I have made changes that I wanted to save.


I have a co-worker's file that he is having trouble with. He is using Excel 2000 SP3. When copying a cell with a formula in it of "=D6+C6" and pasting it into the next cell down, it will display the same value in the cell as the calculated value from above, but has the correct formula displayed in the formula bar of "=D7+C7".

Example:
A1: 50
A2: 10
B1: 60
B2: 20

A3: Formula: =A1+A2 Displays: 60
Right click A3, Copy, right click B3, paste
A3 displays 60

When I click save, it will change the display value to 80.

I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself.

Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value.

I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem.

Any ideas how to fix this?


I have a sheet with a list of names on and I've noticed that when I use ctrl+F to use the find function, even when I know i have entered the correct name it still will not work. I have identifed times when I know the name is there on the sheet, but the find function says "Excel cannot find the data you are searching for"

Im unable to put any pics up as my work pc has java disable so photobucket ect won't work but has anyone else come across this problem and how did you rectify it? A search macro perhaps?


Does anybody know how to undo a save. I accidently saved
something by the same name and now everything is gone.
PLEASE help!!!



Hello,

i've got the following problem:

I want users to double-click on a row on a protected sheet and then do some code based on the row-number of the clicked cell. I've protected the sheet because it contains a lot of formula's.

When a user double-clicks a row it triggers the code through the Workbook_SheetBeforeDoubleClick event.
After the code is executed Excel shows a message that the cell that was clicked was protected etc etc.

How can I prevent this message from popping up?

I've already tried
Code:

application.displaywarnings = false


but that didn't work

Thanks


Hi all,

I've been getting this error on occasion recently.

- It's a shared document
- There is usually 10-12 people using the file at the same time

I've read on the Microsoft Help site that the issue is that somebody is accessing the file while another is trying to open it.

Is there a way, perhaps through VBA, that I can not allow a user to save while somebody else is opening the document? Other suggestions on how to avoid this error?

Thanks!

I automating a task at work that will save some time. Seems simple enough but for some reason the code I have to cause IE to wait unil ready doesnt seem to work in all cases.

Im using :

'wait until the page loads
Do While ie.Busy Or ie.readyState READYSTATE_COMPLETE
DoEvents
Loop

This doesnt seem to work well enough, since actions are occuring that should NOT occur until the browser is ready.

Any other ideas as to how to make IE wait until the page is completely ready? I wondered about possibly checking the .innerHTML to verify that a unique page element is present.


Thanks,

Scott


I'm having a problem in a workbook with several ActiveX command buttons. I had been using the form control buttons to run macros, but the boss wanted each button to have it's own, different color. So I removed the form control buttons and created new ActiveX command buttons. I got into the button properties and set the background colors. I added the _Click code to run the macros when the user clicked the buttons.

All of the buttons were working fine. Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. Nothing.

If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name "cmdButtonGetInfo" and "=EMBED("Forms.CommandButton.1","") in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work.

It's like the buttons are being disabled when I close or open the file. Any suggestions?


I have a template that automatically populates the date field with the
current date when opened, using today(). Users then Save As to have an
archive copy of their spreadsheet. However, when they go back to access
their saved sheet, the date changes from the created date to the current
date. How do I stop this in Excel?

I know that Word lets you do this by changing the code from {DATE} to
{CREATEDATE}, but I can't find a similar setting in Excel.

Please help and thank you!



Hi
I need to disable all kinds of messages that could appear in front of the user when it opens the workbook. I am already using DisplayAlerts=False and On Error Resume Next? Can we also do this through excel application itself and not using VBA ?


Hi all,

I've had a long search through your pages to see if this question has been answered before but having browsed through about 50 pages worth of threads I couldn't see anything, but if I am repeating prior information I do apologise.

I've written a macro that is relatively simple. It just takes some information in one format, rearranges it, adds some formatting and performs some calculations. Nothing incredibly fancy but it works fine on my computer.

Now, I need to share this macro with some other people, so basically I've just sent that excel file on to the people that need to use it. Should be fine and in most cases it is, however there is one user who although they can open the file, can't seem to get the macro to run properly.

It seems to get a small way through the macro but then stop with no error messages or any sign that it hasn't completed properly.

I have checked Macro Security level and that is the same as mine, Tools - Add-Ins is the same, In Visual Basic, Tools - References is the same as mine. It is the same Operating system and the same version of Excel.

I have even signed into this person's computer as myself (it's a big company network thing) and tried to run the macro and it works fine, so there is nothing wrong with the hardware.

I've googled and searched and tried everything I can think of but I'm no closer to solving this problem, so if anyone has read through this wall of text and can come up with a possible solution, that would be greatly appreciated to save me from tearing ALL my hair out!

Thanks very much for your time.


I need help desparately. Last night I spent several hours working on an
Excel 2002 spreadsheet and it has disappeared.

I saved it routinely as I was making many changes updates etc. I opened
original file --stuff.xls --- made changes and saved it as again as
stuff.xls. I have done this several times before.

Today, when I go to open stuff.xls, it is nowhere to be found!!!!! I have
done a search, for any file modified since Thurs and nothing has appeared. I
have checked the recycle bin, this did not go into auto recover nothing of
the like. I am just traumatized, because I have NEVER had anything like this
happen before.

I even thought perhaps I didn't save it as often as I thought, (I know that
I did) but I remembered that I saved it at least once and I can't even find
an Excel file that has been modified since Thurs!!!

Does anyone have any idea what could have happened to this file?? BTW, I
have prayed, rebooted, run spybot, called witch doctor, all with no success.

I have never had a file to just disappear with no trace. Any help would be
greatly appreciated.

Thanks in advance---