First off, I'm using Excel 2019 on Windows 11.
I have created personal.xlsb and when I call a workbook named Test.xlsm I can see the personal.xlsb file. When I put a simple macro (shown below) in personal, it won't work. When I put it in Test.xlsm it works just fine.
Here's the code, which is in module1 in personal; it shows you what the active sheet name is when you change sheets. I'm calling it from ThisWorkbook's Workbook_SheetActivate event.
Sub ChangeMySheets()
MsgBox "Your new sheet is " & ActiveWorkbook.ActiveSheet.Name & "!", vbOKOnly
End Sub
I keep getting a "Sub or function not defined" error message. The problem isn't confined to this one macro. No matter what macros I put into personal.xlsb, I can't call them from Test.xlsm. They all worked fine when they were in a module in TEST.xlsm.
I've tried deleting personal.xlsb and recreating it several times. I've gone into "disabled Items" in Excel->File->Options->Addins->Manage->Disabled items.. but neither personal.xlsb or test.xlsm show as disabled. I've made the directories of Test.xlsm and Personal.xlsb "Trusted" locations. I've gone into \?View Code?\Tools\References and checked the ?Microsoft Visual Basic for References Extensibility 5.3? reference. I have syncing to OneDrive turned on, so I made that location a "Trusted" subdirectory. Then I turned syncing to OneDrive off and copied the Test.xlsm file down to my pc (didn't work). I even went to the "Macro Settings" section under "Trusted Locations" and set it to "Enable all macros" (yes, I know this isn't a good idea, but I wanted to see if Excel was blocking the macros).
I'm attaching (or at least, I'll try to) the Test.xlsm and its accompanying Personal.xlsb file -- please tell me if I failed.
At this point, I'm stumped. Anyone have any avenues I can investigate?
Bookmarks