Disable all save command if condition is not met

Hugotote

New Member
Joined
Aug 7, 2007
Messages
3
Hello, im trying to disable the save command in a document, I would like the user to be able to save the file only if some of the cells in sheet2 are
1.- not empty
2.- with the proper format for a few of those selected cells

if these conditions are not met, then display message "cannot be saved until..."

This is what i have, so far i can only do this for one cell, and i can only validate one condition.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Worksheets("Sheet2").Range("B5") = "Needs to be filled in" Then
Cancel = True
lReply = MsgBox("You cannot save until...", vbOKOnly)
Else
If Worksheets("Sheet2").Range("B5") = "Incorrect format" Then
Cancel = True
lReply = MsgBox("You cant save until correct format", vbOKOnly)
End If
End If
End Sub

The first one does work, but, other than that, if i try to extend the range, it wont compile.

Id apreciate some help in this matter, and i hope i made my self clear.

Thanks in advance
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
It could be that you have given the wrong sheet name for the second IF statement? Perhaps it should be Sheet2?
 
Upvote 0
typing error

Thanks for the observation, however, it was just a typo mistake while copying the code in the post.
 
Upvote 0
I copied your amended code to a new Excel 2003 workbook ..... and it compiles and runs OK (that is, I tested both IF statements and they performed as expected).
I have re-worked the code and tested it (it works OK for both statements) so try copying and pasting it into your workbook - just in case you have something embedded in the text that is causing the problem.
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Worksheets("Sheet2").Range("B5") = "Needs to be filled in" Then
    Cancel = True
    MsgBox "You cannot save until...", vbOKOnly
ElseIf Worksheets("Sheet2").Range("B5") = "Incorrect format" Then
    Cancel = True
    MsgBox "You cant save until correct format", vbOKOnly
End If
End Sub
 
Upvote 0
Thanks it works great, it seems i need to familiarize with the details, would you know how to extend the range for validating in other cells beside B5?
 
Upvote 0
It is difficult to advise without knowing a lot more about what you are trying to do.
If you want a very good reference source for VBA, I recommend 2500 VBA Examples available from MrExcel store or (probably cheaper) on Amazon.com
The following bit of VBA code may give you some ideas if you want to search a range of cells:
Code:
Dim arrCells As Variant
Dim intRow As Integer
'
  arrCells = Range("B5:B20")
On Error Resume Next
  intRow = Application.Match("Search Item", arrCells, 0)
  If Err > 0 Then
    MsgBox "Not Found in Range of Cells"
  Else
    MsgBox "Found as item " & intRow & " in the list"
  End If
On Error GoTo 0
There are, of course, many other ways to do the same thing!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top