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 9 Replies
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 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.
I've got a workbook with a several pivot tables. For convenience, I've deleted the pivot table source and left the pivots "self-standing". Problem is now when I try to refresh the workbook, I get a "Reference is not valid" error which prevents the workbook from updating.
View 2 Replies View RelatedI 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)
I am working on a project in which I used a formula and created a kind of a software.what I need to do is that after my user enters certain data it is sent to a worksheet(as directed).now I want that data to remain saved there even when I am changing the inputs in the formula. what is happening is that my previous data is getting erased when I enter new variables to the formula .
View 1 Replies View RelatedI 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?
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.
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.
Trying to create a new workbook from another open workbook, then copying all the sheets that aren't called "Summary" to that new open workbook and then saving it. I get a subscript error on this line:
[Code]....
i have a workbook blank template for my colleagues to fill in, they fill in this blank template and save it under the serial number of the item they are creating. However a lot of errors exist when they input the serial number inside the workbook where the serial number is a duplicate of a former serial number.
Anyhow i was wondering is there any way when they input the serial number into the worksheet that i could get it to save in a separate workbook and then have conditional formatting to check those serial numbers in the workbook to see if they match?
I will shortly be running a project over a twenty four hour period, where a group of people will be inputting data into a workbook (one worksheet per hour), and what I would like to know is if it is possible, to input some coding into a thisWorkbook module to action a save on the workbook every time an entry is placed in column A (of each worksheet) which is divisable by 10 (a10, a20, a30 etc).
View 2 Replies View RelatedI have an Excel doc with ~20 tabs. Each type represents 1 type of activities (soccer, tennis, etc). The size of the file (with the 20 tabs) is pretty significant (>1mb), which jams our email traffic.
Is it possible to:
- keep this workbook with the 20 tabs when open by the user;
- let the user select and fill in whatever tab he needs (just one at a time);
- have a button for the user to click that will save only this tab with a different filename (therefore, when sent by email, the file should be pretty small).
I'm trying to close a workbook without saving it. But everytime I get the message box popupped twice.
These are the codes that I use:
VB:
Private Sub Workbook_BeforeClose _(Cancel As Boolean)
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
[Code].....
As I've learned to develop Excel apps with vba over the years I've noticed that, if the Excel app starts to get too large (over 1MB) saving it will often cause the application to crash for no apparent reason - the result being that I lose all the vba code I entered before the last save - with the worst case being that, on occasion I have lost all of the vba code in an applications!!!
So, I've learned to back up often using a third-party utility that I use to save my applications, which works wonders in allowing me to save my application without it crashing. The application is called Code Cleaner 97.exe. It was designed around Excel 97 (which I still use) but if possible I'd like to get an updated version for Excel 2003 and Excel 2007, which I also use.
Does anyone know of a more recent version of this utility, or another similar utility that allows large Excel applications to be properly saved, etc?
Code Cleaner 97.exe was a freebie that was available through one of the old Compuserve Excel forums. It was a very popular utility and was suggested as a means to save one's Excel apps without the app getting trashed, etc.
I'm not expecting a freebie solution so I'll gladly pay for something that allows me to save my large Excel apps without them crashing, etc.
Is it possible to rename a new workbook without saving it. What I do is I create a new workbook but I don't want to save it:
Sheets("Layout").Copy
Now Sheets("Layout") is standing in a NEW workbook called Map1.xls.
I want to rename Map1.xls to Invoice.xls without saving the workbook.
Then I do:
Application.Dialogs(xlDialogSendMail).Show
ActiveWorkbook.Close SaveChanges:=False
Ok, I'm trying to learn how to create and save a new workbook using VBA...having some issues with this error:
Run-time error '1004':
Method 'SaveAs' of object '_Workbook' failed.
The line is highlighted in red.
Code:
Sub Copy_ActiveSheet_1()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
[Code] .........
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 RelatedI am using this code in my ThisWorkbook, but it keeps coming back as an error for
ActiveWorkbook.SaveAs Filename:=ThisFile
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Saves file as what is in cell "C7" to specified network location - variant below
Dim Location, ThisFile As String
Location = "\Office-pcpublicCustomers"
ThisFile = Range("C7") & Format(Now(), "dd/mm/yy")
ThisFile = Location + ThisFile
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub
When the button is pressed, It opens a Mesage Box giving an alert to the User about what is He going to do...if he clik no the macro stops, if the user click yes then it opens an InputBox ....and the name the user writes there will be the name of the new book...
If working good if the user press OK ...my problem comes If the User press CANCEL in the InputBox...The excel try to Save the file as FALSE...and I don´t know how to avoid it.
Here is the code.
Sub Botón1_AlHacerClic()
X = MsgBox("Warning Bla bla ".Are you sure ?", vbYesNo, Title:="IMPORTANTE")
Naa = Application.InputBox("Write the file name Here", "New File Name")
If X = vbYes Then
ChDir "C:UsersROBERTODocumentsBASES DE DATOS"
ActiveWorkbook.SaveAs Filename:=Naa, FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End If
End Sub
i have a workbook which is mainly use in construction building design... i want users to input ther respective data in that workbook to calculate and design only, but i dont want the user to save the changes they made...
View 9 Replies View RelatedHow do I get a shared workbook to automatically save upon every change by each user. The only way I can get this to work (at this point) is to run a macro that updates every 45 seconds IN EACH WORKBOOK! It's counterintuitive because the workbook is shared (on a network), but each user must start the "time" macro in their "individual" SHARED workbook to allow the automatic saving.
What am I missing? There has to be a more efficient way to do this, right?
Code below
HTML Sub Time()
Call Save
Application.OnTime Now + TimeValue("00:00:45"), "Time"
End Sub
HTML Sub Save()
ActiveWorkbook.Save
End Sub
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 RelatedAll I want to do is save a copy of one sheet called "Patient Stock Form", change the name to "Patient Order", and put it into an email. But my code is putting the entire workbook into the email. Here's the code:
VB:
Sub EMAILFORM()
Dim oApp As Object ' Outlook.Application
Dim oEmail As Object ' MailItem
[Code]....
Is it possible to have a userform open in a workbook and when the data from the form is saved, save it to a completely different workbook. If so, will that other workbook need to be open for the userform data to be saved?
View 5 Replies View RelatedI have my Excel workbook open & have made changes. Now I close it and Excel prompts me if I want to save the changes. Is there a way to just close the workbook and automatically accept any changes without have the "Do you want to save changes" message appear?
What about if it is opened in read-only mode? Typically the workbook will not save any changes unless you save it off as another file name. If there is a solution to my question above, can it be enabled only when the workbook is opened in edit mode, or will it not auto-save changes over the file because Excel knows it was opened in read-only mode?
I read two pages on "Workbook_BeforeClose" trying to find a relative answer. I want to able to close a workbook with out saving and not rely on the user pressing the correct button, can this be done?
View 3 Replies View RelatedI am trying to write code which copies the activesheet (called "Data"), opens a new workbook, pastes the data into it and closes the new sheet as "sales 2008" followed by the date. What I have at the moment is:
View 3 Replies View RelatedNeed 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 Relatedi get an excel file with dynamic data everyday. is there away i can use a macro on this file to store this dynamic data to a specfic closed workbook and add the data onto the old data in that workbook?
View 4 Replies View Related
