Prevent To Only Save On Specified Workbook With VBA
Jan 28, 2013
am trying to create a workbook template which collects data from users with user forms and some macros. Users assign a project name when workbook opens and then they are able to save as the workbook with name of project name. But i don't want them to save on my template mistakenly. How do i prevent this? I tried workbook_before close sub but i can manage that it recommends to save workbook with project name and if you don't want, it still asks you if you want to save workbook before close
View 7 Replies
ADVERTISEMENT
Jun 24, 2008
I've got a workbook that I want to keep intact in a central location on our local network, to make it easier for everybody to access, but don't want people to be able to save changes to the master workbook itself. I'd like to be able to disable the 'Save' feature while still allowing 'SaveAs'. The following code (installed in ThisWorkbook) results in the OPPOSITE of what I'm trying to accomplish (i.e., it allows 'Save' but disables 'SaveAs'):
Private Sub Workbook_BeforeSave(ByVal SaveUI As Boolean, Cancel As Boolean)
If SaveUI Then
MsgBox "The 'Save' function for this workbook has " & Chr(10) & "been disabled. Please use 'Save As'.", vbOKOnly + vbInformation, "Save Disabled"
Cancel = True
End If
End Sub
Obviously I'd also like to check the filename they're performing the 'SaveAs' under and disallow it if it matches the master filename.
I've thought about making the workbook into a template, which would sort of accomplish the same thing, but it would be much easier to just keep it as a workbook.
View 9 Replies
View Related
Nov 28, 2007
I want to make sure that the user of a file only saves from a button on a particular sheet. Can I ensure that if the user goes File Save at any time that they cannot use it?
View 9 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
Jan 29, 2009
how to prevent a user saving a Word Document.
View 9 Replies
View Related
Jul 30, 2008
I have a spreadsheet that containt a conditional formula to place the current date aka Today() into a cell based on if an entry in the previous cell.
I put in this formula:
=IF(I131>="1",TODAY()," ")
My boss told me to update to this one: (same result)
=IF(I130>="1",DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))," ")
BOTH will change the saved sheet data if we open it tomorrow (due to using Today()). We want to retain the date that it was entered on (which will always be the date he puts entry into sheet).
Is there a way to save the sheet and update the code for Today() to the date value so it will appear as a date when it's opened in the future?
View 9 Replies
View Related
Sep 28, 2006
I have a list dropdown from cells A2 to A10. If only when a user selects "orange", he has to key in some description in column B.
Eg if he selects orange in A2, he has to key in some description in B2.
Is it possible to set some formula or macro which prevents user to save this file if he leave blank in B2 or B10, depending on which row he has select "orange"?
View 8 Replies
View Related
Oct 17, 2006
I have an extensive application which I wrote using Excel 2002 (XP - Version 10). However it will likely be used on anything ranging from 2003 - 1997. When I tested it using Excel 97 it gave me the traditional [annoying] message asking if I was sure I wanted to save it under an earlier version than which it was created.
I want to get rid of this message (a lot of my users aren't the most computer literate and the more annoying messages I can avoid the better!)
I tried using:
Application.DisplayAlerts = False
but it doesn't affect this particular message. I need to know how to keep this message from popping up. Please Help.
Under 97 (NOT on my 2002) it also gives a "Do you want to save the changes" message upon closing even though the "DisplayAlerts" is set to false. I'm not sure why this is still coming up.
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
Mar 21, 2008
I am trying to hide/grey SAVE option from the menu bar and reset menu back to normal after Save AS has been used.
I was able to do disable SAVE using the following code.
Application. CommandBars("worksheet menu bar").Controls("File").Controls(4).Visible = False
But
the SAVE icon on the Standard toolbar still exist. Is there any way I can disable it and also prevent CTRL. S being used.
Second part of the question.
Once I use Save as I would like menu to be reset back to normal.
View 5 Replies
View Related
Mar 20, 2009
When I am saving my spreadsheet as a text file, I have 1 column with formulas that I would like to not show up in my text file. I have tried hiding the column, and have Googled for awhile now.
View 2 Replies
View Related
Aug 29, 2006
how to prevent a workbook from opening if another excel document is open and if a visual basic window is opened.
View 8 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
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
Apr 2, 2009
I display a message box in Workbook before Close event.
If user click yes I shouldnt close the workbook if NO i should close the workbook.
In the Yes part, How can I stop excel from closing the workbook.
If i dont write any ocde it automatically closes in the event.
View 2 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 4, 2007
I want to prevent any Excel Workbook Theft through VBA
1. check the existence of any file in Windows directory ( which we do put for this security)
2. If it doesnt exist "pireted copy? close!" error message may apear
I know it is not fool proof. but it can gives 40% protection
View 5 Replies
View Related
Nov 20, 2007
I need to alter the Close command on the title bar (the 'X' on the top right corner on the blue bar of excel)
Is there any way to alter the close button on the title bar to have it call a sub I created instead of actually closing excel?
View 6 Replies
View Related
Feb 12, 2009
How can I prevent a user from opening another workbook in an Excel session "from the outside"?
I'm fairly new to VB. I'm developing (in VB 2003) a simple but SECURE Excel environment which will allow a user to update a hidden Master_Records workbook. I need to keep the user's Excel session secure...for example, I've disabled all Excel Toolbars and Command Buttons, effectively preventing the user from doing anything except filling in some cell values and clicking on a few custom buttons in the worksheet. But how can I prevent the user from opening another workbook into the active session from his desktop and introducing some malicious code into the session via that route?
One respondent in another forum (the only one, in fact) suggested that I look into "instantiating workbook level events" so that I can detect when other workbooks are open. I'm not sure what that means, is there someone here who could give me some guidance into that solution?
View 13 Replies
View Related
Sep 23, 2009
I'm trying to prevent a user from closing a perticular workbook.
Here is some important factors.
Workbook(A) = "LA-01-04-10" This is only one of 52 other but only one is open at a time.
Workbook(B) = "LA.xls" Always the same name and is always opened when one of the workbooks(A) above it opened.
When workbook (A) is opened it will then open another workbook (B) at that time the window is hidden workbook(B) So the user can only see workbook (A) I have a button on workbook(A) that unhides the window to show workbook(B) After the user completes his work in Workbook (B) they need to click another button that sorts the list and returns them to workbook(A) (Which also hides the window for workbook(B).
The problem is I have users that are trying to close workbook(B) instead of pressing the sort list button. So what I want is to prevent the user from closing workbook(B) by displaying a msgbox saying they need to press the button.
Ive tried several approches to this but I'm still having problems. The below script works if workbook(B) is active or shown. The message is displayed and the workbook will not close. However if the user is in workbook (A) and trys to close the message is displayed and both workbooks close like they are suppose to. But why is the message displayed?
One other note I should say is that not only does workbook(A)Open workbook (B) but it also closes it. Which may be why I'm having problems?
I have also included both workbooks so you can see all the code.
I have this code in Thisworkbook of workbook(B)
View 10 Replies
View Related
Dec 1, 2011
I have a workbook that contains macros. When the workbook is protected, the macro returns an error so I have unprotected the workbook but, if someone protects the workbook and saves it, it returns an error when closing and even if they choose cancel, it still screws up the macro and prevents it from working upon reopen.
Is there a way to prevent someone from protecting the workbook in vb code?
View 7 Replies
View Related
Jan 16, 2007
I have this code in my ThisWorkbook module:
Private Sub Workbook_Open()
With Application
.Calculation = xlManual
End With
End Sub
I want it to open the workbook without going through calculations (which take a long time)...
It sets to Manual, but still re-calcs upon opening...
View 9 Replies
View Related
Aug 30, 2006
i am using the following code to close my userform and work book,this disables the exit button on the userform but i can still close the workbook by using the exit buttons on the sheet. can i disable the exit buttons on the sheet so the only way to close the workbook from the userform
Private Sub CmbClose_Click()
Dim ans As Variant
ans = MsgBox("This will close XL, save current file?", vbYesNoCancel)
If ans = vbYes Then 'save book and quit XL
ThisWorkbook.Save
Application.Quit
ElseIf ans = vbNo Then ' no save and quit XL
Application.Quit
ElseIf ans = vbCancel Then 'no save, no close , back to userform1
Exit Sub
End If
End Sub
View 7 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
Jun 17, 2014
I have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)
However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.
I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.
For now it is only saving the copy into "My Documents"
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16-02-2009 by ceng
'
Sheets("Bunker ROB").Select
Sheets("Bunker ROB").Copy
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Path & Range("D3"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
[Code] ........
View 2 Replies
View Related
Jun 25, 2014
I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code:
Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
[code]....
View 2 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 15, 2010
I have a small macro that before closing the workbook will check a range ("C4:G24") on Sheet ("Data Checks"). It will then show a msgbox displaying each non "OK" result in the range. I want this to close the workbook if all cells in the range have the value "OK" and cancel the close if the msgbox appears. This is what I have so far...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim r As Range, txt As String
With Sheets("Data Checks")
'for each non "OK" result in range
For Each r In .Range("C4:G24")
If r.Text <> "OK" Then
txt = txt & r.Text & vbLf
End If
Next
End With
If txt <> "OK" Then
MsgBox "Please check:" & vbLf & vbLf & txt, vbExclamation
Cancel = True
End If
End Sub
The problem I have is that although the msgbox displays the correct cell values if they are not "OK", it also appears and cancels the close if all cells in range DO have value "OK"???
View 2 Replies
View Related
Feb 20, 2008
I have a spreadsheet on an intranet drive, and many people need to see it and enter information. Sometimes, we have the file opened on a computer, and the user walks away, and their station is locked. That prevents anyone from updating or using the file! locate a macro that will count how long the file is inactive, and then quit?
I'm not at all comfortable coding in VBA, and this sort of thing is out of my range!
View 4 Replies
View Related
Sep 28, 2012
I have two workbooks open. I need to "Save As" and close workbook 2 with a file name from a cell in Workbook 1. The macro is running from workbook 1.
I'm guessing a change in the last line. I don't know what Dim means either.
Dim FName As String
Dim FPath As String
FPath = "G:"
FName = Sheets("sheet 1").Range("A1").Text
ThisWorkbook.SaveAs Filename:=FPath & "" & FName
View 1 Replies
View Related
