• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

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!
0
Rich5150
Asked:
Rich5150
1 Solution
 
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now