+ Reply to Thread
Results 1 to 8 of 8

How do I find out what macro a particular keyboard shortcut is assigned to?

Hybrid View

  1. #1
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2411
    Posts
    26,730

    How do I find out what macro a particular keyboard shortcut is assigned to?

    I was editing a file and used CTRL+r to fill right. But instead, it grabbed a big chunk of my worksheet and deleted it, and may have done other things as well.

    The file I was editing was not a macro-enabled file, and no other macro-enabled file was open, except PERSONAL.xlsb.

    I suspect that one my macros in PERSONAL was assigned CTRL+r, probably accidentally as I would avoid reusing a predefined shortcut. But I have dozens of macros in there.

    Is there any way to trace this, other than by individually examining each Sub to see which one is invoked? Worst-case scenario is that the shortcut refers to a Sub that is not currently open.

    I found a link on one of Allen Wyatt's pages that refers to a tool someone wrote to generate a list of shortcuts to macros, but that link does not go where it says it goes.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: How do I find out what macro a particular keyboard shortcut is assigned to?

    Ivan's page disappeared ages ago sadly. I have a copy of his add-in though, which I hope he won't mind me linking here.
    Attached Files Attached Files
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2411
    Posts
    26,730

    Re: How do I find out what macro a particular keyboard shortcut is assigned to?

    Thanks Rory. I installed it but couldn't figure out how to run it, and the code is password-protected. Meantime I wrote my own quick and dirty code to do this with some research, based on something Andy Pope posted on another forum about 10 years ago.

    Here's where things get weird. I do not have any shortcuts defined for my of my own macros. Just to make sure the code was working I added one, and it was properly listed.

    Either a built-in Excel shortcut has been corrupted (highly unlikely IMHO) or there is some other shortcut coming into play that I can't find, maybe at the Windows level.

    See corrected code in post below
    Last edited by 6StringJazzer; 11-13-2018 at 10:14 AM.

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: How do I find out what macro a particular keyboard shortcut is assigned to?

    Quote Originally Posted by 6StringJazzer View Post
    Thanks Rory. I installed it but couldn't figure out how to run it
    It should add a button to the add-ins tab. If you don’t see that, you’ll need to unblock the .xla in Explorer first.

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2411
    Posts
    26,730

    Re: How do I find out what macro a particular keyboard shortcut is assigned to?

    A little too quick and too dirty. Working on a bug fix to this code, stand by.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,734

    Re: How do I find out what macro a particular keyboard shortcut is assigned to?

    There's also some code here that might work (I haven't tried it)
    https://groups.google.com/forum/#!to...ns/TwcT-IlWjVk

  7. #7
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: How do I find out what macro a particular keyboard shortcut is assigned to?

    Hello 6StringJazzer,

    Maybe this Link could be of some help.

    Kind regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  8. #8
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2411
    Posts
    26,730

    Re: How do I find out what macro a particular keyboard shortcut is assigned to?

    OK, I found the problem. I had another file open that I was working on to solve another thread here, and that had the shortcut of CTRL+r attached. I am starting to think I should troubleshoot other people's files in a sandboxed VM.

    Here is the code that I ended up writing (this is Windows only, ENVIRON not available on a Mac):
    Option Explicit
    
    Public Sub ExportModules()
    
       
       Dim OpenVBProject As VBProject
       Dim CurrentVBComp As VBComponent
       Dim Destination As String
       Dim TextLine As String
       Const ShortcutFlag = ".VB_Invoke_Func"
       Dim PathRoot As String: PathRoot = Environ("HOMEPATH") & "\"
       Dim Report As String: Report = PathRoot & "Shortcut Report.txt"
       Dim SubName As String
       Dim KeyboardShortcutRegEx As Object
       
       Set KeyboardShortcutRegEx = CreateObject("VBScript.RegExp")
       
       ' Format of keyboard shortcut string in exported .bas file:
       ' Attribute [subname].VB_ProcData.VB_Invoke_Func = "[shortcutkey]\n14"
       KeyboardShortcutRegEx.Pattern = "^Attribute ([^.]*)\..*VB_Invoke_Func = ""([^\\]*)\\n14""$"
       
       On Error Resume Next ' in case file does not exit
       Kill Report
       On Error GoTo 0
       
       Open Report For Output As #2
       
       'Set OpenVBProject = Application.VBE.ActiveVBProject
       For Each OpenVBProject In Application.VBE.VBProjects
       
          On Error Resume Next ' some project causes errors on attempt to export,
                               ' that is checked explicitly farther down
                               
          For Each CurrentVBComp In OpenVBProject.VBComponents
          
             If CurrentVBComp.Type = vbext_ct_StdModule Then
             
                Destination = PathRoot & OpenVBProject.Name & " " & CurrentVBComp.Name & ".bas"
                CurrentVBComp.Export Destination
                
                If Err.number > 0 Then
                   Debug.Print Destination & ": " & Err.number & " " & Err.Description
                   Err.Clear
                Else
                
                   ' Get the shortcut keys
                   ' Read the .bas file, checking for RegEx matches
                   Open Destination For Input As #1
                   Do While Not EOF(1)
                      Line Input #1, TextLine
                      If KeyboardShortcutRegEx.test(TextLine) Then
                         SubName = KeyboardShortcutRegEx.Replace(TextLine, "$1")
                         Print #2, OpenVBProject.Name & " " & CurrentVBComp.Name & " " & SubName & _
                         ": CTRL+" & KeyboardShortcutRegEx.Replace(TextLine, "$2")
                      End If
                   Loop
                   Close #1
                   Kill Destination ' comment this out if you would like to keep the .bas files
             
                End If
             End If
          Next CurrentVBComp
       
       Next OpenVBProject
       
       Close #2
       Shell "notepad " & Report, vbNormalFocus
    
    End Sub

+ 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. Keyboard Shortcut macro as changed?!
    By Jabba69 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2017, 04:12 PM
  2. [SOLVED] Creating a macro with ONE SINGLE key assigned as shortcut
    By Eagle29 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-16-2013, 06:57 AM
  3. Change keyboard shortcut for Macro
    By chaminod in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2007, 08:29 PM
  4. Code:Run Macro from Keyboard Shortcut
    By SAR in forum Excel General
    Replies: 2
    Last Post: 08-31-2006, 10:42 PM
  5. Run Macro from Keyboard Shortcut
    By SAR in forum Excel General
    Replies: 0
    Last Post: 08-31-2006, 10:30 AM
  6. [SOLVED] Keyboard Shortcut in Macro
    By osaka78 in forum Excel General
    Replies: 3
    Last Post: 01-16-2006, 04:50 AM
  7. Remove all keyboard shortcut keys assigned to macros - an example
    By aztecbrainsurgeon@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-28-2005, 03: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