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:
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
Worksheets("wsA").Range("S1").Value = 232
Set NewBook = ActiveWorkbook
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")
Loop Until fName <> False
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!