Prevent Saving And Allow Only Through Macro
Mar 14, 2007
Prevent saving and allow only through Macro
I am contracting an excel template and need to stop users saving the file using “Save” or “Save As”. I am able to do this by using the private sub below:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub
I also understand that by switching to “Designer” mode you can save the file and once re opened designer mode will be switched off. What I need is to allow saving using a macro, is there a code that I can insert in the macro to switch designer mode?
View 9 Replies
ADVERTISEMENT
Nov 9, 2007
I use this function to prevent that the user is saving the excel file manually:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub
But now I also cant save the file with a macro!
ActiveWorkbook.SaveAs pathResult & "Files" & name & No & ".xls"
How can I allow the macro to save the file?
View 9 Replies
View Related
Jul 5, 2006
I'm wondering if it is possible to prevent a user saving a workbook. If opened as a read-only, excel throws up an option to save with an alternative name. I'm wondering if I can put some code in the Workbook_BeforeSave event that prevents saving of the document unless the application.username is myself.
View 2 Replies
View Related
Jun 23, 2007
I have macros that pulls data from an SQL db. Users need the ability to modify the data on the worksheets BUT they can NOT have the ability to save the document.
Is there a way to disable the "save" function from the FILE drop-down box?
View 9 Replies
View Related
Jan 19, 2008
In my program I have prevented the user from saving the Workbook with the following code
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim p As String
Cancel = True
Do While Cancel = True
p = InputBox("Enter password to save file:", "Password Required To Save", "")
If p = "Enter Password Here" Then
Cancel = False
Else
yn = MsgBox("Wrong password. Try again?", vbYesNo, "Invalid Password")
If yn = vbNo Then
Exit Sub
End If
End If
Loop
End Sub
I also need to prevent the user, when they use the save as, from saving certain worksheets. Ex: There are 6 worksheets in the workbook. I only want them to be able to save the first three. Issues: One of the workbooks that I want to allow them to change has a tab name that changes frequently.
View 2 Replies
View Related
Feb 7, 2014
I want this statement translated into a macro.
Do not save file year.xlms if total of column N in sheetR does not match with total of column O in that sheet. I also want a msgbox if this happens.
View 4 Replies
View Related
Dec 13, 2006
I already have a macro that on opening the workbook checks to see if the workbook has been renamed outside excel in windows and if so closes the workbook. I need a macro to intercept the save as command and prevent the user from saving the workbook as another name. I would however still like the user to be able to save. I would also like them to be able to save the workbook in another directory but only with the same workbook name.
View 4 Replies
View Related
Aug 14, 2014
Adding some additional code to prevent someone from saving the workbook under a different name. Currently if someone tried to enter data after the allotted time period, it would allow them to in any "unlocked" cells. When exiting or trying to save the file with this new data, they will get a message stating something to the effect that this is a read only file would you like to save as another name, which would allow the user to circumvent what I'm trying to accomplish. The end result would be they'd need a new spreadsheet.
[Code].....
View 2 Replies
View Related
Feb 9, 2010
I have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.
What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)
View 2 Replies
View Related
Jan 30, 2003
I have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?
View 6 Replies
View Related
Feb 21, 2002
Is there any way to write a macro which can prevent computer illiterate users from saving their files EVERYWHERE??
View 9 Replies
View Related
Aug 8, 2007
I have a macro, that when running needs to display certain sheets that should be hidden again when the macro is done. I solve this by using the code
Sheets("Sheet1").Visible = True
'macro code
Sheets("Sheet1").Visible = xlVeryHidden
But what if the macro fails? Or if the user ends it by pressing Esc? Then he will be able to see the hidden sheet. Is there something I can write in the code that makes the macro hide the sheet, then show the error message?
View 5 Replies
View Related
Nov 14, 2008
I need a line of code so that when I hit commandbutton2, the Private Sub Worksheet_Change(ByVal Target As Range) event macro on the same page DOESN'T run. The button clears certain lines, and when it runs the change event it ends up in an error, and I don't need it to run when hitting the commandbutton.
View 6 Replies
View Related
Feb 9, 2009
Is there a way to prevent access to the code of a macro? I am interested in sending out some macro's, but would prefer that the user only be able to run them, and not be able to access the code via the edit button. Curiously, there have been occasion's where I wanted to edit my own work, but the edit button did not function, so I had to exit and start over to proceed. From this I'm guessing there must be a way.
View 2 Replies
View Related
Jan 11, 2007
I would like to write a macro that performs the following function:
I have a value in cell A1
In cell A2, I have the formula "=A1"
I want to make it so that cell A2 can never be directly changed; the only way to change the value in A2 is by changing A1. A2 should always equal A1.
I want a message box to pop up and alert the user whenever they attempt to modify A2, instructing them that if they wish to change A2, they have to change A1.
I had written this macro, and it kinda does the trick:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("a2") Range("a1") Then
MsgBox "text goes here"
Range("a2").Value = Range("a1")
End If
End Sub
When I attempt to change cell A2, I get the MsgBox popup and it works how I want it to. However, when I alter cell A1, I get the same popup before cell A2 updates (to be precise, the popup occurs after I click another cell). I would like this to be immediate and without a MsgBox.
View 9 Replies
View Related
Apr 29, 2008
I have a macro that is run by clicking on a macro button. The macro copies the data from Sheet1 and pastes it in another sheet, Sheet2. I added some conditional formatting that colors certain cells red if others are blank on Sheet1. I would like to add some code to my macro that will not allow it to copy and paste from sheet1 to sheet2 if there are any red cells in the range.
View 9 Replies
View Related
Dec 8, 2008
How do I save the spreadsheet with the current name of the spreadsheet in a macro?
Example:
I want to save the spreadsheet to a network drive using the current name of the spreadsheet (it changes every other day). But I dont know to put the name in the SAVE statement. Here is what I have now:
View 4 Replies
View Related
Dec 24, 2007
I Want A Code That Will Save My Workbook To A Specific File (different Form The One It Is Currently In) Using The Name That I Have Typed In Cell D13 In The Workbook
View 9 Replies
View Related
Sep 4, 2009
I have this analyze that is runned by a macro in one workbook, and it starts a analyzing-process in another workbook. The data is picked up in the no2 book, and returned to the first book. It is analyzing lots of workbooks, sometimes up to 1000 workbooks, it means that no2 workbook gets a new name and then saved.
I have once heard that the process could be way faster if the workbooks where the analyzes is processed through not were saved, and I actually dont need the books as long as I got the data into my first workbook.
But Im not sure what in the macro that makes it save the no2 workbook, but I would really like to speed up this process. As it is now I have to start the analyze before I go to bed, and the hopefully it's done when I wake up next morning.
View 9 Replies
View Related
Aug 6, 2008
I have an add-in that I'm passing around to my users, and though it has 8 or 9 subs, it only has 2 that they need to see. However, I'd rather not relegate all of the code to a single module, as there is quite a bit of it. I'm aware that I could change all the subs to functions, but they would still appear in the UDF list, and this would also be confusing to the individual responsible for the upkeep of these macros (and I'd have to rework the code a little bit).
I'm just asking if there is any direct way to do this, before I go to the trouble of making a lesser workaround. Google seems to think no, but "don't show macros in list" isn't a very solid search.
View 9 Replies
View Related
Aug 5, 2009
I'm using the following code to delete columns:
Dim rng As Range
Dim i As Integer, counter As Integer
Set rng = Range("1:1")
i = 1
For counter = 1 To rng.Columns.Count
'If cell i in the range contains an "x",
'delete the column
If rng.Cells(i) = "x" Then
rng.Cells(i).EntireColumn.Delete
Else
i = i + 1
End If
Next
My problem is that I have cells in other worksheets linked to the worksheet that is running this macro and everytime I run it, I get a handful of "#REF" errors. I think this problem might be solved if I could simply delete the contents of the column rather than deleting the entire column. How can I modify my code to do this?
View 4 Replies
View Related
Nov 23, 2009
I'd like to know if there is a change event that only occurs when a target cell is changed by the user, but does not occur when i'ts changed by a macro. nfortunately, the Worksheet_Change event occurs in both cases.
View 2 Replies
View Related
Sep 8, 2006
I have to issue template workbooks to people for budgeting purposes.
Within the workbooks are various numbers of worksheets pre -formatted and ready for these people to enter data.
Some of the data in the worksheets is important to them as individuals but not to me so I have a series of macros that lift the information from the worksheets and put it into a worksheet more specific to me.
Because the users are on the whole not that good with Excel I have put in easy to use look up tables and various proctections to stop them adding or deleting rows or columns as this plays havoc with my macros.
There is one thing I have "so far" been unable to do and I wondered if anyone could help.
Is there a macro that I could put somewhere in the workbook which would detect when someone tried to cut and paste and would either put up a message box or stop them doing this?
The reason being they are cutting from one row to another and this is messing up the calculations which are protected.
Is there also a way to stop them changing the name on the sheet tab in the smae way?
View 9 Replies
View Related
Feb 6, 2008
i have a worksheet named for example 'allocation 1' this is a master document and is opened and modified and 'saved as' under a customer name. This then stops a few important macros working properly because they refer to the original title and not the new saved title. Is there any code that will let the macro recognise any new title it is saved under?
View 10 Replies
View Related
Nov 15, 2008
Thanks for all the help so far with this issue. I seem to be getting errors after running a macro a second time. The name of the cell range is changing. I have attached the spreadsheet and I will try to be specific as possible.
Goal 1: I need to input monthly data into the Sales tab and then save it as a CSV file.
Goal 2: Re-open original xls file that has data then Run macro to create reports.
Goal 3: Save Original xls file as a clean sheet to use again next month.
Process:
1 - Insert Data into Sales tab
2 - File/Save As a CSV (Now the CSV file is open)
3 - Close CSV file and re-open the original xls file
4 - Run Macro to create reports
5 - Print Reports
6 - Manually delete current data on all tabs so I have a clean sheet for next month
7 - Save and Close
Problem:
When I open the xls file (next month) to input new data and run the reports I get a Run Time Error '1004" The Pivot Table field Name is Not Valid
I found that the Named Range of cells changed from the original:
View 8 Replies
View Related
Nov 28, 2008
Need the VBA code to close a workbook, and not save it? I need it to open a workbook, run a macro, and close without saving. The code I have thus far is:
View 3 Replies
View Related
Jan 6, 2010
Using excel 2003, I have a ss that uses filters and sorting. I can get to the data I want but must reopen the workbook if I want to start over and extract different information. I tried installing this VBA:
Sub CloseMe()
Application.OnTime Now + TimeValue("00:00:10"), "OpenMe"
ThisWorkbook.Close False
End Sub
Sub OpenMe()
MsgBox "I'm Back!"
End Sub
This is my first time at inserting VBA. Usually I just use keystroke macros. I have set my Excel VB Book options to: Option Explicit double clicked the sheet I was using and under general inserted the code, closed Ctrl+Q, saved workbook. When I reopened and tried to use it all I get is “I'm Back!” nothing else happens. One time it actually ran and reopened but it appeared that the workbook was saved before closing because it was still sorted. What I need is it to completely unsorted as it is when I first open it so I can start over.
View 4 Replies
View Related
Jan 26, 2010
I currently have the following ...
View 8 Replies
View Related
Jan 10, 2012
I have a macro that saves the workbook after it has finsihed the rest of its formatting. I am finding it is saving in the wrong folder. I looked at my code and it is clearly states 'my documents' where I want it to save. Yet it keeps saving on a random folder on a shared drive.
View 8 Replies
View Related
Apr 26, 2012
I'm having some trouble creating a macro that will save the worksheet under the name of the cell ("G4") that needs to change to the next ascending number (i.e. 101101, 101102, 101103, etc). Have a macro but wont function after saving and never saves as the new cell #.
View 9 Replies
View Related
