ambiguous error
 

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

--
Message posted from http://www.ExcelForum.com

ambiguous error
 

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

--
Message posted from http://www.ExcelForum.com

ambiguous error
 

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


"jmorgs " wrote in
message ...
I have an Excel workbook which is a form that must be filled
out by
employees. I wrote code so that if a certain box is left empty
it will
not allow the workbook to be saved. The problem is that when I
tried to
copy and paste this code to another cell I get an error that
says
"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 so
this code will work.

Any help is appreciated!


---
Message posted from http://www.ExcelForum.com/

ambiguous error
 

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?



"jmorgs <" wrote:

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 is
workbook_beforeclose, because I have another cell with this exact same
code, I get this ambiguous error!
HELP!

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson