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.
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?
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.
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?
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.
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.
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
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?
The dialog box comes up with file name formatted correctly and shows .xls as file type but when you choose save the dialog box disappears as if the file was saved but the file never gets saved.
I have a form which has col A Surname Col D Post Code Col E The month (for example)
through a formular this gives the unic customer reference number surnamepostcodemonth given in col G but when I save the document I have to copy and paste the value in G1 for the file name is there away of pressing Save As and it auto fills the file name?
the actual value in the field G1 is ='Raw Data'!D2&" "&'Raw Data'!E2&" "&TEXT(('Raw Data'!F2),"MMMYY")&" "&'Raw Data'!G2 I obviously want the result "Smith TW6 Jul09" to be the file name when I save it..
I try to save another workbook, say Book_B, inside a BeforeSave routine for say Workbook A. It works fine when triggered by the CommandBar for saving A, but skips the "Save" when triggered by saving A from another workbook, say Book_1. Is this my fault, or some inherent limitation in Excel, and if so:
Following is some simplified code to illustrate the situation:
This is the code for Book_A:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim fName As String fName = "Book_C" For Each w In Workbooks If w. Name = "Book_B.xls" Then w.SaveAs Filename:=fName Next w End Sub
This is the code for Book_1:
Sub Book_A_saving() For Each w In Workbooks If w.Name = "Book_A.xls" Then w.Save Next w End Sub
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?
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"?
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.
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.
if someone would review the code below and tell me why it does not cycle an autosave more than once. It is supposed to autosave automatically every five minutes but for some reason beyond me it does not. I did not author this but picked it up in a different forum.
Option Explicit Public vartimer As Variant Const TimeOut = 1 'set in minutes to whatever amount of time is desired Sub Timer() vartimer = Format(Now + TimeSerial(0, TimeOut, 0), "hh:mm:ss") If vartimer = "" Then Exit Sub Application .OnTime TimeValue(vartimer), "SaveOpenWorkbooks" End Sub
I want to be able to have the Save As dialog box come up, but with a File Name already chosen, so that the user can save it as this File Name in any location he/she wishes. I can get the Save As dialog box coming up with my code, but I don't know how to automatically enter the File Name
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.
I'm on a system using Windows XP with several user accounts. It's possible to share a document by putting it in a directory called, unsurprisingly, "Shared files' or somesuch. Anyway, I wish to put one of my Excel spreadsheets into this directory so that other users can see it. The workbook itself has about 10 worksheets and I want other people to be able to edit only one of them. So I used the "Protect Worksheet" option within Excel to protect those worksheets I don't want edited. However, I notice that it's still possible to delete a worksheet even though its protected. Is there a way to prevent from deleting worksheets?
i have a workbook with 6 worksheets in it. the user can put data into any cell in any worksheet. what i want to be able to do is prevent the user from accidently deleting any of the worksheets
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.)
I am needing to disable 5 worksheets from being calculated under the 'AutoCalculate' default option of excel. Currently, I set all 5 sheets' .enablecalculation property to false (see code example below), then save the workbook. This should prevent these 5 sheets from being calculated when the workbook is opened again - however these sheets are still being calculated with excels default 'Auto Calculate' setting once the workbook is reopened.
My issue: While having 'Auto Calculate' enabled, how is a sheet disabled from being calculated upon opening?
Current method of disabling worksheet - does not work tho.
I am trying to e-mail a portion of a spreadsheet that I have. My approach is the following: I save a copy in a temporary folder, open that copy, remove all the sheets that I don't need to send out, save it (same name as the e-mail subject), and then e-mail as an attachment.
The current approach does not remove the macros and references though. I use the following to remove the macros and references:
I have this code (got it from here: [url] that saves worksheets as separate workbooks. It works perfectly fine for that but I would like to save each worksheet as a text file (tab delimited). I tried changing FileFormat:=xlNormal to FileFormat:=xlFile but a debug error occurs.
Sub MakeMultipleXLSfromWB() 'Split worksheets in current workbook into ' many separate workbooks D.McRitchie, 2004-06-12 'Close each module AND the VBE before running to save time ' provides a means of seeing how big sheets really are 'Hyperlinks and formulas pointing to other worksheets within ' the original workbook will usually be unuseable in the new workbooks. Dim CurWkbook As Workbook Dim wkSheet As Worksheet Dim newWkbook As Workbook Dim wkSheetName As String Dim shtcnt(3) As Long
