Which macro shortcut keys do you use?
July 16, 2010 • JP • 3 Comments • Rate This Article
• Links to this article
A quick poll: What shortcut keys do you use for your macros, and what macros are they assigned to? Here are mine.
They're not written as well as they could be, but I don't care. They work and that's good enough for me (until I get really bored; then I'll fix them).
Shortcut key: Ctrl+Shift+P
Macro name: CopyPasteValues
Description: Converts formulas in selected range to their values
Macro:
Dim rng As Excel.Range
If TypeName(Selection) <> "Range" Then
Exit Sub
End If
Set rng = Selection
rng.Copy
rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
A quick way to remove formulas and paste in the underlying data.
Shortcut key: Ctrl+Shift+F
Macro name: FilterToggle
Description: Adds autofilter arrows to selected range (usually row 1)
Macro:
' add data filter arrows
' if error occurs, just skip
On Error Resume Next
Selection.AutoFilter
End Sub
Apply autofilter arrows to any worksheet. Just select the appropriate row first.
Shortcut key: Ctrl+Shift+H
Macro name: FormatHeaderRow
Description: Applies autofilter arrows, bold and color formatting to row 1, centers row 1 text and autofits all columns
Macro:
Application.ScreenUpdating = False
If ActiveSheet.AutoFilterMode = False Then
With Range(Range("A1"), Range("IV1").End(xlToLeft))
.AutoFilter
.Font.ColorIndex = 2
.Font.Bold = True
With .Interior
.ColorIndex = 43
.Pattern = xlSolid
End With
.HorizontalAlignment = xlCenter
.WrapText = False
.Columns.AutoFit
End With
' Range("A2").Select
' ActiveWindow.FreezePanes = True
Else
MsgBox _
"Cannot autofilter the header row, there is already an autofilter on this sheet", vbCritical
End If
Application.ScreenUpdating = True
End Sub
This macro makes the header row (usually row 1) look exactly how I like it. The autofilter arrows are applied. The cells' text is centered. The font is bold, white with green background (I change this depending on my mood). Finally, the columns are autofit so I can see the entire header and all the data.
Optionally, I can freeze the window pane so that the header stays on the screen when scrolling.
Shortcut key: Ctrl+Shift+S
Macro name: SetNormal
Description: Removes formatting from worksheet and makes it look "normal" (according to me)
Macro:
' set worksheet to normal
Application.ScreenUpdating = False
If TypeName(Selection) <> "Range" Then Exit Sub
Dim rng As Excel.Range
Set rng = Selection
If rng.cells.count = 1 Then
Set rng = ActiveSheet.UsedRange
ActiveWindow.Zoom = 80
End If
With rng
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
With .Font
.Bold = False
.Name = "Tahoma"
.ColorIndex = 0
.Size = 10
End With
.Interior.ColorIndex = xlNone
.HorizontalAlignment = xlGeneral
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
.WrapText = False
.Rows.AutoFit
.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub
My "normal" is different from yours. I hate getting spreadsheets with different fonts, font sizes and colors. When I just want to work with the data and not be distracted by the layout, I run this macro. It sets the window zoom to 80%, with a Tahoma font (size 10). It removes any border lines and cell colors. It re-orients any cell to General alignment and unwraps any wrapped cells. Finally, it autofits all rows and columns so all data is visible.
Shortcut key: Ctrl+Shift+U
Macro name: TogglePersonalXls
Description: Hides and unhides personal.xls workbook
Macro:
' toggle personal.xls workbook hidden status
Dim window As Boolean
window = Windows("PERSONAL.XLS").Visible
Windows("PERSONAL.XLS").Visible = Not window
End Sub
I don't use this one much anymore, but the shortcut key is still there in case I need it.
So what shortcut keys do you use, and what macros are assigned to them?
Excel ·
Rate This Article:
(1 votes, average: 5.00 out of 5)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: What columns are filtered?
Next Post: Delete duplicate contacts in Outlook using VBA
Hi,
A. Numberformat that i've linked to a button.
Selection.NumberFormat = "#,##0"
End Sub
.
B. Change comments in something looking more attractive => Also with a personal button
Dim MyComments As Comment
Dim LArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.TextFrame.Characters.Font.Name = "Tahoma"
.Shape.TextFrame.Characters.Font.Size = 8
.Shape.TextFrame.Characters.Font.ColorIndex = 2
.Shape.Line.ForeColor.RGB = RGB(0, 0, 0)
.Shape.Line.BackColor.RGB = RGB(255, 255, 255)
.Shape.Fill.Visible = msoTrue
.Shape.Fill.ForeColor.RGB = RGB(58, 82, 184)
.Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
End With
Next 'comment
End Sub
.
Looking forward form more timesavers….
Thanks for sharing those "quick fixes" macros that actually save so much time.
The set normal is for me the best, but then all of them are great so that I know for sure that I'll be using them all pretty soon. The main issue now is to ensure keyboard short cuts that do not clash with other macros!
Thanks!