Object variable or With block variable not set Error Message

[Nutterhead]

I have this bit of code for a sheet I'm having an issue with. The code is in a template and then the template is saved with a different name. The part of the code in italics ran fine by itself but then when I went to add code to exit the sub IF the workbook name is not equal to Billing Template.xlsm I ran into issues. Below is the code and I'm not sure what I'm missing or have wrong.

Private Sub Workbook_Open()

If ActiveWorkbook.Name <> "Billing Template.xlsm" Then
Exit Sub
Else

Dim ans As String
ans = MsgBox("Starting a new days report?", vbYesNo)
If ans = vbNo Then

Exit Sub

Else
Worksheets("billingtemplate").Range("A2:I100").ClearContents
Worksheets("billingtemplate").Range("M5").ClearContents
Worksheets("billingtemplate").Range("M6").ClearContents

End If

End If

End Sub

 

[Beyond_avarice]

Works for me.

Private Sub Workbook_Open()
 

If ActiveWorkbook.Name <> "Billing Template.xlsm" Then
        Exit Sub
    ElseIf MsgBox("Starting a new days report?", vbYesNo) = vbNo Then
        Exit Sub
        Else:   Worksheets("billingtemplate").Range("A2:I100", "M5:M6").ClearContents
               
End If

End Sub

 

[Greg Truby]

The code is in a template and then the template is saved with a different name. The part of the code in italics ran fine by itself but then when I went to add code to exit the sub IF the workbook name is not equal to Billing Template.xlsm I ran into issues.

You appear to have answered your own question. The file is saved under a different name, hence the IF statement will evaluate to TRUE and EXITs the procedure.

If the workbook is really a [macro-enabled] TEMPLATE (and you're not just calling it that), then it will end in an XLTM extension, not an XLSM extension. If it ends in XLSM, then it's not a template. It's just a garden variety macro-enabled workbook. If it is indeed a template then the workbooks are created using a NEW command and they'll have a name (without extension) that is auto-generated based on the name of the template, i.e. Billing Template1, Billing Template2, ...​ at the time they are created (when the new workbook's OPEN event would be raised), so if this really is a template, your code would never run.

 

[Nutterhead]

I copied that and pasted it as is and I'm still getting the same error. Then I thought maybe it's cause when I open the book I get the Enable Editing message. I'm working in SharePoint and I have been going in and out of the sheet via explorer. I found if I open it via SharePoint I don't get the error message.

 

[Beyond_avarice]

I don't have access to SharePoint, but if I recall; your open file name is now an "HTTP" construct. You will need to find the string "InStr" of your file name or use "Like".

 

[Nutterhead]

You appear to have answered your own question. The file is saved under a different name, hence the IF statement will evaluate to TRUE and EXITs the procedure.

If the workbook is really a [macro-enabled] TEMPLATE (and you're not just calling it that), then it will end in an XLTM extension, not an XLSM extension. If it ends in XLSM, then it's not a template. It's just a garden variety macro-enabled workbook. If it is indeed a template then the workbooks are created using a NEW command and they'll have a name (without extension) that is auto-generated based on the name of the template, i.e. Billing Template1, Billing Template2, ...​ at the time they are created (when the new workbook's OPEN event would be raised), so if this really is a template, your code would never run.

It's not specifically a template, that is an error in my wording, it's a blank sheet saved with formatting and information that we use daily that we go into each day and do a SAVE AS to create the current days sheet.

 

[Nutterhead]

I don't have access to SharePoint, but if I recall; your open file name is now an "HTTP" construct. You will need to find the string "InStr" of your file name or use "Like".

I'm a little lost with this as I'm self taught VBA. If I hope the sheet on the SharePoint site the Workbook_Open code works with no issues and everyone using it will use it this way so I am ok. I know we do have another VBA code to save the file with the date to SharePoint and a shared drive and it does have InStr in it, it's below for reference but it's a different macro than the one I asked about.

If ActiveWorkbook.Name = "Billing Template.xlsm" Then
Dim ans As String
ans = MsgBox("Final Save Before Upload?", vbYesNo)
If ans = vbNo Then

ThisWorkbook.Save
MsgBox ("Your data has been saved."), vbInformation
ActiveWorkbook.Close

Else
If IsEmpty(ActiveSheet.Range("M5")) Then
MsgBox ("Must Have Input Name")
Exit Sub
Else
ChDir [Removed Path for discretion]
ActiveWorkbook.SaveAs Filename:= _
"[Removed Path for discretion] Billing Upload\billingupload.csv", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveWorkbook.Save
Dim dtDate As Date
dtDate = Date
Dim strFile As String
strFile = "[Removed Path for discretion] Billing/09 - Sept/Manual Billing " & Format(dtDate, "mm-dd-yyyy") & ".xlsm"
ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

MsgBox ("Your data has been saved."), vbInformation
ActiveWorkbook.Close
End If
End If
End If

End Sub

 

[Greg Truby]

I'd start with just the basics to make sure your logic is flowing like you expect.

Private Sub Workbook_Open()    
    If LCase(Me.Name) <> "billing template.xlsm" Then Exit Sub
    If vbNo = MsgBox("Starting a new day's report?", vbYesNo + vbQuestion) Then Exit Sub
    
    '// code to do this or that
    MsgBox "I should be doing something very exciting!", vbExclamation
    
End Sub

Test is under various scenarios and see if it behaves like you're expecting. Then add the other lines of code after you're sure this part is working how you want it to.

EDIT - forgot to mention. It's bad voodoo to declare a variable inside an IF statement (use a DIM statement inside an IF). And in your case you needn't preserve the user response anyway, so you didn't need to declare ans at all which is why you don't see it above.