Hello community,
I have a code that I would like to run when an excel file is being opened. To stress it out - I would like to run the code when any excel file is run, not a specific one (nevermind what the code does)
So, I need to do two main things:
1.: Insert the code in Private Sub Workbook_Open() - to run it on the opening worksheet event
2.: Insert the code in PERSONAL.XLBS - so that it will run automatically when opening any excel file
Firstly, I put the following code in one specific excel file. Works as intented - when I open this file and activate content, the code is run - all good
Secondly, I put this code in the PERSONAL.XLBS.
When I open any excel file - I get the runtime error 91: Object variable not set (Error 91). This happens on the following codeline:
Set Default = ActiveWorkbook.BuiltinDocumentProperties
I do not understand why it works on a specific file but not on all files, i.e. in PERSONAL.XLBS...
I have a code that I would like to run when an excel file is being opened. To stress it out - I would like to run the code when any excel file is run, not a specific one (nevermind what the code does)
So, I need to do two main things:
1.: Insert the code in Private Sub Workbook_Open() - to run it on the opening worksheet event
2.: Insert the code in PERSONAL.XLBS - so that it will run automatically when opening any excel file
Firstly, I put the following code in one specific excel file. Works as intented - when I open this file and activate content, the code is run - all good
VBA Code:
Private Sub Workbook_Open()
Dim i As Integer
Dim Default As Object
Set Default = ActiveWorkbook.BuiltinDocumentProperties
Dim Name As Variant
Dim Value As Variant
On Error GoTo BadValue
Range("A1") = "Built in Document Properties"
For i = 1 To Default.Count
Name = Default.Item(i).Name & ": "
Value = Default.Item(i).Value
Range("A" & i + 1) = Name
Range("B" & i + 1) = Value
Next
BadValue:
Value = "Bad Value"
Resume Next
End Sub
Secondly, I put this code in the PERSONAL.XLBS.
When I open any excel file - I get the runtime error 91: Object variable not set (Error 91). This happens on the following codeline:
Set Default = ActiveWorkbook.BuiltinDocumentProperties
I do not understand why it works on a specific file but not on all files, i.e. in PERSONAL.XLBS...