Prevent Saving Certain Worksheets On Save As

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


ADVERTISEMENT

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 View Related

Prevent Saving Function

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

Prevent Saving A Workbook

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

Prevent Users From Saving The Document

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

Prevent Saving Of Excel If Certain Condition Not Fulfill?

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

Stop Saving File As Another Name. Prevent SaveAs

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

Adding Some Additional Code To Prevent Someone From Saving Workbook?

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

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 View Related

Prevent FILE SAVE

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

Macro To Prevent 'save-as'

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

How To Prevent SAVE In Word

Jan 29, 2009

how to prevent a user saving a Word Document.

View 9 Replies View Related

Save As Sub Not Saving

Dec 31, 2009

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.

View 3 Replies View Related

Saving Or Save As

Aug 4, 2009

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..

View 9 Replies View Related

Saving In Before Save

Apr 4, 2007

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

View 7 Replies View Related

Save Today() As Value To Prevent Changes On Reopen

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

Prevent Save Option If Condition Not Met

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

Prevent Save As Version Message

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

Prevent Workbook Save But Allow SaveAs

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

Prevent Save Until Save As Used

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

Auto-Save Code Not Saving

Nov 14, 2006

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

View 9 Replies View Related

Saving File With Save As Dialog Box

Dec 14, 2006

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

View 4 Replies View Related

Prevent Hidden Columns From Showing Up When I Save It As A Txt File.

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

Prevent Others From Deleting Worksheets

Jun 6, 2006

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?

View 2 Replies View Related

Prevent Some Worksheets From Deletion

Jan 5, 2007

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

View 8 Replies View Related

Prevent The User From Saving The Data Input From The User Form If Any Of Those Three Fields Is Left Blank

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

Prevent Worksheets From Auto Calculating

Aug 12, 2008

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.

worksheets("Mysheet1").EnableCalculation = False

View 9 Replies View Related

Saving Subset Of Worksheets

Nov 4, 2008

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:

View 3 Replies View Related

Saving Worksheets As Text

Jul 22, 2007

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

View 9 Replies View Related

Excel 2002 :: Does Saving File As Unicode Save It In UTF-8 Or UTF-16 Format

Jan 14, 2011

what format is used when you save an excel file as "unicode." I am using excel 2002 on XP.

I have been asked to provide a UTF-8 formatted unicode file for use by another program but I am not sure which formatting excel uses.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved