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
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » VB Runtime error when opening excel
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.
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.
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
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.
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.
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.
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
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.
Notifications