none
Personal VBA does not always open when opening existing files??? RRS feed

  • Question

  • Good Morning.

    I am using Excel 2010 and have created some macros in a personal workbook. When I open a NEW excel document the personal workbook opens and is available and when I open an existing document that is macro enabled the personal workbook is available, after I close that doc. and I open a non macro enabled excel doc the personal vba workbook is viewable. But when I close the non macro enabled doc and open a macro enabled doc the personal vba workbook is not there. Now, if I close the desktop folder where I have all my excel docs. completely then reopen it the and find the folder I want and open the macro enable doc. the personal vba workbook is there again. So, my question is why am I forced to go thru these extra steps, and why is it not opening in every doc.?

    Thanks for you help,

    Adam

    Thursday, April 6, 2017 1:27 PM

All replies

  • Hi Swiss,
     
    Open Excel, then when Personal.xlsb is visible, go to the View tab and click
    the Hide button. Now close all Windows of Excel and when prompted, click Yes
    (or Save) to save the personal macro workbook.
     
     
    Thursday, April 6, 2017 2:02 PM
  • Thanks for your reply Jan. I followed your instructions but the problem still persists. The Personal.xlsb will open when I open Excel from the windows button and all Excel documents on my desktop and it will open from all the Excel documents inside a desktop folder named Swiss Parts Folder until open a non macro enabled Excel document in that folder and close it. When I close that document another Excel window is on the screen with a File in Use dialog box reading...

    PERSONAL.XLSB is locked for editing

    by 'my name'.

    Open 'Read-Only' or click 'Notify' to open read-only and receive notification when the document is no longer in use.

    With the "Read Only", "Notify" and "Cancel" buttons to the right.

    No matter what button I click the Personal.xlsb wont appear until I close the Swiss Parts Folder and re-open it.

    Thanks,

    Adam

    Thursday, April 6, 2017 2:22 PM
  • Hi Swiss,
     
    Odd. Does personal.xlsb contain any event code or application classes?
     
     
    Thursday, April 6, 2017 3:55 PM
  • I am not sure how to find that info.
    Thursday, April 6, 2017 4:07 PM
  • Hi Swiss Programmer NC,

    Try to open VBE from the "Developer" Tab or just press Alt + F11 key.

    then you can try to check ThisWorkbook or Sheet module or any other module for code and events.

    also try to check for AutoExec Macro.

    whether it have some code that get execute each time when you open Excel.

    I also want to confirm with you, that how you open Personal.xlsb file? you mentioned that this file is opened with every documents. so do you open this file manually or with code?

    what is the purpose to open this file with every document?

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, April 7, 2017 1:38 AM
    Moderator
  • Hi Swiss,
     
    Given that you do not know where to find that information I think you don't
    have any event code in there (because you would have had put it in the file
    yourself). I would try this -taking deep breath- :
     
    - Open an empty Excel file
     
    - Open the VBA editor (alt+F11)
     
    - Drag each module from personal.xlsb to the new excel file (in the project
    explorer, View, Project explorer from the VBE menu)
     
    - don't forget the check the ThisWOrkbook module, you can only manually copy
    all code in that module into the other workbook's ThisWorkbook module
     
    - Save the new file as an Excel file with macro's
     
    - Close Excel
     
    - Open Windows explorer
     
    - Type this into the address bar:
    %appdata%\microsoft\excel\xlstart
     
    - Delete (or move) the personal.xlsb
     
    - Open Excel
     
    - Record a dummy macro, make sure to record it in the personal macro workbook
     
    - Stop recording, close Excel, Save the personal.xlsb
     
    - Now re-open the new workbook with the copied code and copy its modules back
    to your brand-new personal.xlsb
     
     
    Friday, April 7, 2017 9:50 AM