Need Help on Define Names

  • Thread starter Thread starter Souny
  • Start date Start date
S

Souny

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.
 
Try something like this.

Option Explicit
Sub Test()
Dim aWB As Excel.Workbook
Dim myName As Excel.Name
Dim NameMatch As Boolean

Set aWB = ActiveWorkbook

NameMatch = False
For Each myName In aWB.Names
If myName.Name ="DefineName1" or_
myName.Name = "DefineName2" or _
nyName.name = "'Sheet 2'!DefineName3" Then

'Alternative
'if myName.Name like "*DefineName*" then

NameMatch = True
Exit For
End If
Next myName

If Not NameMatch Then
MsgBox "Please use another template.", vbInformation
Exit Sub
End If

'Execute rest of sub

End Sub
 
Sub checknames()
mc = 0
For Each n In ThisWorkbook.Names
If LCase(Left(n.Name, 4)) = "name" Then
mc = mc + 1
If mc = 3 Then Exit For
End If
Next n
'If mc = 3 Then MsgBox "oK"
If mc <> 3 Then MsgBox "another"
End Sub
 
Don,

Thank you very much for the code. I tried your code, and somehow, I am
still getting the message even if I have all 3 define names.

I don't know the following information would relevant to the code that I
need help with.

I have more than these 3 defined names in my workbook. I want the code to
message the users if these 3 defined names specifically do not exist in the
workbook. If these 3 defined names specifically exist in the workbook, the
rest of code would execute.

Below is a sample to indicate the structure that I currently have.

Sub checknames()
mc = 0
For Each n In ThisWorkbook.Names
If LCase(Left(n.Name, 4)) = "name" Then
mc = mc + 1
If mc = 3 Then Exit For
End If
Next n
If mc <> 3 Then
MsgBox "another"
else
'execute the rest of code
End Sub

Thanks.
 
Barb,

Thank you very much for the code. I tried your code and got an error
message. The error message is "Compile error: Variable not defined". When I
click Ok on the error message, it points to Sub Test().

I didn't choose the alternative way because my defined names do not have the
same name; however, I will keep it for future reference.

Do you know why?

In addition to what I have provided initially, I have more than 3 defined
names that I indicated in my workbook.

Basically, I need help with the code if all 3 defined names, specifically,
are in the workbook, execute the rest of code. If any one of 3 defined names
is not in the workbook, the users will message with "please use other
template".

Thanks.
 
Barb,

I just tried your code again without Option Explicit, and it worked.

Do I really need it?

In addition, if you don't mind sharing, why my code did not work? What did
I miss?

Thanks.
 
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?
 
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You send a clear explanation of what you want
3. You send before/after examples and expected results.
 
Back
Top