• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

Number of Macro making Excel Default Shortcuts slow

Hi,

I have a lot of macros in my workbook (Excel 2010). The are used on buttons for navigation through out the excel file. When assigning the macros I do NOT create short cuts for them. The macros are very basic:

Sub Finance_InputsSection()
    ActiveSheet.Range("A2").Select
End Sub

When I highlight cells and right click and copy using the mouse- it happens instantly. When I click Find in the ribbon the search dialog box open instantly also. However when I highlight cells and tap short cut Ctrl + c it lags for about 3-4 seconds. The same when I tap Ctrl + f. I created a blank workbook and just simply copied in the VBA code. Same issue. This leads me to believe that when I tap Ctrl+c it loops through all my macros to see are any short cuts assigned and hence the lag.

I have created a number of modules and broken my VBA code in smaller chunks but still the same.

Any ideas?
0
Kevin Cryan
Asked:
Kevin Cryan
  • 4
  • 3
1 Solution
 
RobOwner (Aidellio)Commented:
Can you provide the workbook in question?  Kind of hard to reproduce without it.
0
 
Kevin CryanAuthor Commented:
Hi Rob.

If you create a blank Excel 2010 or 2013 macro enabled workbook and paste in the following attached sample macros into a module. If you tap Ctrl + c or Ctrl + f you should see the lag. But if you right click a cell with your mouse and go copy you shouldn't experience any lag.

Let me know if you need any more info.
0
 
Kevin CryanAuthor Commented:
Sample File with macros
SampleMacros.txt
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
RobOwner (Aidellio)Commented:
As the shortcuts are inherently linked to the macros, Excel searches through them all and I couldn't find anywhere to turn it off.

One way around it, is to disable all Ctrl+[key] combinations.  I've tested and included the code below.  You can still use Ctrl+Insert for Copy, Shift+Insert for paste, Shift+Del for Cut

Sub DisableShortCutKeys()
'Disable Ctrl-a to Ctrl-z
Dim x As Integer
For x = 97 To 122
Application.OnKey "^" & Chr(x), ""
Next x
End Sub

Sub EnableShortCutKeys()
Dim x As Integer
For x = 97 To 122
Application.OnKey "^" & Chr(x)
Next x
End Sub

Open in new window

0
 
Kevin CryanAuthor Commented:
Hi Rob,

Thanks for reply. Unfortunately I couldn't say to the users that the most common shortcuts of Ctrl+c and Ctrl+f are disabled. Looks like there is no fix for this.
0
 
RobOwner (Aidellio)Commented:
i know... i thought you may say that.. sorry i couldn't help further.From doing some testing its apparent that you cannot turn off shortcuts for macros.  Excel still goes through them all to see if there is one or not... seems very inefficient to me.
Can't be done.
0
 
Kevin CryanAuthor Commented:
Thanks for the help Rob. I will maybe look at raising it with  Microsoft.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now