Do you mean that if exactly one name is missing, then force the message or if 1,
2 or even 3 names are missing, the user should see the message.

If you really meant that only a single missing name should cause the message:

Option Explicit
Sub testme()

Dim myNames As Variant
Dim HowManyMissing As Long
Dim TestName As Name
Dim iCtr As Long
Dim wkbk As Workbook

Set wkbk = ActiveWorkbook 'is this right?

myNames = Array("Sheet1!DefineName1", _
"Sheet1!DefineName2", _
"'Sheet 2'!DefineName3")

HowManyMissing = 0
For iCtr = LBound(myNames) To UBound(myNames)
Set TestName = Nothing
On Error Resume Next
Set TestName = wkbk.Names(myNames(iCtr))
On Error GoTo 0

If TestName Is Nothing Then
HowManyMissing = HowManyMissing + 1
End If
Next iCtr

If HowManyMissing = 1 Then
MsgBox "Please use another template"
'wkbk.Close savechanges:=False '???
End If

End Sub

If you really meant that any missing name should cause the message, you don't
need to check for all three.

Option Explicit
Sub testme()

Dim myNames As Variant
Dim TestName As Name
Dim iCtr As Long
Dim wkbk As Workbook

Set wkbk = ActiveWorkbook 'is this right?

myNames = Array("Sheet1!DefineName1", _
"Sheet1!DefineName2", _
"'Sheet 2'!DefineName3")

For iCtr = LBound(myNames) To UBound(myNames)
Set TestName = Nothing
On Error Resume Next
Set TestName = wkbk.Names(myNames(iCtr))
On Error GoTo 0

If TestName Is Nothing Then
MsgBox "Please use another template"
'wkbk.Close savechanges:=False '???
Exit For
End If
Next iCtr

End Sub

And these are all sheet level names, right?


Souny wrote:

Hello all,

I have 3 defined names (Sheet1!DefineName1, Sheet1!DefineName2 and 'Sheet
2'!DefineName3) in two worksheets of my Excel file.

I need help with writing a code to execute if one of the 3 above defined
names is not in the workbook, message the users that please use another
template. Else execute the rest of code.

Below is the code that I currently have, and it's not working.

if activeworkbook.name<"DefineName1" or activeworkbook.name<"DefineName2"
or activeworkbook.name<"'Sheet 2'!DefineName3" then
msgbox "Please use another template.",vbinformation
else
execute the rest of code
end if

Based on the above code, even if I have all three define names in the
workbook, the message comes up and it does not execute the rest of code. The
message should not come up and the code should execute the rest of code if
all three define names are in the workbook.

Thanks.

--

Dave Peterson