I have a set of macros (I’ll list them below) that function properly, but every time I open a sheet with them I get several popup errors I have to click through in order to begin using the sheet. They say “basic runtime error: object variable not set”.
I’ve googled extensively and asked chatGPT to no avail. Because the sheets are functioning properly, i.e. the macros seem to be working normally, is there a way to just turn off the error reporting? The 2 lines at the beginning of each macro was a suggestion from chatGPT that didn’t work.
Function CurrentSheetName
On Error Resume Next
On Error GoTo 0
CurrentSheetName = ThisComponent.CurrentController.ActiveSheet.Name
End Function
Sub GoToSheetBasedOnDropdown
On Error Resume Next
On Error GoTo 0
Dim oSheet As Object
Dim oCell As Object
Dim sSheetName As String
' Assuming the dropdown is in cell A5 on the "Main" sheet
oCell = ThisComponent.Sheets.getByName("Main").getCellRangeByName("b5")
sSheetName = oCell.String
' Check if the sheet exists
oSheet = ThisComponent.Sheets.getByName(sSheetName)
If Not IsEmpty(oSheet) Then
' Go to the sheet
ThisComponent.CurrentController.setActiveSheet(oSheet)
Else
MsgBox "The sheet named " & sSheetName & " does not exist.", 64, "Error"
End If
End Sub
Sub GoToSheetBasedOnDropdown2
On Error Resume Next
On Error GoTo 0
On Error Resume Next
On Error GoTo 0
Dim oSheet As Object
Dim oCell As Object
Dim sSheetName As String
' Assuming the dropdown is in cell A5 on the "Main" sheet
oCell = ThisComponent.Sheets.getByName("Main").getCellRangeByName("b13")
sSheetName = oCell.String
' Check if the sheet exists
oSheet = ThisComponent.Sheets.getByName(sSheetName)
If Not IsEmpty(oSheet) Then
' Go to the sheet
ThisComponent.CurrentController.setActiveSheet(oSheet)
Else
MsgBox "The sheet named " & sSheetName & " does not exist.", 64, "Error"
End If
End Sub
Sub GoToSheetBasedOnDropdown3
On Error Resume Next
On Error GoTo 0
Dim oSheet As Object
Dim oCell As Object
Dim sSheetName As String
' Assuming the dropdown is in cell A5 on the "Main" sheet
oCell = ThisComponent.Sheets.getByName("Main").getCellRangeByName("b21")
sSheetName = oCell.String
' Check if the sheet exists
oSheet = ThisComponent.Sheets.getByName(sSheetName)
If Not IsEmpty(oSheet) Then
' Go to the sheet
ThisComponent.CurrentController.setActiveSheet(oSheet)
Else
MsgBox "The sheet named " & sSheetName & " does not exist.", 64, "Error"
End If
End Sub
Sub RefreshSheetNumberForAllCheckBoxReferenceToTheActualSheet
On Error Resume Next
On Error GoTo 0
dim oDoc as object
dim oSheet as object
dim oDrawpage as object
dim oShape as object
dim oControl as object
dim oValueBinding as object
dim oNewCVB as object
Dim oLinkedCell as new com.sun.star.table.CellAddress
Dim oNamedValue as new com.sun.star.beans.NamedValue
dim aBoundCell as variant
dim i as integer
dim iCount as integer
dim iSheetNumber as integer
dim iColumn as integer
dim iRow as integer
dim iSheet as integer
oDoc = Thiscomponent
oSheet = oDoc.getcurrentcontroller.activesheet
iSheetNumber = oSheet.getRangeAddress.Sheet
oDrawpage = oSheet.Drawpage
iCount = oDrawPage.Count
For i = 0 to iCount - 1
oShape = oDrawPage.getByIndex(i)
If oShape.supportsService("com.sun.star.drawing.ControlShape") Then
oControl = oShape.getControl()
If oControl.supportsService("com.sun.star.awt.UnoControlCheckBoxModel") Then
oValueBinding = oControl.getValueBinding()
aBoundCell = oValueBinding.BoundCell
iColumn = aBoundCell.Column
iRow = aBoundCell.Row
iSheet = aBoundCell.Sheet
oLinkedCell.Sheet = iSheetNumber
oLinkedCell.Column = iColumn
oLinkedCell.Row = iRow
oNamedValue.Name = "BoundCell"
oNamedValue.Value = oLinkedCell
oNewCVB = oDoc.createInstance("com.sun.star.table.CellValueBinding")
oNewCVB.Initialize(Array(oNamedValue))
oControl.setValueBinding(oNewCVB)
EndIf
EndIf
next i
End Sub