I recently moved a Excel vba project from my personal computer to my work computer. This project is meant to connect and print using a Brother Printer. I keep getting a Run-time error '91': Object variable or With block variable not set. This impeads me being able to print anything using my work computer. But when I connect and use the vba on my personal computer it seems to run fine and the error does not occur. Though when stepping through the code I don't see anything in the Locals window that would indicate the issue.
Dim CreateLabel As VbMsgBoxResult
Dim labelInfo As String
Dim sPath As String
Dim ObjDoc As Object
' Step 1: Ask the user if they wish to create a label
CreateLabel = MsgBox("Do you wish to create a label?", vbYesNo)
If CreateLabel = vbYes Then
' Step 2: Prompt for each parameter
Dim licensePlate As String
Dim partNumber As String
Dim Description As String
Dim expirationDate As String
Dim validInput As Boolean
Dim userInput As String
' Step 2a: Enter SupplierPlateID with exit option
'Do
'userInput = InputBox("Enter SupplierPlateID" & vbNewLine & _
"Press 'Cancel' to terminate the process.")
'If userInput = "" Then Exit Sub
'licensePlate = userInput
licensePlate = InputBox("Enter License Plate:")
'validInput = ValidateSupplierPlateID(licensePlate)
'If Not validInput Then
' MsgBox "Invalid SupplierPlateID."
'End If
'Loop While Not validInput
' Step 2b: Enter Part Number with exit option
Do
userInput = InputBox("Enter Part Number (must have two '-' and end with '-875')" & vbNewLine & _
"Press 'Cancel' to terminate the process.")
If userInput = "" Then Exit Sub
partNumber = userInput
validInput = ValidatePartNumber(partNumber)
If Not validInput Then
MsgBox "Invalid Part Number. It must have two '-' and end with '-875'."
End If
Loop While Not validInput
Description = InputBox("Enter Description:")
' Step 2c: Enter Expiration Date with exit option
expirationDate = InputBox("Enter Experation Date (YYYY-MM-DD)" & vbNewLine & _
"Press 'Cancel' to terminate the process.")
' Step 3: Validate and display entered information
labelInfo = licensePlate & " | " & partNumber & " | " & Description & " | " & expirationDate
confirmMsg = "Is the following information correct?" & vbCrLf & vbCrLf & labelInfo
confirmed = MsgBox(confirmMsg, vbYesNo) = vbYes
'Update the label's caption with the user-created string in the Userform
UserForm1.Label3.Caption = labelInfo
' Step 4: Allow correction or confirmation
If Not confirmed Then
' Provide an option to go through the steps again if the information was incorrect
Call CreateAndPrintLabel
Exit Sub
End If
' Store labelInfo in cell B12 on Sheet1
ThisWorkbook.Sheets("Sheet1").Range("B12").Value = labelInfo
' Step 5: Confirm if user wants to print
printLabel = MsgBox("Do you wish to print the label?", vbYesNo)
If printLabel = vbNo Then
Exit Sub ' Exit the macro if user selects "No" for printing
End If
' Step 5.5: Print the label using Brother printer
Set ObjDoc = CreateObject("bpac.Document")
sPath = "C:\Users\55455.buff\Documents\My Labels\Matrix.lbx"
' Open lbx file
If ObjDoc.Open(sPath) Then
' Set text for the entire label
ObjDoc.SetText 0, dataMatrixContent
ObjDoc.GetObject("dataMatrix").text = labelInfo
' Print the label
ObjDoc.StartPrint "", bpoDefault
ObjDoc.PrintOut 1, bpoDefault
ObjDoc.EndPrint
' Close lbx file
ObjDoc.Close
Else
MsgBox "Failed to open label file."
End If
Else
MsgBox "ActiveX Data Matrix control not found on the sheet."
End If
'End If
End Sub
Function ValidatePartNumber(partNumber As String) As Boolean
If CountCharacter(partNumber, "-") = 2 And Right(partNumber, 4) = "-875" Then
ValidatePartNumber = True
Else
ValidatePartNumber = False
End If
End Function
Function CountCharacter(ByVal text As String, ByVal character As String) As Long
CountCharacter = (Len(text) - Len(Replace(text, character, ""))) / Len(character)
End Function
Function IsValidExpirationDate(expirationDate As String) As Boolean
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "^\d{4}-\d{2}-\d{2}$"
If regex.Test(expirationDate) Then
Dim currentDate As Date
currentDate = Date
Dim futureDate As Date
futureDate = DateAdd("yyyy", 6, currentDate)
If DateValue(expirationDate) <= futureDate Then
IsValidExpirationDate = True
End If
End If
End Function
Function ValidateSupplierPlateID(supplierPlateID As String) As Boolean
If Len(supplierPlateID) = 8 And Left(supplierPlateID, 2) = "PC" Then
ValidateSupplierPlateID = True
Else
ValidateSupplierPlateID = False
End If
End Function
Sub Print_Label()
Dim bRet As Boolean
Dim sPath As String
Dim ObjDoc As bpac.Document
Set ObjDoc = CreateObject("bpac.Document")
sPath = "C:\Users\giovanni.fontanetta\Documents\My Labels\Matrix.lbx"
'Open lbx file
bRet = ObjDoc.Open(sPath)
If (bRet <> False) Then
' Start Print-Setting
ObjDoc.StartPrint "", bpoDefault
' Ask the user how many copies to print
Dim numCopies As Integer
numCopies = InputBox("Enter the number of copies to print:", "Number of Copies", 1)
For i = 1 To numCopies
ObjDoc.PrintOut 1, bpoDefault
Next i
' Finish Print-Setting and start the printing
ObjDoc.EndPrint
' Close lbx file
ObjDoc.Close
End If
End Sub
I suspect that below line is causing the issue possibly due to incorrect installation of brother printer driver or related software on your work computer.
You can possibly put some Debug.print or Msgbox statements at several places in the code to narrow down and understand which portion of the code gives the error.