Check for Required Fields, an hilarious error!

[laavista]

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

 

[Joel]

Modifyu the code so that it checks if the active sheet name is Sheet1. then
change sheets to shet2 and save. Yo can export the VBA code to a BAS file
and import the code using import. See www.cpearson.com on methods to import
and export macros. It is under copying macros from one workbook to a 2nd
workbook.

 

[Dave Peterson]

This code fires when you're saving--but only if macros are enabled and if events
are enabled.

So you could open that workbook with macros disabled, make your changes and save
and close the workbook.

Or you could turn off events first, save, then turn on events.

Open the VBE
Hit ctrl-g to see the immediate window
type this and hit enter:
application.enableevents = false

Then back to excel and save your workbook.

Then back to the VBE's immediate window and turn it back on again:
application.enableevents = true

(Remember that any user can do the same thing, too!)

=====
Another option would be to look at the some indicator (application.username???)
and process accordingly.

 

[p45cal]

re:> "Another option would be to look at the some indicato

(application.username???) and process accordingly."

Yes, I've done this in the past, though *Application.username* migh
not be good if on a company network if your username (the same usernam
that's to be found in the -Options- dialogue box -General -tab reache
via the dropdown menus *Tools|Options...*) is common to other user'
User Names there. I worked for a largish company where they installed M
Office and the Username was always the company name.
Easy to solve, make sure your UserName is likely to be unique. Anyon
can change the MSOffice username easily in this dialogue box.

In the code you could add:
If Application.UserName="YourUniqueName" then 'at th
top of the sub
'your present code here
'
'
End if 'at the bottom of the sub

In the corporate environment I used *Environ("Username")* which is th
same as the log on to the network/machine, which is something ver
likely to be unique to each user

 

[p45cal]

Fundamental error:
If Application.UserName="YourUniqueName" then
should read:
If Application.UserName<>"YourUniqueName" then

 

[Rick Rothstein]

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").Range("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

 

[Dave Peterson]

When I was developing a workbook, I'd change my username to:

Dave Petersonxxx
(via tools|Options|General Tab (in xl2003 menus)

Then I'd check for that username when I needed to save without the checks.

But when I was testing my code, I'd change my username back to normal. Then I
could see if the code broke.

if lcase(application.username) = lcase("dave petersonxxx") then
'do nothing
else
'do the work
end if

It worked nicely when I wanted to be a user sometimes and a developer other
times.

 

[laavista]

P45cal: Thanks for your input!

Fundamental error:
If Application.UserName="YourUniqueName" then
should read:
If Application.UserName<>"YourUniqueName" then

 

[laavista]

This is great, Dave, and is an easy fix. Thanks!!

 

[laavista]

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.

 

[laavista]

Dave, thanks again! This is awesome. Thanks so much for taking the time to
help me with this. I really appreciate it!

When I was developing a workbook, I'd change my username to:

Dave Petersonxxx
(via tools|Options|General Tab (in xl2003 menus)

Then I'd check for that username when I needed to save without the checks.

But when I was testing my code, I'd change my username back to normal. Then I
could see if the code broke.

if lcase(application.username) = lcase("dave petersonxxx") then
'do nothing
else
'do the work
end if

It worked nicely when I wanted to be a user sometimes and a developer other
times.

 

[Rick Rothstein]

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").Range("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.

 

[laavista]

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!