I have an add-in loaded all the time that checks any opened workbook name
for a specific string. The below code does this except when excel is opened
with no workbook (i.e. from Programs Menu). This code resides in the
App_WorkbookOpen event in the add-in.

How can I suppress the error generated when no workbook is opened when excel
opens and is loading my add-in with below code? The
Application.ActiveWorkbook.Name object is null and that's causing the error.

If InStr("History by Length", Application.ActiveWorkbook.Name) 0 Then
MsgBox "it is our file"
Call CreateMenu
End If

actually i've discovered my code in my previous post is failing even when i
open a workbook.

error is

Object variable or With block variable not set

what's wrong with that syntax?


"scott" wrote in message
...
I have an add-in loaded all the time that checks any opened workbook name
for a specific string. The below code does this except when excel is opened
with no workbook (i.e. from Programs Menu). This code resides in the
App_WorkbookOpen event in the add-in.

How can I suppress the error generated when no workbook is opened when
excel opens and is loading my add-in with below code? The
Application.ActiveWorkbook.Name object is null and that's causing the
error.

If InStr("History by Length", Application.ActiveWorkbook.Name) 0 Then
MsgBox "it is our file"
Call CreateMenu
End If

How about putting:

If Not ActiveWorkbook Is Nothing Then
...
End if

around your If InStr statement?

josh



"scott" wrote in message
...
actually i've discovered my code in my previous post is failing even when
i open a workbook.

error is

Object variable or With block variable not set

what's wrong with that syntax?


"scott" wrote in message
...
I have an add-in loaded all the time that checks any opened workbook name
for a specific string. The below code does this except when excel is
opened with no workbook (i.e. from Programs Menu). This code resides in
the App_WorkbookOpen event in the add-in.

How can I suppress the error generated when no workbook is opened when
excel opens and is loading my add-in with below code? The
Application.ActiveWorkbook.Name object is null and that's causing the
error.

If InStr("History by Length", Application.ActiveWorkbook.Name) 0
Then
MsgBox "it is our file"
Call CreateMenu
End If

If I open a file named "History by Length.xls", shouldn't my below code
fire?

If InStr("History by Length", Application.ActiveWorkbook.Name) 0
Then
MsgBox "it is our file"
Call CreateMenu
End If


"Josh Sale" <jsale@tril dot cod wrote in message
...
How about putting:

If Not ActiveWorkbook Is Nothing Then
...
End if

around your If InStr statement?

josh



"scott" wrote in message
...
actually i've discovered my code in my previous post is failing even when
i open a workbook.

error is

Object variable or With block variable not set

what's wrong with that syntax?


"scott" wrote in message
...
I have an add-in loaded all the time that checks any opened workbook name
for a specific string. The below code does this except when excel is
opened with no workbook (i.e. from Programs Menu). This code resides in
the App_WorkbookOpen event in the add-in.

How can I suppress the error generated when no workbook is opened when
excel opens and is loading my add-in with below code? The
Application.ActiveWorkbook.Name object is null and that's causing the
error.

If InStr("History by Length", Application.ActiveWorkbook.Name) 0
Then
MsgBox "it is our file"
Call CreateMenu
End If

I'm just trying to help you avoid the error you said you were getting ... I
haven't followed the whole thread.



"scott" wrote in message
...
If I open a file named "History by Length.xls", shouldn't my below code
fire?

If InStr("History by Length", Application.ActiveWorkbook.Name) 0
Then
MsgBox "it is our file"
Call CreateMenu
End If


"Josh Sale" <jsale@tril dot cod wrote in message
...
How about putting:

If Not ActiveWorkbook Is Nothing Then
...
End if

around your If InStr statement?

josh



"scott" wrote in message
...
actually i've discovered my code in my previous post is failing even
when i open a workbook.

error is

Object variable or With block variable not set

what's wrong with that syntax?


"scott" wrote in message
...
I have an add-in loaded all the time that checks any opened workbook
name for a specific string. The below code does this except when excel
is opened with no workbook (i.e. from Programs Menu). This code resides
in the App_WorkbookOpen event in the add-in.

How can I suppress the error generated when no workbook is opened when
excel opens and is loading my add-in with below code? The
Application.ActiveWorkbook.Name object is null and that's causing the
error.

If InStr("History by Length", Application.ActiveWorkbook.Name) 0
Then
MsgBox "it is our file"
Call CreateMenu
End If

If my workbook is named "My Book.xls" why doesn't the INSTR function work
below?

Dim bk As Workbook

For Each bk In Application.Workbooks

If InStr("My Book", bk.Name) 0 Then
MsgBox "it is our file"
End If

Next



"Josh Sale" <jsale@tril dot cod wrote in message
...
I'm just trying to help you avoid the error you said you were getting ...
I haven't followed the whole thread.



"scott" wrote in message
...
If I open a file named "History by Length.xls", shouldn't my below code
fire?

If InStr("History by Length", Application.ActiveWorkbook.Name) 0
Then
MsgBox "it is our file"
Call CreateMenu
End If


"Josh Sale" <jsale@tril dot cod wrote in message
...
How about putting:

If Not ActiveWorkbook Is Nothing Then
...
End if

around your If InStr statement?

josh



"scott" wrote in message
...
actually i've discovered my code in my previous post is failing even
when i open a workbook.

error is

Object variable or With block variable not set

what's wrong with that syntax?


"scott" wrote in message
...
I have an add-in loaded all the time that checks any opened workbook
name for a specific string. The below code does this except when excel
is opened with no workbook (i.e. from Programs Menu). This code resides
in the App_WorkbookOpen event in the add-in.

How can I suppress the error generated when no workbook is opened when
excel opens and is loading my add-in with below code? The
Application.ActiveWorkbook.Name object is null and that's causing the
error.

If InStr("History by Length", Application.ActiveWorkbook.Name) 0
Then
MsgBox "it is our file"
Call CreateMenu
End If

The below code works because I use the whole file name, but won't work if I
just use "Length" or any other part. Why won't INSTR find a word within a
bk.Name?



My problem is that parts of the file name may vary from month to month. Any
idea why INSTR acts this way?



CODE:



If InStr("FIN039 Product History by Length.xls", bk.Name) 0 Then

MsgBox "it is our file"

End If



"Simon Murphy" wrote in message
...
Scott
I think it may be an inconsistency depending on whether the book name has
any extended chars in it (like space) or not.
this seems to work through (adding the .xls)
Sub test()

Dim bk As Workbook

For Each bk In Application.Workbooks
Debug.Print bk.Name
If InStr("book test.xls", bk.Name) 0 Then
MsgBox "it is our file"
End If

Next

End Sub


cheers
Simon




"scott" wrote:

If my workbook is named "My Book.xls" why doesn't the INSTR function work
below?

Dim bk As Workbook

For Each bk In Application.Workbooks

If InStr("My Book", bk.Name) 0 Then
MsgBox "it is our file"
End If

Next



"Josh Sale" <jsale@tril dot cod wrote in message
...
I'm just trying to help you avoid the error you said you were getting
...
I haven't followed the whole thread.



"scott" wrote in message
...
If I open a file named "History by Length.xls", shouldn't my below
code
fire?

If InStr("History by Length", Application.ActiveWorkbook.Name)
0
Then
MsgBox "it is our file"
Call CreateMenu
End If


"Josh Sale" <jsale@tril dot cod wrote in message
...
How about putting:

If Not ActiveWorkbook Is Nothing Then
...
End if

around your If InStr statement?

josh



"scott" wrote in message
...
actually i've discovered my code in my previous post is failing even
when i open a workbook.

error is

Object variable or With block variable not set

what's wrong with that syntax?


"scott" wrote in message
...
I have an add-in loaded all the time that checks any opened workbook
name for a specific string. The below code does this except when
excel
is opened with no workbook (i.e. from Programs Menu). This code
resides
in the App_WorkbookOpen event in the add-in.

How can I suppress the error generated when no workbook is opened
when
excel opens and is loading my add-in with below code? The
Application.ActiveWorkbook.Name object is null and that's causing
the
error.

If InStr("History by Length", Application.ActiveWorkbook.Name)
0
Then
MsgBox "it is our file"
Call CreateMenu
End If

Arguments to instr are probably backwards and you should make it case
insensitive.

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
The below code works because I use the whole file name, but won't work if
I
just use "Length" or any other part. Why won't INSTR find a word within a
bk.Name?



My problem is that parts of the file name may vary from month to month.
Any
idea why INSTR acts this way?



CODE:



If InStr("FIN039 Product History by Length.xls", bk.Name) 0
Then

MsgBox "it is our file"

End If



"Simon Murphy" wrote in message
...
Scott
I think it may be an inconsistency depending on whether the book name
has
any extended chars in it (like space) or not.
this seems to work through (adding the .xls)
Sub test()

Dim bk As Workbook

For Each bk In Application.Workbooks
Debug.Print bk.Name
If InStr("book test.xls", bk.Name) 0 Then
MsgBox "it is our file"
End If

Next

End Sub


cheers
Simon




"scott" wrote:

If my workbook is named "My Book.xls" why doesn't the INSTR function
work
below?

Dim bk As Workbook

For Each bk In Application.Workbooks

If InStr("My Book", bk.Name) 0 Then
MsgBox "it is our file"
End If

Next



"Josh Sale" <jsale@tril dot cod wrote in message
...
I'm just trying to help you avoid the error you said you were getting
...
I haven't followed the whole thread.



"scott" wrote in message
...
If I open a file named "History by Length.xls", shouldn't my below
code
fire?

If InStr("History by Length",
Application.ActiveWorkbook.Name)
0
Then
MsgBox "it is our file"
Call CreateMenu
End If


"Josh Sale" <jsale@tril dot cod wrote in message
...
How about putting:

If Not ActiveWorkbook Is Nothing Then
...
End if

around your If InStr statement?

josh



"scott" wrote in message
...
actually i've discovered my code in my previous post is failing
even
when i open a workbook.

error is

Object variable or With block variable not set

what's wrong with that syntax?


"scott" wrote in message
...
I have an add-in loaded all the time that checks any opened
workbook
name for a specific string. The below code does this except when
excel
is opened with no workbook (i.e. from Programs Menu). This code
resides
in the App_WorkbookOpen event in the add-in.

How can I suppress the error generated when no workbook is opened
when
excel opens and is loading my add-in with below code? The
Application.ActiveWorkbook.Name object is null and that's causing
the
error.

If InStr("History by Length", Application.ActiveWorkbook.Name)

0
Then
MsgBox "it is our file"
Call CreateMenu
End If