Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Template Excel File - Stop User From Saving

I have a template excel file that is loaded with macros. When the file is opened, a userform opens and the user makes several different selections. Based on the selections, the excel file is populated with information and pictures. I want to make sure that the user does not accidentally save over the template file when he/she is finished with the document.

My original solution was: when the user opened the excel file, it would automatically save under a different name in a temporary location. This would stop the user from being able to overwrite the source file. The users were not happy because the excel file is very large and takes a long time to save.

on another method to stop the user from overwriting the source file? using code? or another method?

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Saving File (template) Automatically
how do i go about saving an Excel sheet (template) automatically, using data stored in the A1 cell of Sheet1 as the file name. i want to use an Excel template to capture data, and then store the data in a central location. to do this i am thinking of getting the template to automatically save to a set location e.g. C/: files. is there a simple way to do this.

View Replies!   View Related
Stop Saving File As Another Name. Prevent SaveAs
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 Replies!   View Related
Code For Saving As A Template
I have some code and then
ChDir "W:MET Logbook"
ActiveWorkbook.SaveAs Filename:= _
"Micro Evaluation " _
& Format(Date, "(mm-dd) ") & Format(Time, " AM/PM") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub
And I want this to save my workbook in the different location as a template. Only it saves the workbook as a regular excel workbook instead. Any help with what I can change to save it as a template?

View Replies!   View Related
Chart Template Saving
I am trying to save a chart as a template but when i apply it to another chart it doesn;t have all the original formatting i did. I doesn't retain the gradient fills on the series, the fact that i've changed one series to line on a col chart??

View Replies!   View Related
Prevent The User From Saving The Data Input From The User Form If Any Of Those Three Fields Is Left Blank
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 Replies!   View Related
Stop Users From Saving A Form
I placed the code below in the ThisWorkbook section of a Service Request macro. The intention was to block users of saving the input they had just entered into the blank template. However, some continue to do it, despite the code. Obviously it is not as tight as I expected.

Does anyone know a better, tighter method, short of killing the offending user (which I am tempted to do, believe me!)?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim LoginName As String
LoginName = Environ("username")
If LoginName "chuckdrago" Then
If SaveAsUI = True Then
Cancel = True
MsgBox "Saving this form is not allowed! Print a Copy"
ThisWorkbook.Saved = True
Cancel = True
MsgBox "Saving this form is not allowed! Print a Copy"
End If
End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

View Replies!   View Related
Saving User Inputted Data From A Worksheet To Another Worksheet And Saving It
I have created a form in a worksheet which I have added questions too and then locked all cells except the ones where I want the answers in.

I have then added a button to the bottom of the sheet called "Print and Save". This work sheet is called "form"

On a second sheet called "database", I have all the titles of the questions running from a -> k and nother else.

What I want to happen is the user input the information on "form" into the boxes available. Once they have completed the questions, I want them to be able to click on "Print and Save" so that firstly the page "form" prints, and then for all of the answers to be saved in "database" below the titles for each question.

I have had a play, but just don't know where to start. Also, once there is a row of questions below the titles, I don't want it to overwrite information already there, it needs to go onto the next empty row available.

Attached is my excel file.

You will see in the code that I have the code for printing.. I just found this on the net and it seems to work fine for me.

View Replies!   View Related
Saving Multiple Workbooks At One Time/file As A Text File
found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?

Sub SaveAsCell()
Dim strName As String

On Error Goto InvalidName
strName = Sheet1. Range("V77")
ActiveWorkbook.SaveAs strName

It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?

Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.

View Replies!   View Related
Stopping User Saving Unless Condition Met.
I am looking to stop the user saving the sheet if certain fields are not complete.

The sheet has headers on line 4 and runs from A4 to M500

In the M cells there is a counta function to check the records are complete.

M4:M500 shows either

Complete - All records comlpete
Incomplete - something missing
Blank - nothing entered yet.

What i want to do is, if one of the cells in M shows incomplete not allow them to save the sheet until its complete.

I tried the folowing (was my attempt to modify another post)

View Replies!   View Related
Preventing A User From Saving A Workbook
if it is possible to prevent a workbook from being saved other than by a macro (So no saves on Ctrl + S or File Saves). Basically, I want to allow users to use a template that I have set up, but I do not want to allow them to be able to save the document.

View Replies!   View Related
Creating A Template File Using VBA
I need a little help creating the macro for a template file which is going to be used for data input and further analysis. I have a set of arrays, which can be altered. The arrays may look like so:

View Replies!   View Related
Embed Template Within File
I have a file with two sheets - Main and Reference. The file has a macro that when used from Main sheet displays some data (images to be specific) from the Reference sheet. The main sheet is of a specific format and has some buttons to call the macro.

I want to have the capability to create any number of main sheets of the same format. I know this can be done by saving the Main sheet as a template and right clicking on a sheet and clicking 'Insert..'. But that would require the template to be stored in the excel start file on the local computer.

I want to know if there is any way the worksheet template can be stored within an excel file, so that if the file is sent to people on a different computer, the user can easily add more worksheets using the template?

View Replies!   View Related
Saving A File With Specific File Name Using Macro
I am trying to save my workbook in a specific directory with a specific filename and to incorporate a date field from within the spreadsheet. I have changed the format of the cell so it does not include / as i know this would not work. Saving it is the easy part but adding in the field from the spreadsheet is where im falling over.

View Replies!   View Related
User Forms Saving Items Entered To Memory
Is there a way to save items into memory to be recalled in userforms.

Let's assume that I have a userform called UserForm1 and three textboxes named TextBox1, TextBox2 and TextBox3.

View Replies!   View Related
User To Select Variable Folder For Saving Files
I would like my macro to prompt the user to select a folder for files to be either opened from or saved to during the running of my macro.

In fact, I need them to select two folders, one for this month and the other for last month.

I think I need to set the two folders as a variable but being fairly new to vba I'm not quite sure how to do this.

View Replies!   View Related
How To Stop The User Selecting Entire Row
I want to stop the user selecting an entire row by clicking on the row header.

How can I do this?

View Replies!   View Related
Stop User Typing In A Combobox
how to write VBA to stop user typing in a combobox but only choose from the dropdown list?

SO that to avoice any device I/O error.

View Replies!   View Related
Stop User Changing Rows
Is there a general way to disable a user from changing row size or adding new rows.

View Replies!   View Related
Open Text File Into Excel Template
I work for a company that has about 650 locations. Each location has a location manager that is responsible for submitting a Performance Review spreadsheet for all the employees at their location.

Currently I have an Excel file that has all employees/locations. One of the columns on my spreadsheet is "Location ID"...which is literately a number we've assigned to our Locations. I have an Excel template saved that I'd like each Location to open into.

Can someone help me? I need each location to be it's own spreadesheet...which I have a template created already. I'd prefer to find an automated way to do this...rather than manually creating 650 spreadsheets.

I'd prefer to do this in anoter application, such as MS Access, but senior management already made the decision this will be done in I'm stuck with what I got.

View Replies!   View Related
Update Range In Template From Open File
I have used the directions below (from this site) to add a name to a validated cell and have that name added to a list. The issue I am trying to solve is this, when opening a new file from the template containing the code below the new file needs to have the range to which I'm refering updated to the range that was in the last opened file from the template. I tried by using a macro to copy and paste the range to an external wb that was hidden and then copy and paste to the new file opened from the template but it was a mess.
If anyone can help by posting some example code to steer me in the right direction I would be very grateful

1. Add any list of names or items to the range A1:A10 on any sheet.

2.Now in cell A11 enter this formula and copy it down to say row 20. =IF( COUNTIF($A$1:A10,$D$1),"x",$D$1) Note the relative reference of A10

3.Go to Insert>Name-define and in the Names in workbook: box type:MyName

4.In the Refers to: box enter this formula: =OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,"<>x"),1)

5.Click Add then Ok.

6. Select Cell D1 and go to Data> Validation, select List from the Allow: box and in the Source: box type: =MyNames Ensure the In-cell dropdown box is checked.

7.Click the Error Alert page tab and uncheck the Show error alert after invalid data is entered box. Now click Ok.

8.Right click on the sheet name tab and select View Code in here paste the code below:

Private Sub Worksheet_Calculate()
On Error Resume Next
Application.EnableEvents = False
Range("MyNames") = Range("MyNames").Value
Application.EnableEvents = True
On Error Goto 0
End Sub

9.Click the top right X to get back to Excel and now Save.

Now select cell D1 and type in any name, that is NOT part of the list, and Enter. Select D1 again and look at the list. The new name should now be part of it!

View Replies!   View Related
VBA Code In A Template (.xlt) Versus Regular (.xls) File
Does VBA code created in an Excel file that is saved as a Template (.xlt) work the same when the file is opened as an .xls file?

View Replies!   View Related
Create New File With Data From Database Through A Template
I've been looking around to find a solution for my problem and as a last resort I have decided to make a post, and I will get straight to the point. I have 3 xl Files of relevance:

1 - is my "database" which consists of multiple sheets with different information in each. Each sheet is correlated with each other by one common ID. 2 - is my "template" which is 2 sheets, which functions as a report. The template serves as the Report which will be printed. The data from each row from the database can fill the spaces in the template.

3 - "the tool" xl file is where i have my macros and the mapping for the which columns from the database belong to which cells in the template.

Basically what I'm trying to get to work is: User opens Tool clicks "create Reports" and the tool should then open the database, pick the range of rows from the database put the data into the relevant cell in the template save the "template" with the info on it, close it and do it again with each row of data from the database. So if I pick rows 4 to 34 in my tool, it should create 30 xls files from the template and fill in the data from each row into each newly generated "report".

View Replies!   View Related
Data Validation: How Can I Stop The User From Moving On?
I have attached a sheet that I'm having an issue with. Columns D and E on the Dim Calculator sheet have a data validation formula in the cell. It works in the sense that it pops up if they are outside the parameters that I want them to hit. However, if the user clicks the Cancel button they can just keep going on even though the entry in the cell is aganist the validation.

Example: Columns D and E cannot be larger than Column C. Currently now if Columns D or E are larger than Column C the validation error pops up. But I can just click on cancel and keep going. That is what I want to prevent.

Is there a way to grey out the cancel button?

Should I do something else within the validation itself?

View Replies!   View Related
Code To Stop A User Adding Or Deleting Sheets
I need to stop users from adding and deleting sheets in a workbook

My idea was obviously to disable the command bars to add or delete sheets in open event and then put back in before close.

But then i thought...whats stopping them from right clicking the sheet tab and inserting a sheet, can i remove that menu to...?

I also wasnt sure if there were short cut keys to add or delete sheets.?

Some of my users use excel alot so i want to account for an tips they know that i might not.

View Replies!   View Related
Saves A Template With Macro (XLTM) As A Normal File (XLSX)
When I prepare a file with macro's (to be used by other people), I save this file as a template with macro's with the extension *.xltm (template with macro).
Therefore no one can overwrite my file. These other people can open this template (e.g. double-click, but not file-open). But when these people save this file, Excel automatically saves it as a normal Excel-file with the extension *.xlsx.
Ok, a message is seen after pressing <enter> or clicking Save, but does normal users know, what they have to do then?

If I'm making a template with macro's, I want Excel to have this file saved as a file with macro's. Whatever did I make a file with macro's for? Is this a bug or is it done on purpose?

View Replies!   View Related
Read Data For A Group Of Charts Into A Master Template File
I need to read data for a group of charts into a master template file.

Sometimes my chart data range needs to be
Other times, when different data is read in, the same chart may need to only read

Is there any to name a range to read until row AT reaches it's first blank?

View Replies!   View Related
Saving A Worksheet As A File
I have a workbook which utilizes 2 worksheets to fill a third. I would like a macro to be able to save JUST that worksheet, and not the other two. I thought there was a checkbox allowing you to "save selected" only, but I'm either dreaming, or it was in a previous version of Excel (I know use Excel 2000).

View Replies!   View Related
Saving CSV File (with Delimitator)
I have a spreadsheet file (excel 2003). I'd like convert it in CSV with delim.

But when I save it appear message that say "the file contains some things not compatible with CSV with delimiter" then I can save it and lose information. After when I open it with Word it looks ok but delimiter is ";"

I set delimiter in windows and inside excel too to ","

But I can't undertand what are things not compatible. there are only words and number as general format....(there aren't "," and each other strange character).

View Replies!   View Related
Saving File In A Macro
How do I save the spreadsheet with the current name of the spreadsheet in a macro?

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 Replies!   View Related
Routine For Saving File
I am trying to develop a routine that will save the file I am currently working on, then save the current tab as a *.csv file with the name equal to cell "C1" in the same directory as the original file. Then I would like it to close the .csv file and reopen the original file all while being generic with the naming so that if the file name and location changes, the script will still work. I recorded a macro to get me started.

View Replies!   View Related
Saving A File With A Date
I have the below code to save a file once cretaed from master data, but it keeps failing on me.

View Replies!   View Related
Saving To A .csv File And Only 1st Row Contains Quotes
I am trying to create a .csv file from an Excel file using VBA and when I use the ActiveWorkbook.SaveAs command, only the first row of text contains " quotes around the text. I have multiple rows that must contain the quotes. I have tried using """", and Chr(34), but then that puts """ (3 quotes) around my text. Is there a different function that I should be attempting to use? I am using concatanation to create a field where I would like the quoted text and am using the ActiveCell.Value command (ActiveCell.Text didn't seem to make any difference).

View Replies!   View Related
Saving And Deleting A File
I have and xls list that needs to be saved in a text format without the use of "". The data needs to be read as data not as "data". The only way I have been able to do this is to save the file as a .prn and then rename the extension to .txt. My problem is that in order to rename the prn file i need to exit the file. If I do that then the macro ends and it will not rename. So I have worked around that by saving a second copy and then renaming the first copy. But I do not want to have a second copy. any ideas on how to get around this or to delete the scond copy from within the macro?

View Replies!   View Related
Saving WORKSHEET file. I'm using this code:

View Replies!   View Related
Vba Saving File To Certain Location
way to construct my save file so that it saves to the correct location.

I have a a FileSearch that lookings in a specific Folder and the sub folders for a file type and then saves them to a different location. Here is the location of the save in regards to the original location...

Filesearch folder= C:path
Savefolder=C:path he folder its found in & " excel converted"

If that makes any sense, basically I need to call the path of the file I opened, but not including the file name.

View Replies!   View Related
Saving File Using Cell As Name
I am trying to save this file but I'm having problems with "my Name" This information is in a workbook that is already opened called "Recaps" and the sheetname is "Data" and the value I am looking for is in "$C$5". I open another workbook called "Spare" and want to rename and save it to the code below.

Dim myPath$, myName$
Application.DisplayAlerts = False
myPath = "C:Service RecapsJohn Mudaro"
myName = Workbook ("Recaps) Sheets("Data").Range("C5").Value
ActiveWorkbook.SaveAs myPath & myName
Application.DisplayAlerts = True

View Replies!   View Related
Saving A File To The Server
I'm trying to open a file from server.After doing all the changes I have to save the file back to the server in the same name. It is asking for save as shouldn't. I want to save the file in the same path and in same name without asking the option of "Save as". I hv full permission for editing the file

View Replies!   View Related
Saving A File With A Different Name Each Time Using A Macro
What i want is for my user to click the save button linked to a macro that saves my file as Order0000. If there is a file already called this i want it to change the name to Order00001 and so on for Order00002.

Is this possible and if so what VBA code

View Replies!   View Related
Saving To The CSV (comma Delimited) File
I have a work sheet with some names address, and phone number in it... I need to save it as a CVS file (comma delimited) easy right??? WRONG!!! everytime I save it, it takes the phone number column and shortens it AND turn the phone numbers into a mess

What it looks like NOW

What is looks like after save


If is make the column bigger it goes back to the "good" numbers, but when I save to a CVS it goes to the "bad" number.

View Replies!   View Related
Temp File Not Saving Macros
The form is completed by the project leader, then they hit a Command Button and it's sent to the Senior Manager. After the Senior Manager looks at it and approves it, he then sends it on to Admin (me!) so the project can get set up on all of our systems.
We recently upgraded to Office 2007 (yes, we are a bit late!) and now the Macro that sends the form to Admin no longer works. An example of the error is as follows:
Cannot run the Macro "FY06-10 PROJECTS 19-Jan-10 08-32'EmailtoAdmin.EmailtoAdmin'. The macro may not be available in this workbook or all Macros may be disabled.

Here is the code that I use to send it to the Senior Manager:
Sub MailtoSM()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
Set Destwb = ActiveWorkbook

With Destwb
If Val(Application.Version) < 12 Then

FileExtStr = ".xls": FileFormatNum = -4143

View Replies!   View Related
Saving A File Using Cell Reference
I'm trying to open a workbook using a macro, then saving the workbook that's just been opened using a cell value as the title.

This is what I have so far ... to open up the workbook and then run a macro from there .. this works fine

Sub Test1()
' Test1 Macro
' Macro recorded 08/11/2009 by Tracy
Workbooks.Open ThisWorkbook.Path & "/MultiFuel Expense.xls"

Application.Run "'MultiFuel Expense.xls'!TestSave"

End Sub
This macro is the one I'm struggling with. I need to save a copy of the workbook I just opened (currently named MultiFuel Expense) in the same directory using the cell contents of G2 as it's name with _expense on the end.

Sub TestSave()
' TestSave Macro
' Macro recorded 08/11/2009 by Tracy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "" & Range("G2") & "_expense"
End Sub

View Replies!   View Related
Saving File In DBase IV Format
I have done this several times in the past, but don't do it often. I have an
Excel spreadsheet that I need to save in DBase IV format, but when I try, it
cu ts off all of the columns to the right of what I can see on the screen
when I go back into the DBF file.

View Replies!   View Related
Delete Code When Saving The File
I have posted already the problem to delete the code when sending a sheet via email and I have found the solution to the problem.

I tried to do the same when saving the file to specific folder, but i am facing problem.

View Replies!   View Related
Saving The Date Into The Title Of The XLS File
I want to add the text (a date) in cell A6 to the title of my spreadsheet when I file => save as. I have the file => save as written into my code at the end and it is the last operation of the code, but I want the date that appears in A6 to automatically appear in the title. This date is not todays date. This way the title will always be EP120-the text in cell A6.

i.e. EP120-Aug-18-2007

Here is the code I have so far...

View Replies!   View Related
Saving Custom Toolbar With File
The file who containd the toolbars informations is C:Documents and Settings<<USER>>Application DataMicrosoftExcelExcel.xlb

I copied this file to another computer and my custom toolbar appeared there.

In a file, I created a custom toolbar with submenus. It is possible to save this toolbar with file, so that I can use the file on other computers without the need to create each time this bar.

View Replies!   View Related
Saving A File With DateTime Stamp
I have a macro which creates a new file and saves an extract of data to it. I would like to add a datetimestamp to the end of the filename while saving.

Is there anyway to do this using my existing macro with some changes?

My relevant part of the macro looks like this.

ActiveWorkbook.SaveAs Filename:= _
"W:PJ_Report.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _

This saves the file as PJ_Report.xls

I would like this to be


or even

PJ_Report_20080609 will do.

View Replies!   View Related
Slow Saving File Office 10.5 (Mac)
In certain spreadsheets I have noticed a significant deterioration in save times. There may/may not have any formulas or macros on them and most are very small files 20-50kb. I notice that Excel puts a temporary file on the desktop while saving the original. The temp file goes away once the original is saved. While saving, I get the time icon which goes to beachball and it can take up to a minute to save the file. This happens on 2-3 Macs on network. Using 10.5

View Replies!   View Related
Saving File As Current Workbook Name
ChDir "C:Documents and SettingssysxxxDesktopcode testNew Folder"
ActiveWorkbook. SaveAs Filename:= _
"C:Documents and SettingssysxxxDesktopcode testNew FolderBillsFileName.xlw" _
, FileFormat:=xlExcel4Workbook, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Here's the code. My only question is how would I change "BillsFileName.xlw" to be the name of the current workbook?

Is it something as simple as


View Replies!   View Related
Saving File With Save As Dialog Box
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 Replies!   View Related
Copyright 2005-08, All rights reserved