VB Runtime error when opening excel

Topic

New Reply

When some of our users open excel they get the following message;

Run-time error ’91’:
Object variable or With block variable not set

Any ideas what would cause this??

Cheers

Lee

Reply | Quote

Replies

Hi Lee,
Do these users have any workbooks that open automatically when they start Excel (they’ll be in the XLSTART directory) such as a personal macro workbook? It sounds like there’s code running automatically but the code has an error in it. I guess it could also be a faulty add-in though that’s probably less likely.
Hope that helps.

Reply | Quote

Excellent it was exactly that. I removed a file called pdfwriter.xls from that directory and it fixed the problem.

Any ideas how I can find out why this file was causing a problem?

Thanks

Lee

Reply | Quote

When you get the error, is there a button the bottom of the message box labeled Debug? If there is, pressing that button should show the line of code causing the error. If not, then that workbook probably came from a vendor that has the code locked down so that you can not see it. From the name, it sounds like a file to create Adobe Acrobat .PDF files from Excel. However, that would not necessarily have come from Adobe. Maybe someone else will recognize the file name and tell you what vendor to contact.

Reply | Quote

When i click the debug button the follwing chunk of code is displayed. The line starting “our index” is where the arrow is pointing to.

If Not found Then
‘ Add the item to the File menu
Set filePrintItem = fileMenu.FindControl(Type:=msoControlButton, Id:=4, _
Recursive:=True)
ourIndex = filePrintItem.Index + 1
Set createPDFItem = fileMenu.Controls.Add(Type:=msoControlButton, _
Before:=ourIndex, Temporary:=True)
createPDFItem.Caption = “Create Adobe PDF…”
createPDFItem.OnAction = “PrintPDFFile”
createPDFItem.Tag = “CreateAdobePDF”
End If

Reply | Quote

For those users who are getting the error, check the File menu in Excel and see if Print… is still on there – if they’ve moved it (e.g. onto one of the main toolbars, then you’ll get that error. The code in the add-in tries to place a control just before the print item on the File menu and causes an error if it’s not there. Simple solution is to copy the Print… item back to the File menu (you can still leave a copy wherever the user wanted it).
Hope that helps.

Reply | Quote

I’ve just checked the machines in question.
The print section of the file menu has the following entries;

Print Area
Page Setup
Print
Print Preview

Reply | Quote

Curious. Is it the Print… item with Ctrl+P listed as the shortcut rather than just Print (i.e. the one that brings up the Print dialog box rather than the one that sends straight to the printer)? The error implies that it can’t find the control with ID = 4 (the Print… control) on the File menu.

Reply | Quote

It is print with ctrl+p and it does bring up the print dialogue box

Reply | Quote

Lee,
Try running these macros in Excel:

Sub FindID4()
    Dim mnuFile As CommandBar
    Dim itmFilePrint As CommandBarControl
    Dim itmMenuItem, blnItemFound As Boolean
    Set mnuFile = CommandBars("File")
    For Each itmMenuItem In mnuFile.Controls
        If itmMenuItem.ID = 4 Then
            MsgBox "Found ID 4 at index " & itmMenuItem.Index
            blnItemFound = True
            Exit For
        End If
    Next 'itmmenuitem
    If Not blnItemFound Then MsgBox "Control with ID 4 does not exist on File menu."
End Sub
Sub ListIDs()
    Dim mnuFile As CommandBar
    Dim itmFilePrint As CommandBarControl
    Dim itmMenuItem, blnItemFound As Boolean
    Set mnuFile = CommandBars("File")
    For Each itmMenuItem In mnuFile.Controls
        With itmMenuItem
            MsgBox .Caption & " has ID " & .ID
        End With
    Next 'itmmenuitem
End Sub

The first one will tell you if a control with ID 4 exists on the file menu, the second one will display the Caption and ID of each control on the File menu so you can check what the ID of your print control is (if it’s not 4).
Hope that helps.

Reply | Quote

Rory,

Id 4 doesn’t exist on the file menu. The print id’s are as follows;

Print Area – ID 30255
&Print… CTRL+P – ID 101
Print Preview – ID 1

Is id 4 the default for the print command on the file menu?
Does the results mean that if I change the code to check for id 101 I will stop getting the runtime error?

Cheers

Reply | Quote

Yes – just change ID:=4 to ID:=101 on those machines (assuming they’re all the same) and the error should go away.
Hope that helps.

Reply | Quote

Thanks

Much appreciated!

Reply | Quote

Was it definitely an .xls extension rather than .xla? Adobe does provide an add-in called pdfwriter.xla, which I have on my PC here but it doesn’t cause me an error. Do the users who don’t get that error have anything similar in their XLSTART directory?

Reply | Quote

It is an xla extension, it gets put there when you install the software to run a digital sender.
I’ll try and get hold of a copy of the file from a pc that doesn’t have the error.

Reply | Quote