Results 1 to 7 of 7

Thread: Macro Keys

  1. #1

    Macro Keys



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I use many macros to ease my work and assign short-cut keys to them. I'll explain my problem with an example:

    Say I have a 2 workbooks - A and B.
    Wkbk A has a macro to do a certain task and I assign it a key Ctrl+g.
    Wkbk B also has a macro to do some other task with the same shortcut key of Ctrl+g.
    If the workbooks are opened individually one at a time, there is no problem with the macros. BUT if both workbooks are open then even if the active workbook is A, on pressing Ctrl+g, I get an error of subscript out of range and debugging points to a line in the macro of workbook B. Why? What can be done so that the macro in active workbook is the default macro to be played.

    Thanks.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,027
    Articles
    79
    Blog Entries
    14
    I believe that the most recent workbook to be opened gets the shortcut key assigned (it overwrites any previously).

    You could deal with this by using code to assign your macro to the keystroke in the Workbook_Activate event. That way each time the workbook is activated it would re-asset control.

    I know you post at other forums, and it's been a week since you posted this. Have you got a solution yet and, if so, would you mind linking to it or posting it here? If not, we'd be glad to help.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    No. I haven't posted this question anywhere. Normally I wait for about 7-10 days before I post my question anywhere else. I understand that it is not a good habit to pose the same question at multiple sites. The last one also, I posted here after I did not get a reply from there for quite some time.

    Coming to this question, how do I assign the keystroke in the Workbook_Activate event?

  4. #4
    Code:
    Private Sub Workbook_Activate()
    ' assign shortcut  Alt-t to macro Macro1
    Application.MacroOptions _
        Macro:="Macro1", _
        Description:="My macro does this", _
        ShortcutKey:="t"
    End Sub

  5. #5
    eferrero

    The vb code seems to work, but I couldn't find the workbook_activate option so put the code in worksheet_activate. I get an error 1004 when I activate a sheet other than the the sheet for which the activate code is written.
    Where can I find the Workbook_Activate?

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,027
    Articles
    79
    Blog Entries
    14
    Hi hercule,

    The Workbook_Activate event should be placed in the ThisWorkbook code module. It sounds like you put it in the Sheet1 (or equivalent) module.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Thanks a lot Ken. Works fine now. Appreciate your help.

Posting Permissions

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