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!
Rich5150Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Calvin BrineCommented:
What prompts the code to be executed?  Is it a worksheet event, workbook event, button driven macro, etc...
0
ExcelEOCommented:
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.
0
Rich5150Author Commented:
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

0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

ExcelEOCommented:
Where is it saved?  In a module?  Is the module part of the workbook?
0
Rich5150Author Commented:
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.
0
ExcelEOCommented:
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
0
Rich5150Author Commented:
Thanks. I don't disagree with you.  You're explanation makes sense. I'll check out the link.
0
broro183Commented:
hi,

Does the issue still ocur with either of these slightly modified versions of the code?

Option Explicit

Sub SaveFileAs_v2()
'change as desired
Const iniPathName As String = vbNullString
Dim NewBook As Workbook
Dim fName As Variant

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

Set NewBook = Nothing
End Sub


Sub SaveFileAs_v3()
  
'change as desired
Const iniPathName As String = vbNullString
Dim NewBook As Workbook
Dim fName As Variant

Set NewBook = ThisWorkbook
  
If NewBook.Name = "Testbook.xlsm" Then
    Call ResetPricing
    Call NormalPrice
    NewBook.Worksheets("wsA").Range("S1").Value = 232
    Do
        fName = Application.GetSaveAsFilename(InitialFileName:=iniPathName, fileFilter:="Excel Files (*.xlsm), *.xlsm", Title:="Testbook - Save File")
        If fName = False Then
            MsgBox "No file name given: please provide a file name or you will loop in this code forever!", , "Testbook Alert"
        Else
            NewBook.SaveAs Filename:=fName
        End If
    Loop Until fName <> False
Else
End If

Set NewBook = Nothing
End Sub

Open in new window


hth
Rob
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rich5150Author Commented:
I'll test and get back to you. Thanks.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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.
0
Rich5150Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.