Run VBA Code when opening Excel file aka Workbook Open - For all files PERSONAL.XLSB

Adis_1102

New Member
Joined
Oct 8, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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

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...
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,743
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Where exactly are you putting this code in the "PERSONAL.XLBS" file?
If you are putting it in the Workbook_Open event, like in the other files, it will only pertain to the opening of the "PERSONAL.XLBS" files, and not the other files being opened.

If you are opening the other files straight from Excel or from Windows Explorer, I do not know that you can get the code from the PERSONAL.XLBS file to automatically run against those files.
To run it automatically on those files, I believe you would need to have it in the Workbook_Open event of each of those files.

What might be a better option is to open up some file that has the code that you want, and add a button in that same file that they can use to browse and open up other Excel files. Then, you control that process, and have code run against it after opening the file.
 

Adis_1102

New Member
Joined
Oct 8, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the Board!

Where exactly are you putting this code in the "PERSONAL.XLBS" file?
If you are putting it in the Workbook_Open event, like in the other files, it will only pertain to the opening of the "PERSONAL.XLBS" files, and not the other files being opened.

If you are opening the other files straight from Excel or from Windows Explorer, I do not know that you can get the code from the PERSONAL.XLBS file to automatically run against those files.
To run it automatically on those files, I believe you would need to have it in the Workbook_Open event of each of those files.

What might be a better option is to open up some file that has the code that you want, and add a button in that same file that they can use to browse and open up other Excel files. Then, you control that process, and have code run against it after opening the file.

Thanks for the hints!

Yeah I do have it on Workbook_Open Event...I think I will need to create an Add-In for it...
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,743
Office Version
  1. 365
Platform
  1. Windows
You are welcome. Hope it works out for you.
Not sure how to make sure how you make sure that the code will run automatically (from the Add-In), if the code is not in the files you are opening, and you are not opening the file from within a file that has the code you need.

If you get it to work, please post back. I am very curious to see how this would work (and it might be helpful to others who want to try doing the same thing).
We like this forum to be used as a Reference tool, so that people can search old questions to see if their question is similar to any old ones already asked and answered.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,110
Messages
5,628,761
Members
416,337
Latest member
tl3phd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top