VBA That Disables Save Function
Nov 10, 2011
I have a template workbook which I need to prevent anyone making changes and overwriting the original.
I used the following code;
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
Cancel = True
MsgBox "You cannot save this workbook. Use Save As"
End If
End Sub
This works fine apart from I am then unable to save these changes myself so when the workbook is next opened the code has not been saved.
View 2 Replies
ADVERTISEMENT
Nov 2, 2009
Each of the worksheets in my model use A1 as a control cell for any errors and inconsistencies. My aim is to disable save and close commands in case A1 is not equal to 0 in any of the worksheets.
The code I currently use for that purpose is as follows.
View 2 Replies
View Related
Jul 15, 2014
I have certain cells in my spreadsheet that contain formulas linking them with other sheets within my workbook. I have grouped sets of rows in my first 2 sheets (to show just headings and to expand to subheadings & details). When I try protect the cells with formulas on the sheet then it blocks me from expanding or contracting the row groups.
Is there an easier way to stop someone editing or deleting the data in my cells? There are about 2 dozen cells with formulas in my first sheet that need protecting - unfortunately not in any particular order...
View 8 Replies
View Related
Sep 28, 2009
Sub ExcelOptionDialogSendkeys()
Application.SendKeys "{ESC 5}%TO"
End Sub
But if you happen to want to assign it to a Ribbon button click...
Sub rxExcelOptionsDialog(control As IRibbonControl)
Application.SendKeys "{ESC 5}%TO"
End Sub
the sendkeys method just doesn't seem to work under any circumstance.
View 9 Replies
View Related
Jul 4, 2007
I have a user who wanted a spreadsheet to highlight cells containing formulas whenever the formula was changed. (They want the ability to make changes, but want those changes highlighted. They do not want to protect cells with formulas.) I tried to accomplish this with Conditional Formatting but could not figure out a way to do it, so I turned to VBA. I wrote the following routine that is called on the Worksheet_change event:
Sub CheckFormulas(Target As Range, FormulaRange As String)
Dim CurCell As Range
For Each CurCell In Range(FormulaRange)
If Target.Address = CurCell.Address Then
Target.Interior.ColorIndex = 3
End If
Next CurCell
End Sub...................
View 2 Replies
View Related
May 10, 2008
I am using Reafidy nice bit of code to hide worksheets if the user disables macros. The problem, though, is that it doesn't work for Chart Worksheets. How should I adjust this to accommodate charts?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
bIsClosing = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wsArray() As Variant
Dim iCnt As Integer
Application. ScreenUpdating = 0
Splash.Visible = True
View 4 Replies
View Related
Mar 20, 2013
I've got a workbook where I need to protect certain cells by locking them.
The trouble is when I do this I lose the ability to apply formatting and to insert symbols in other cells?
I've checked the obvious options for when you're locking cells but nothing seems to be stopping this there.
Is there a reason it disables these options, a work around or am I just being dumb?
I'm using Excel 2007 by the way.
View 2 Replies
View Related
Jul 23, 2012
I currently have a macro which does data mining and saves the workbook in the end. I intend to disable to the save function of the workbook and force the user to use the macro everytime the workbook needs to be saved. this is what i have so far but it does not seem to work. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim NoSave
NoSave = MsgBox("Changes have to be submitted before the workbook can be saved, Proceed and submit ?", vbYesNo, "Continue?")
[Code]....
View 3 Replies
View Related
May 28, 2009
Is there something I can program into my form so that when a record is added and the 'add' button is clicked, it not only adds the record to the work sheet but saves the workbook as well.
View 2 Replies
View Related
Apr 1, 2009
I have some formulas in my workbook that use the weeknum function from the analysis toolpak. I have the analysis toolpak installed, yet every time I save the file and reopen it, the formulas with weeknum are gone, with #N/A in its place as the formula. Sometimes the results of the formula from the last time remain (the cells contain the number of the week, but without the underlying formula), sometimes not. All the other formulas are fine. The formulas are simple, like =WEEKNUM(C2)
I have been trying to save as a 97-2003 .xls file. Today I'm trying to save as .xlsx, but I have to rebuild the formulas first, so I don't know how that's going to turn out.
View 5 Replies
View Related
Oct 19, 2008
I used this forum to successfully prevent a user from saving a workbook without entering data in a certain cell. However, I now have a merged cell that I must refer to when writing my "before save" code and don't know how to refer to that cell. Below is what I have: One workbook with nine worksheets. The merged cells I want to refer to are H11 and I11 in sheet seven. User RYLO posted the below code that worked for me but I don't know how to alter it to refer to a merged cell.
View 4 Replies
View Related
Jun 4, 2009
I'm working on the following
Workbooks.Open Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-" & Ucase(Format(DateAdd("y", 0, Date)), "YYYY-MM-DD")&".XLS"
ChDir "D:CommondataIBMmain"
ActiveWorkbook.SaveAs Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-NAFTA.XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Date: 2009-06-03
What I'm trying too do is open a file, make an exact copy and save it under a new file name.
My problem is in the date formula the day is not always the same. In the sample case it's 03 at other times the day will change.
is there a way too get this too work irregardles of what the day might be?
View 9 Replies
View Related
Mar 11, 2014
am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
View 6 Replies
View Related
Dec 3, 2012
I have an expense report, which was originally done in Excel 2003. It still prompts users to open as read only however if they select no, they are not prompted for the password, and it has allowed them to save, so when the next person opens it, they have the previous person's report instead of the clean workbook. I have tried everything to put a password onto it. I know this is really basic stuff, but maybe I am missing a step? I want the end user to be able to make all the changes they want in read only mode and then do a save as, but if the select No when prompted "Open as Read-only?" they should have to put a password in.
View 4 Replies
View Related
Aug 21, 2012
What I am trying to do is that I have an excel file with macros and it is a read-only file. In order for the user to save, I want them to only be able to save as a .xlsx file as it disables all macros. If for whatever reason, the user wants to save the file as another .xlsm file, they should be allowed but before they save, a "are you sure you want to save as .xlsm?" message should pop up.
All the options in the save as box should still be available in case they want to save in that particular format. Just that the .xlsx should be the default.
View 2 Replies
View Related
Sep 6, 2012
I'm trying to make a macro check if a file has been saved (ever). If so I want the macro to do a regular save (with already esatablished filename and location) before it proceeds with the rest of the macro. If the file hasnt been saved (if it runs from a new workbook) then I want it to pop up the save.as dialog, so that the user can choose the name and location of the file before the macro continues .
The macro itself is saved in personal.xlsx.
View 1 Replies
View Related
May 10, 2008
I would like to add some icons on the left side of excel open file pane to faciliate my work. Because i need to load some files under the same folder many times a day. Does anybody know how to do that? I've seen people has more icons on the pane before. The defaut setting has only 'History', 'My Documents', 'Favorites', 'Desktop' and ' My nutwork places' on it.
View 2 Replies
View Related
Jun 22, 2014
I have a UserForm in which i have inserted a CommandButton. I also have a Table on a Sheet in Excel that i would like to save as PDF.
When i click the CommandButton on the UserForm i would like the SaveAs screen to come up and have the Table (or Used Range in the Excel Sheet) as the selection to be Saved as .PDF format.
View 3 Replies
View Related
Jul 31, 2009
I have figured out how to save the workbook using a variable to saveas but I would like to create a new folder to place the new workbook into. I have tried a few things a am not having any luck. I recorded a macro and the code it produced is as follows.
View 6 Replies
View Related
Jan 17, 2010
I have a macro that copy one sheet of the Active workbook and sends it via email.
I need to add a code in this sheet so when one opens it from the email, with a command button to be able to save the file to specific, fixed folder on the local network with it’s original file name.
View 10 Replies
View Related
Mar 27, 2008
I have a sheet which I would like to back up online every time it is saved.
I have a sub, OnlineFile(), which I have used many times without any problems, which creates a .BAT file to use ftp to upload files onto our server. As arguments, it takes the local file path that you want to upload (or download to), the name of the file as you would like it stored, the online file path, and whether you want to upload the file onto the server, or download it off the server.
It should be very easy to combine the two: what I tried was:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "" & "Rubbish.xls"
Call OnlineFile(ThisWorkbook.Path & "" & "Rubbish.xls", "Rubbish.xls", "wwwrootexcelfilesMasters", "Upload")
Kill ThisWorkbook.Path & "" & "Rubbish.xls"
End Sub
Every time I ran this, the file got uploaded correctly, but Excel had a series of problems. Often the code ran fine the first time it was run, but crashed the second time round.
After a fair bit of troubleshooting, I found that the problem came about when the file was trying to save itself - i.e. after the end of the sub. If you pause any macros from running, it still saved fine, but when the code ran, it found errors, froze or closed excel.
My thought at this point was that the code was fragmented or something. I made a new sheet, and retyped just the relevant bits of code (i.e. the BeforeSave sub, the OnlineFile sub and created the form called by OnlineFile). The problem persisted.
I've come up with a workaround for this now (which uses the same OnlineFile sub & form, and works fine), but it's driving me crazy - why does this cause excel to die? Particularly on the second time through? The code seems far too simple to cause such problems - is there a bug in Excel perhaps (I'm using Excel 2000 on Windows XP).
View 9 Replies
View Related
Sep 5, 2012
I am trying to disable the save and save as function using a macro, i know that this in its self is quite simple but i am making VBA create a new workbook and i want this (nameless) workbook to have the save feature disabled.
View 1 Replies
View Related
Oct 14, 2013
when i try to save data in other sheet. it is save but, if middle name is null in last record. then after i save other record the data is not save in same row.
i also want "sr.no" come automatically form data..
for.ex.
if in sheet2 last "sr.no" is "7" then it automatically display "sr.no" "8" in sheet1
View 8 Replies
View Related
Jan 26, 2007
I have a spreadsheet with 32 worksheet, I want to write a macro to "save the work, save as.. in a specific location and with a specific name and then reseting all data in specific cells.
View 14 Replies
View Related
Oct 5, 2011
I need my spreadsheet to require that the user save it by clicking a macro button, and not the Save or SaveAs options in the file menu. I have found out how to disable saving entirely:
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
If SaveAsUI Then SaveAsUI = False
End Sub
But this of course prevents my macro button from saving too!
Is there any way to allow VBA code to save the spreadsheet while simultaneously preventing the user from saving through the menu?
View 2 Replies
View Related
Oct 10, 2011
I have written the following simple macro to import some data into a worksheet and then prompt the user to save the file in Excel 2003 format (the system to which we will upload this data does not accept formats later than 2003). The template is in "*.xlsm" format.
The code executes without error, but when the user hits the "Save" button in the "Save As" dialog box, nothing happens. The "Save As" box closes, but no file is saved.
Code:
Private Sub cmdImportData_Click()
Dim sFName As String
'On Error Resume Next
PrepData
CopyData
FormatColumns
'prompt the user to save the file in "*.xls" format
sFName = Application.GetSaveAsFilename("upload", "Excel files (*.xls), *.xls")
End Sub
View 3 Replies
View Related
Nov 16, 2006
I have a file that I have created that the users will use as a master template to create data that I need. Once they have finished, I want the file to define what the filename should be when they click save.
At the same time, I want to disable "Save As..." as some users will just use that and then will use a file name that another macro (that I will use) won't recognise.
View 7 Replies
View Related
Feb 26, 2007
i have a master workbook. it gets used daily as a template and saved as another file name. the file name is generated by 5 cells that each contain different data (date, street, town, state). is it possible when i hit the save or save as button/icon, for a file name to be automatically generated from the cells i have data in??
PostPosted: Wed Oct 15, 2003 5:12 pm Post subject: Re: change default saveas name for new file w/o saving
Okay, so you want to create a workbook via code, prompt the user for a SaveAs name, then save this workbook?
Sub CreateAndSave()
Dim wb As Workbook
Dim SaveFileName As Variant
Set wb = Workbooks.Add
ChDrive "C"
ChDir "C:VBA Code"
SaveFileName = Application.GetSaveAsFilename("It is a new file.xls", _
"Microsoft Excel Workbook (*.xls),*.xls")
On Error Resume Next
If SaveFileName False Then wb.SaveAs FileName:=SaveFileName........................
View 9 Replies
View Related
Apr 5, 2007
I have a update/Save button on a spreadsheet. So when clicked it updates another sheet and saves the workbook.
how can i have it run the macro when they don't click the button and do
File -> Save.
View 9 Replies
View Related
Dec 13, 2007
I use to send reports to my seniors on daily basis.
I want these reports to be with me and they can just open and check the reports, when I send them. If they try to save file through File-Save or File-Save As, they should not get these options at all in the File Menu.
View 9 Replies
View Related
