Archive for the ‘Excel’ Category

Macro keyboard shortcuts in Excel 2007

June 10, 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…


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
        MyData.SetText Selection.Value
    End If
End Sub