Link to home
Start Free TrialLog in
Avatar of Rich5150
Rich5150Flag for United States of America

asked on

Object variable error on first use of workbook

I have a procedure that prompts the user to save the file under a different name when the workbook is opened.  It's worked well for a long time but I just discovered the following occurrence that creates a runtime error 91 (Object variable or With block variable not set).  

The file was emailed to a user, who then saved this file to her desktop.  She then opened the file from her desktop - file opens - she is prompted to enable editing.  Upon enabling editing she receives this runtime error, with the debugger going to the following code and the IF statement highlighted:
Sub SaveFileAs()

Dim PathName As String
Dim Drive As String
Dim Loc As String
Dim NewBook As Variant
Dim Ans As String
Dim fName As Variant

If ActiveWorkbook.Name = "Testbook.xlsm" Then
    Call ResetPricing
    Call NormalPrice
    Worksheets("wsA").Range("S1").Value = 232
    Set NewBook = ActiveWorkbook
    Do
        fName = Application.GetSaveAsFilename(InitialFileName:=PathName, fileFilter:="Excel Files (*.xlsm), *.xlsm", Title:="Testbook - Save File")
        If fName = False Then
            Ans = MsgBox("File NOT saved: Save this file later using Save As...", , "Testbook Alert")
            Exit Sub
        End If
        Loop Until fName <> False
        NewBook.SaveAs Filename:=fName
Else
End If
End Sub

Open in new window


The strange thing is that this only occurs the first time she tries to run the file after it has been saved from the email.  The next time she opens it, no error is received.

The other odd thing is that initially I couldn't replicate this error, and then figured out that one difference is that this user had her trust settings to enable macros for all files, whereas mine was set to disable with notification, which caused me to have to both enable editing and then enable macros, after which I did not receive the error.  However, if I changed my trust setting to enable macros for all files, then the error was received after enabling editing, just as it did on the user's computer.

I don't understand the reason why you would get this error in one scenario and not the other, but I suspect that the issue has something to do with needing to make a variable declaration for ActiveWorkbook.  I appreciate any direction you can give for this.  Thanks!
Avatar of Calvin Brine
Calvin Brine
Flag of Canada image

What prompts the code to be executed?  Is it a worksheet event, workbook event, button driven macro, etc...
It is hard to say without knowing more about the application but I think the code that calls the ActiveWorkBook may be calling it before any workbook is loaded.    You can replicate the same thing yourself easily.  Just open blank excel sheet.  Close the book1 and insert a macro to an add-in visible in VB that looks for a active workbook.
Avatar of Rich5150

ASKER

Code is below - it is a workbook open event that calls a the OpenCheck macro, which calls the SaveFileAs macro.
Private Sub Workbook_Open()

    Call OpenCheck

    Call MyFullName
      
    Sheets("Gen").Select
    Range("G7").Select
    OpeningMenu.Show
    
End Sub

Sub OpenCheck()

    Dim Today As Date
    Dim Expire As Date

    Today = Now()
    Expire = #9/30/2012#
    
    If Today > Expire Then
    Ans = MsgBox("This version of Testbook has expired - please contact Marketing for the latest copy", , "Testbook Alert")
    
    Else
    Call SaveFileAs
    End If
End Sub

Open in new window

Where is it saved?  In a module?  Is the module part of the workbook?
The procedure just calls up the Save As dialogue box and allows the user to choose both the name to save the file under and the location.  

I agree with ExcelEO that it looks like it isn't recognizing the active workbook, but I don't understand why this error only occurs the very first time that the file is opened after it is placed on the hard drive, and only if the user has the trust setting to enable macros on all files.  

I've tested this, and if the trust setting is to disable macros with notification, then after the user opens the file for the first time, and then enables editing and enables macros, then no error is received.  If the user has previously set the trust setting to enable macros, the user doesn't get the prompt to enable macros and the error is received immediately after editing is enabled.

ExcelEO - thanks for your feedback.  I've never worked with add-ins before, but I assume that this would be something that only resides on my laptop, which doesn't help me when distributing this file to other users.  Or maybe I'm not following what it is that you are suggesting.
Sorry, I am not so helpful but I think you may like to take a look at this forum

http://www.pcreview.co.uk/forums/auto_open-vs-workbook_open-t953960.html

Also, it may be that when the macros are all enabled, there is no pause for the workbook to fire up properly.  And the automatic event can't find the activeworkbook immediately.  But this is just my conjecture and I couldn't repeat the error myself.  Also I just realized that I only have excel 2007.

I hope this helps
Thanks. I don't disagree with you.  You're explanation makes sense. I'll check out the link.
ASKER CERTIFIED SOLUTION
Avatar of Rob Brockett
Rob Brockett
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'll test and get back to you. Thanks.
I've requested that this question be closed as follows:

Accepted answer: 500 points for broro183's comment http:/Q_27475986.html#37232535

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
My apologies for the lack of follow-up.  Holidays and vacations got in the way.  I'd appreciate it if I could have through January 20th to get back to this and see if it can be successfully resolved.  Thanks to everyone on this forum for their support.