Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
no saving if certain value present
Is there a way to not allow the user to save if values of certain cells are
greater than a specified value? If not is there a way to have a prompt pop up when the values are too high? Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
no saving if certain value present
You might want to try using Data Validation. You can set it up so that if the
user enters anything greater than the speified value, an error window will pop up asking them to try again. In Excel 2007 this feature is located in the Data Tab, Data Tools Category, and it's called Data Validation. "laureneparker" wrote: Is there a way to not allow the user to save if values of certain cells are greater than a specified value? If not is there a way to have a prompt pop up when the values are too high? Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
no saving if certain value present
One way:
Put this in your ThisWorkbook code module: Private Sub Workbook_BeforePrint(Cancel As Boolean) Const cdTooHigh As Double = 100 'change to suit Dim rCell As Range Dim sMsg As String For Each rCell In Sheets("Sheet1").Range("A1,B2,C3,J10") If rCell.Value cdTooHigh Then Cancel = True sMsg = "Cell " & rCell.Address(False, False) & _ " in worksheet " & rCell.Parent.Name & _ " must be less than " & cdTooHigh Exit For End If Next rCell If Cancel Then MsgBox sMsg End Sub If you're not familiar with macros, see http://www.mvps.org/dmcritchie/excel/getstarted.htm In article , laureneparker wrote: Is there a way to not allow the user to save if values of certain cells are greater than a specified value? If not is there a way to have a prompt pop up when the values are too high? Any help would be greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
no saving if certain value present
Personally I would avoid the "can not save scenario". That tends to frustrate
users to no end. They close the file and loose their work and then call you to complain. The prompt however is a good way to handle this, but it requres a macro to make it happen. It is also helpful to have conditional formatting of the cells in question to indicate which values are not within parameters. Here is some sample code for you to test. To add this code right click the XL logo next to the item File in the menu and select view Code. This prompts the user when they try to close the file. You will need to change the sheet reference and the range address... Private Sub Workbook_BeforeClose(Cancel As Boolean) If Sheets("Sheet1").Range("A1").Value 100 Then If MsgBox("Prameters exceeded. Did you still want to Exit?", _ vbCritical + vbYesNo, _ "Parameters Exceeded") = vbNo Then Cancel = True End If End Sub -- HTH... Jim Thomlinson "laureneparker" wrote: Is there a way to not allow the user to save if values of certain cells are greater than a specified value? If not is there a way to have a prompt pop up when the values are too high? Any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Present value and future value | Excel Worksheet Functions | |||
present value formula | Excel Worksheet Functions | |||
net present value | Excel Worksheet Functions | |||
Net Present Value | New Users to Excel | |||
Present Value | New Users to Excel |