+ Reply to Thread
Results 1 to 4 of 4

Can't use macros in personal.xlsb workbook

  1. #1
    Registered User
    Join Date
    02-01-2023
    Location
    U.S.A.
    MS-Off Ver
    2019
    Posts
    2

    Can't use macros in personal.xlsb workbook

    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?
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,618

    Re: Can't use macros in personal.xlsb workbook

    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    02-01-2023
    Location
    U.S.A.
    MS-Off Ver
    2019
    Posts
    2

    Re: Can't use macros in personal.xlsb workbook

    Thank you so much -- that really did the trick (and kept me from going bonkers)!! Once I made your change (and used Personal.xlsb instead of just Personal) your fix worked like a charm!

    Go protonLeah!! And is there any way I can give you the equivalent of a gold star (I'm new to the forum)?
    Last edited by glencora0; 02-12-2023 at 04:13 AM.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,618

    Re: Can't use macros in personal.xlsb workbook

    Add Reputation button @ lower left just above the quick reply window.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Keeping ALL macros in Personal.xlsb ?
    By terrypin in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 01-06-2020, 10:20 AM
  2. Macros in Personal.xlsb
    By prestone442 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-05-2018, 07:13 PM
  3. Replies: 0
    Last Post: 01-15-2015, 05:53 AM
  4. [SOLVED] Copied personal.xlsb to another computer, macros won't execute
    By Rick K in forum Excel General
    Replies: 3
    Last Post: 10-27-2014, 10:19 AM
  5. Can't access macros in personal.xlsb workbook from macros in a second workbook
    By JimFM in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-24-2013, 03:35 PM
  6. Exported Ribbon won't run macros from PERSONAL.XLSB
    By Sicrates in forum Excel General
    Replies: 1
    Last Post: 06-26-2012, 04:29 AM
  7. Macros in Personal.xlsb not available when Excel is opened from another app
    By Muskett32 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-15-2012, 02:57 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1