Macro keyboard shortcuts in Excel 2007

I’ve just written a new macro in Excel, and wanted to assign Ctrl-Shift-F to it, as it does some advanced finding. However, the Shortcut key field that appears in the Record Macro dialog and also in the Options… dialog opened from the Macros dialog, it seems to only be possible to assign Ctrl-<something> to a macro, and not Ctrl-Shift-<something>.

Disappointed, I choose Ctrl-E instead. Cursing Microsoft again for not putting a decent keyboard customization feature in Excel 2007 (and also in Outlook Editor), like they did in Word 2007.

But then I recalled that I already had a macro that copied the content of the selected cell, and I’m used to activate it using Ctrl-Shift-C. I want back to the Options… dialog and found out that although it says Ctrl+<something>, if you type Shift-<something> in that field, the Ctrl label changes to Ctrl+Shift.

I’d say that’s not the most discoverable feature I’ve seen…

PS

The Macro I use for copying a cell (or cells) content is as follows:


Sub CopyCellContent()
    Dim MyData As DataObject
    Dim s As String
    Dim i As Long
    Set MyData = New DataObject
    If IsArray(Selection.Value) Then
        s = Selection.Cells(1).Value
        For i = 2 To Selection.Cells.Count
            s = s & ", " & Selection.Cells(i).Value
        Next i
        MyData.SetText s
    Else
        MyData.SetText Selection.Value
    End If
    MyData.PutInClipboard
End Sub

About these ads

6 Responses to “Macro keyboard shortcuts in Excel 2007”

  1. Eileen Says:

    My method of changing a macro assigned kyboard shortcut from Ctrl+ to Ctrl+Shift+ …….

    1) In Macro menu click on desired macro and click Options.
    2) In the Shortcut key input field type an uppercase S. This changes Ctrl+ to Ctrl+Shift+
    3) Click OK, then go back into Options and change the S to the letter you desire – must use an uppercase letter.
    4) Click OK.

    • splintor Says:

      I don’t get it – why not use the letter you desire in the first place?

    • Alex Says:

      Actually, you can simply shift the letter you want, not just S, and you will get the same result anyway.
      I found that out by fat-fingering around (= pressed keys by accident).
      If you notice, your method works because, in both changes, you are shifting your letter. Shifting, shift = Shift+[letter].
      Thanks anyway, Eileen

    • Toshi Says:

      Thanks a lot. It works well, Ctrl+Shift+something on 2007 Excel.

  2. Alex Says:

    This undocumented feature you refer to also was in Excel 2003 (yes, we still use it at work), about pressing Shift and it adds it to Control. I wonder how come you did not run into this problem sooner.

    You still don’t mention how to get to the Macro menu, or wherever it is that you can do macro assignments in Excel 2007. As you may know, Office 2007 did a major rework on the menues and keyboard shortcuts, so the usual places no longer work.

  3. sam Says:

    yeah, I set a macro to ctrl+z. big mistake. I can’t figure out how to change it in excel 2007. been looking for 30 minutes now.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: