Rick, thanks again for this information. This simplies the code
significantly.

Lesson learned on my part. I'll be more specific with my next question.

Appreciate your help!

"Rick Rothstein" wrote:

The CountA worksheet function does not require contiguous cells (I used contiguous cells because you didn't provide the range you were interested in covering, so I had to make a guess), here is the code modified to handle 10 non-contiguous cells (by the way, they do not have to be in the same column as I have shown either)...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' Count how many cells in the range have something in them
If WorksheetFunction.CountA(Worksheets("Sheet1").Rang e("A19,A25,A31,A37,A43,A49,A55,A61,A67,A73")) = 0 Then
Cancel = True
MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!"
End If
End Sub

By the way, this portion of this thread is a perfect example of why it is almost always a bad idea to simplify your questions when you post them to newsgroups... we cannot offer other solutions to setups you don't tell us about.

--
Rick (MVP - Excel)


"laavista" wrote in message ...
Rick, thank you for your input. I will change my code and use the SaveAsUI
parameter.

I like the change you made with the consecutive range, but the cells I'm
checking are not contiguous.

I really appreciate you taking the time to provide advice.

"Rick Rothstein" wrote:

You have answers to your main question, but I would like to address your posted code.

First, why did you change the SaveAsUI parameter from the BeforeSave event header to CheckCells? You should not be modifying the header in any way as it is provided by VB for you... leave it as SaveAsUI and, if you need to, use that name within your code. Just so you know, that parameter will be automatically set to True if the SaveAs dialog box will appear and False if it won't appear. The SaveAs dialog box will appear only when the file is being saved for the first time or when the user chooses Save As from the File menu item... I don't see how your code would need to care about that.

Second, assuming your range of 10 cells is A19:A28 (just a guess as you didn't tell us), you can perform the function you are trying to do with this much shorter code...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' Count how many cells in the range have something in them
If WorksheetFunction.CountA(Worksheets("Sheet1").Rang e("A19:A28")) = 0 Then
Cancel = True
MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!"
End If
End Sub

--
Rick (MVP - Excel)


"laavista" wrote in message ...
This is the first time I have ever been stumped, was desperate, and laughed
until I had tears in my eyes!

I'm using Excel 2003. In the spreadsheet, there are 10 cells, and the
user must select 1 or more options by entering an X in the desired cell(s).
If they SAVE the spreadsheet, at least one of the 10 cells must have data.

I'm thinking, no problem, that's simple. I'm fairly new to Excel VBA, and
coded the following. It works, but it won't let ME save my code without
having an X in at least one of the 10 cells!

Is there a way to save the code only?

Your help would be GREATLY appreciated!

For brevity sake, I only listed conditions for 2 of the cells.

=======
Private Sub Workbook_BeforeSave(ByVal CheckCells As Boolean, Cancel As
Boolean)

Dim TheCells As Range
Dim SomethingWasChecked As String

If CheckCells = False Then

SomethingWasChecked = "N"

If Me.Worksheets("sheet1").Range("A19").Value < "" Then
SomethingWasChecked = "Y"
GoTo StopChecking
End If

If Me.Worksheets("sheet1").Range("A25").Value < "" Then
SomethingWasChecked = "Y"
GoTo StopChecking
End If


StopChecking:

If SomethingWasChecked < "Y" Then
Cancel = True
MsgBox "Please checkmark at least one option or close file without
saving!", vbCritical, "Missing Data!"
End If

End If

End Sub