no saving if certain value present

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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