Object variable or With block variable error
 

I'm not sure if this will solve your problem, but I would not use the
New keyword in the variable declaration. Better do a Set AppClass = New
EventClass. Also, the rng.Select in the Event Module seems to be
redundant, because you work on the range, not on the selection.

Regards,
Steve
www.stkomp.com

Tony Logan wrote:
I'm getting an error I can't explain.

The code I have is supposed to run whenever a workbook is opened. The active
sheet is searched for a particular font. If the font is found, the user gets
a message box.

This works fine as long as I already have Excel running.

However, if Excel is not running and I try opening an Excel file from
Windows Explorer, for instance, I get a run-time error "Object variable or
With block variable not set" at the first line of code.

One other bit of info that may or may not be useful: after this error
occurs, the code no longer works, no matter how many additional files I open.
I have to close out of Excel and then restart it to get the code working
again.

I'm running Excel 2003 on Windows XP with SP2. The code is stored in an
add-in (.xla file) I keep in the XLSTART folder.

Here's the code in the EventClass Class Module:

Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)

Dim c As Variant
Dim rng As Range

Set rng = Excel.ActiveSheet.UsedRange
rng.Select
For Each c In rng
If c.Font.Name Like "Arial" Then
MsgBox "found one"
Range("A1").Select
Exit Sub 'if we find just one occurrence, we're done
End If
Next

End Sub


And here's the code in the This Workbook module:

Option Explicit

Dim AppClass As New EventClass

Private Sub Workbook_Open()
Set AppClass.App = Application
End Sub