ambiguous error

[jmorgs]

I have an Excel workbook which is a form that must be filled out b
employees. I wrote code so that if a certain box is left empty it wil
not allow the workbook to be saved. The problem is that when I tried t
copy and paste this code to another cell I get an error that say
"ambiguous name detected, workbook_before close" My code is as follows


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim j As Integer


If Trim(Me.Worksheets("competency").Range("C3").Value) = "" Then
MsgBox "Cell C3 is empty. Please fill it"
Cancel = True
Exit Sub
End If

End Sub

The problem is that I can't think of another name to name the event s
this code will work.

Any help is appreciated

 

[jmorgs]

The problem is that if I give it another name, like sheet_beforeclose
or book_beforeclose the code won't work, but if I name i
workbook_beforeclose, because I have another cell with this exact sam
code, I get this ambiguous error!
HELP

 

[Chip Pearson]

All procedures in a module must have unique names. The error
message is telling you that you already have a BeforeClose event
procedure in the ThisWorkbook module. Put your code in the
existing procedure rather than creating a new procedure with the
same name.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Dave Peterson]

If you have multiple cells on the same worksheet that must be filled in, maybe
you could use something like:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myRng As Range
Dim myCell As Range
Dim myMsg As String

Set myRng = Me.Worksheets("competency").Range("C3,d19")

myMsg = ""
For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
myMsg = myMsg & ", " & myCell.Address(0, 0)
End If
Next myCell

If myMsg <> "" Then
myMsg = "Please Fill: " & Mid(myMsg, 3)
MsgBox myMsg
Cancel = True
End If

End Sub

=====
And just a thought, wouldn't this be better in the workbook_beforesave event?
If I open and want to close without saving, why should I have to fill in those
cells?