+ Reply to Thread
Results 1 to 9 of 9

VBA Macro only works when a certain excel file is open

  1. #1
    Registered User
    Join Date
    07-09-2020
    Location
    Dallas
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    34

    VBA Macro only works when a certain excel file is open

    Hello all,

    As the title explains, I have VBA macro that a buddy of mine sent me. However, the macros only work when that certain excel file is open. I have dragged the modules to my personal.xlsb where my other macros are yet the macros still do not work unless the original excel file is open. I am sure there is a line of code that is causing me issues, but I have little to no VBA experience. If anyone thinks they are able to help, please dm me.

    Thanks!

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: VBA Macro only works when a certain excel file is open

    One of the first reasons which come to my mind is that aside from the VBA code in standard modules, in original file some code could be either in ThisWorkbook or/and in Sheet(s) module(s).
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    07-09-2020
    Location
    Dallas
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    34

    Re: VBA Macro only works when a certain excel file is open

    Kaper, thanks for replying.

    The code that is in the "Thisworkbook" is this:

    Please Login or Register  to view this content.

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: VBA Macro only works when a certain excel file is open

    When this file is opened this code Workbook_Open is automatically called and as you can see the only thing in this code is to run Sethotkeys macro.

    So please check if between the macros you copied are these: SetHotkeys and Resethotkeys. And before running other macros run SetHotkeys and after finishing your work you may want to run Resethotkeys.

  5. #5
    Registered User
    Join Date
    07-09-2020
    Location
    Dallas
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    34

    Re: VBA Macro only works when a certain excel file is open

    Kaper, I see the reference to one of the modules that has the code:

    sub sethotkeys () .. lines of code.. End Sub

    Then,

    sub resethotkeys () ..lines of code.. End sub

    However, I am not following as to how this pertains to me being able to use the macros without the workbook being opened. If it isn't apparent already, I am a complete novice to macros.

  6. #6
    Registered User
    Join Date
    07-09-2020
    Location
    Dallas
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    34

    Re: VBA Macro only works when a certain excel file is open

    Bump
    (10 characters)

  7. #7
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: VBA Macro only works when a certain excel file is open

    As I understand, you copied to your Personal.xlsb also these two subroutines. (you mentioned copying modules from the macro file).
    You can Run them directly from View->Macros->View Macros (or if you have anabled Developer ribbon from Developer->Macros)

    Of course it is possible that these Subroutines have some exta calls to other pieces of code inside, if it's true - your code will probably stop - then press Debug in Error dialogbox and see the line of code highlighted in yellow.
    Last edited by Kaper; 06-25-2022 at 03:11 AM.

  8. #8
    Registered User
    Join Date
    07-09-2020
    Location
    Dallas
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    34

    Re: VBA Macro only works when a certain excel file is open

    Kaper, somehow the macros work - I have added reputation.

    Can you please explain the logic here..
    So, Originally I had macros that were unable to run because of the code found in the worksheet folder of the original file
    Once the modules were added to my personal workbook you suggested going into each of the 3 modules -> running the code-> debugging the code.

    Now all of a sudden, the macros work. Can you explain the logic so I can understand deeper?

  9. #9
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: VBA Macro only works when a certain excel file is open

    As I can't see the macros, I can't say what goes inside.
    But you have shown the code of Thisworkbook of the original file which, after opening "in magical way" made macros operational.
    In this code there was Workbook_Open sub (formally - event handler) which was automaticaly called when that file was open. It contained Sethotkeys sub, which probably is in one of these 3 modules.
    So once you run it, it was like opening the original file.
    As I said - I don't know what is in this macro. But it's name is probably meaningful - so it probably sets some hotkeys which are then used in (ore used to call) other macros.

    BTW. After finishing work, it would be wise to call the ResetHotkeys sub. (again, I don't know what is inside, but probably it restores default hotkeys, so you can use Excel in 100% standard way (but probably the other macros - these from copied modules) will not work then (until sethotkeys is excecuted again).

    Hope it clarifies the case a bit.

    PS. And thanks for a reputation star

+ 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] Simple Macro works in 1 Excel file but not the other.
    By MsBBStacker in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-03-2015, 01:13 AM
  2. [SOLVED] Macro in file works the first time, but not second time I open file
    By gillyr7 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-14-2014, 07:02 PM
  3. [SOLVED] strange sumifs problem, works with file open, #value with file closed
    By neowok in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-04-2013, 05:32 AM
  4. [SOLVED] How do I open a floppy from a Works file in Excel on new computer
    By Barbie in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-11-2006, 12:15 AM
  5. Replies: 1
    Last Post: 01-02-2006, 11:30 PM
  6. Replies: 0
    Last Post: 12-30-2005, 04:35 AM
  7. Replies: 1
    Last Post: 01-25-2005, 11:06 PM

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