RE: Need Help on Define Names

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 Reinhardt" wrote:

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


"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.
.

Relevant Pages

  • RE: Need Help on Define Names
    ... I just tried your code again without Option Explicit, ... Dim NameMatch As Boolean ... I need help with writing a code to execute if one of the 3 above defined ... workbook, the message comes up and it does not execute the rest of code. ...
    (microsoft.public.excel.programming)
  • Re: blue screen
    ... Enquire, plan and execute ... Background information on Stop Error message ...
    (microsoft.public.windowsxp.general)
  • Re: Prompt for VBAproject password
    ... I am not looking to unlock the project by means of code. ... At this moment I only get Error 50289, saying that Excel cannot execute the ... macro because the project is protected. ... Once individualised I want to finalise the workbook. ...
    (microsoft.public.excel.programming)
  • Re: Seeking Advice
    ... Why with Execute and not OpenQuery. ... I got an error message that the table already existed. ... Dim strDoc 1As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Removing and event from the html code.
    ... Like I said earlier I inherited this script with the page. ... Actually I don't get an error message, ... > This assigns the *result* of running countdown to window.onload. ... window.onload won't execute until the page has fully loaded. ...
    (comp.lang.javascript)