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


ADVERTISEMENT

Calculate Average For Values In Range Of Data That Fulfill Certain Condition

May 20, 2014

I am trying to calculate average for values in a range of data that fulfill a certain condition viz. >1000 &

View 5 Replies View Related

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 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 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 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 Click On Check Box If Condition Met

Oct 20, 2006

I have two check boxes in a worksheet created from the "Forms" toolbar.

Is it possible to prevent a user from clicking these two check boxes if he meet a condition in cell A1.

Eg, If cell A1 value is "Marketing", the user is not able to click on these two boxes.

View 9 Replies View Related

Prevent Data Entry If Condition Met

Jun 4, 2007

I am adding up the value of a range of cells in one cell. What I want is If the value of the cell which is adding up the values of the range of cells exceeds a particular number it should throw an error.

View 6 Replies View Related

Stopping User Saving Unless Condition Met.

Mar 6, 2008

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

How To Prevent Printing In Excel

Feb 25, 2014

I am trying to prevent a document from being printed unless certain cells are filled in. I am new to VB and just want to know the code. I have 36 fields that need information to be filled in.

View 3 Replies View Related

Saving Excel Sheet As PDF?

May 26, 2013

I got a problem saving an Excel Sheet as PDF. The problem is that i cant see some parts of sheet on the PDF file. I have uploaded 2 files one of which is that .xlsx file which i want to save as PDF, the other is the PDF file that i see after saving which is not desirable.

View 1 Replies View Related

Saving Excel Sheet As TXT?

Jan 27, 2012

I've got a macro that generates some parameters and after they have been generated, the values have to be saved in text file. The problem that I'm facing is that the text file saves the values in double quotes.

I've been searching online for a solution to the problem and what I've found is that if the cell value in excel includes a comma, the resultant text file will store the entire value in double quotes.

I'm using the save as method and I read that if you provide the save format as xlTextPrinter this problem will not occur, but the limitation is that it can only work with values less than 240 characters in length.

View 5 Replies View Related

Excel 2010 :: Prevent Scrolling To Bottom Row

Jun 28, 2011

I'm currently creating a workbook whereby I have (regrettably) highlighted entire columns and formatted these columns to show borders. The reason I did this was because drawing them in manually (I thought) would take much longer with the way the worksheet is laid out.

Obviously highlighting the entire column has meant that borders were drawn all the way down to the bottom row of the workbook, and so the vertical scroll bar cannot effectively be 'dragged' up and down as even a tiny movement will cause it to scroll right past the worksheet.

I have tried running a macro to clear contents and formatting for all the unused rows, but the scroll bar remains to be a pain.

I want the scroll bar to only scroll down to a few cells below the available work space like it should do.

View 9 Replies View Related

Prevent Excel From Applying Table Styles?

Dec 1, 2011

Is there a way of preventing Exel from Applying table styles, when converting a range to a table. and leave the formatting of the table intact?

View 1 Replies View Related

Excel 2007 :: Prevent SQL From Locking Tables?

Oct 28, 2013

I saw a previous post regarding using "with (nolock)" in the FROM statement with each table to prevent SQL from locking the tables. I'm not sure exactly what's going on, but when I edited a query to add an extra criteria, the query is grinding the server practically to a halt (such power!!). Our IT guy tells me that my query is taxing the server. Maybe I'm not using the NOLOCK correctly?

Here is the query as I'm running it that runs slow. (The additional WHERE statement that I added that created the slow down is this one: ((cicmpy.AccountTypeCode='TSH') AND (oehdrhst_sql.ord_dt>={ts '2013-01-01 00:00:00'}))

Prior to adding this statement and the NOLOCK, the query runs in a flash.

Full query:

SELECT
oehdrhst_sql.ord_type,
oehdrhst_sql.bill_to_no,
cicmpy.AccountTypeCode,

[Code]....

View 1 Replies View Related

Excel 2007 :: Not Saving Formatting Changes

Oct 13, 2008

I have someone in the company that I work for that is using Excel 2007 in compatibility mode, that is trying to make changes to an Excel 2003 file.

The file in question is a large one. She can type in the rows and columns and anything that she types will save. However if she trys to make any changes to the file in terms on calculations or highlighting specific columns, then these changes are not saved.

I have found out though that if she saves this 2003 file in the 2007 format, then all over her changes will save. She can't save it in the 2007 format due to some people still on 2003 that edit this file as well.

View 10 Replies View Related

Saving Default Settings In Excel

Aug 20, 2013

I am working on lots of excels, with data queries from sql and pivot tables. Is there a way to set defaults to some settings in excel (not in this specific file).

To be more specific:

1. Import data from SQL: Refresh on opening, save password
2. Formatting of number to have two digits and a comma separator
3. Pivot table: when adding to values sum instead of count, refresh when opening the file

View 1 Replies View Related

Saving Data From Excel To Access Through VBA

Mar 12, 2014

I have created one Info path solution to gather information from different locations to one hidden Access database. And in last month or two I already have more than 30 000 entries in database. On other side I have created 2 excel workbooks, one for purpose of report that has all connection done through VBA, user just opens it, clicks button and gets report and one workbook that is for other set of users so they can change 2 specific columns of data, everything else is locked. When one department fills value it should fill based on gathered information, and clicks save that cell or cells are being locked.

At first I did on worksheet change event, when user changes a cell (enters information) it triggers worksheet_change event and saves that value in database and colours cell green. But, then users start complaining that when they use copy paste or the use fill paste option (just pull value from one cell to others) it does not save value. Ok ... that is because worksheet_change event does not trigger in situation when copy paste in any form occurs.

So how to solve it ... when pulling data in excel I pull identical table (data) in other hidden worksheet. When they change what they need to change, they click button "save to database" i have created and there is a macro that makes other sheet visible, goes through the records and saves where cell value in table that is being entered is different then same cell in hidden table.

Problem is that sometimes my code works without a problem and sometimes they get an error. I cannot figure out when. When a lot of users use that excel file, for some users it is a read only file, and as I presume that is ok, because macro works in any case and data are stores in database so excel file itself does not have to be saved.

Here is code.

[Code] .....

View 1 Replies View Related

Excel 2010 :: Saving To TXT Files?

Nov 23, 2011

I'm saving information from excel 2010 in to a text file (txt), when doing this and opening up the txt file the below line is saving it with " at the start and at the end of the line.

:32A:110809GBP4,00

Is there any way when saving as a text file without the " pulling through at the start and at the end of the line?

View 2 Replies View Related

Saving Excel File As Macro

Nov 13, 2013

I'm using this bit of code to save a cvs file as an xls file.

Code:
'Save file
Dim Filename As String

Filename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Filename "False" Then

ActiveWorkbook.SaveAs Filename
End If

However, I would like the file to be named Win7Sync-

View 5 Replies View Related

Saving Batch Files From Excel

Nov 22, 2013

I currently have 3 batch files that I would like to be able to update using VBA instead of manually adding the information to each one. My goal is to be able to use my excel file and when I add new lines I could then run the script to save and/or update each of the batch files. I would like to be able to add the new lines I added in excel to the end of the batch files. I use the batch files for automation purposes but I do not like the fact that I have to open each one and added the same lines three times. This is why I want to be able to update my excel file and then add those new lines to the batch file. An example of how to accomplish this task using VBA would be awesome.

View 2 Replies View Related

Excel 2010 :: Keeps Crashing And Not Responding Or Saving?

Mar 9, 2012

I have a genuine copy of office pro 2010. All works fine except excel!

It seems to be mainly on one workbook I have brought over from open office and on a mac! It wont save due to errors it won't tell you about? It then stops responding or crashes? Wont save as etc....

I have repaired, I have even deleted and downloaded a new office pro genuine and installed and doing the same so i guess it has to be the file?

BUT it seems to work on another laptop we have, same file, same excel????

The only thing i seem to be able to find is in the permissions it has an "unknown account"? Also under the file menu where it allows you to check the file it states something about conditional formatting i have used and not being compatible BUT i cant for the life of me find that in the menus to get rid?

View 10 Replies View Related

Excel 2007 :: Saving As Text Tab Delimited

Apr 18, 2013

Excel 2007 on windows 7 professional.

We've got an end user who opens a .dat file which is delimited with semi-colons, replaces some text in a couple of columns then saves as a text tab delimited file. She swears that before today, the saved file would retain the semi-colons. Now she says it's not. she generated a new .dat file for me and recreated the process, with the resultant semicolon-free file. I asked her to generate another one and send it to me. I opened it first in notepad, and saw the semi-colons.

Opened it in excel (I'm using 2010 on win 7 professional), which saw it as a delimited file and asked me to specify the delimiter. It shows up fine, all the columns are righteous. I made no edits and saved as a text tab delimited, and that saved file had no semi-colons in it when I opened it. This seems normal to me; there's no semi-colons in the excel file, so why would there be in the text tab delimited file. I asked her coworker to generate another .dat from his pc and let me know what he sees, but he hasn't reported back yet.

View 5 Replies View Related

Saving Excel Sheet On Network Location

Mar 21, 2014

I am currently having a slightly annoying issue that only happens 50% of the time, that when trying to re-save a excel sheet on a network location. The network location is a folder on another computer. The same happens when I try and save it as a pdf, but only happens half of the time.

The message which pops up says something along the line of 'Saving as: 'File-ServerDocumentsexcelsheetexample.xls' ' and a sort of green loading bar appears. It does this for about 15 seconds then goes off.

I've checked our anti virus (which is eset) and I've set it to not real time scan network locations.

I have attached a example of the saving message: ExcelWarningExample.jpg

View 2 Replies View Related

Excel 2010 :: Unfilter Workbook Before Saving?

Feb 16, 2014

In order to unfilter a workbook before saving, that is often left by the users with filters on, I use the following code:

[Code].....

The code works just perfect for a workbook that has a table in a normal range of cells.

If I insert a table (using Office 2010 and later option), the code simply doesn't work.

View 5 Replies View Related







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