EXCEL VBA: HOMEMADE EXCEL SHORTCUTS TO IMPROVE WORK EFFICIENCY
Overview
If you find an English expression that feel incorrect or awkward, please let me know.
Message box of Disqus is under the article.
Or my E-Mail is here.
ABOUT THIS ARTICLE
I think there are quite a lot of cases to post text from Excel to another file or document, cut and paste shapes such as rectangle and arrow on Excel sheet, and create a very simple flowchart or schematic diagram.
In these cases, what improved the speed of work was my homemade shortcuts which are not not originally included in Excel, so I'm going to introduce them.
LIST
List below are the shortcuts to be introduced
| Functionality | When To Use | Assigned Key |
|---|---|---|
| copy only text from target cell | when gettting sentence in the cell without double quotes at both ends |
Ctrl + Shift + K |
| move the selected object to front or back | when creating a little complicated diagram etc. | Ctrl + Shift + B |
| pause Excel Events | when opening Excel books with macro, without running event processing which automatically open form |
Ctrl + Shift + M |
HOW TO REGISTER MACROS FOR USING HOMEMADE SHORTCUT
- open VBE.
- write some procedures in standard module.
- back to sheet, and open Macro setting Screen by pressing Alt F8.
- select target procedure and register shortcut key to call it via options.
WHAT KEY TO REGISTER?
The most convenient way to register shortcuts is to use a key that has not yet been reserved or is less used normally.
I suggest Ctrl + Shift + K, M, N.
SEPARATE DESCRIPTION
SHORTCUT TO COPY ONLY TEXT FROM TARGET CELL
When we try to copy cell in sheet and paste it to another application such as notepad, it goes along with some extra stuff, i.e. Line Feed and double quotes.
They are sometimes botherring our task.
Excel App has a function of pasting only values but doesn't have copying only values, I guess that's what causes our small troubles.
Below is a shortcut to improve this.
CODE
1'******************************************************************************************
2'*Function :copy activecell's content to clipboard
3'******************************************************************************************
4Public Sub copyCellValueToCB()
5
6 'Const
7 Const FUNC_NAME As String = "copyCellValueToCB"
8
9 'Vars
10
11 On Error GoTo ErrorHandler
12
13 'store text to clipboard
14 With CreateObject("Forms.TextBox.1")
15 .MultiLine = True
16 .Text = CStr(ActiveCell.Value)
17 .SelStart = 0
18 .SelLength = .TextLength
19 .Copy
20 End With
21
22ExitHandler:
23
24 Exit Sub
25
26ErrorHandler:
27
28 MsgBox "An error has occurred and the macro will be terminated." & _
29 vbLf & _
30 "Func Name:" & FUNC_NAME & _
31 vbLf & _
32 "Error No." & Err.Number & vbNewLine
33
34 GoTo ExitHandler
35
36End Sub
SHORTCUT TO MOVE THE SELECTED OBJECT TO THE FRONT OR BACK
When creating a simple flowchart, schematic diagram, and organization chart,
probably there are a case that we want to move shapes to the front or back against the other shapes in order to adjust the overlap of them.
In that case, calling the processing of 'bring to front' by Right-click is slow, and this shortcut reduce time of it.
CODE
1'******************************************************************************************
2'******************************************************************************************
3Public Sub ZOrderToFront()
4
5 'Const
6 Const FUNC_NAME As String = "ZOrderToFront"
7
8 'Vars
9
10 On Error GoTo ErrorHandler
11
12 Selection.ShapeRange.ZOrder msoBringToFront
13
14ExitHandler:
15
16 Exit Sub
17
18ErrorHandler:
19
20 If Err.Number = 438 Then
21 MsgBox "Plrease run after selecting target object.", vbExclamation, "Warning"
22 Else
23 MsgBox "An error has occurred and the macro will be terminated." & _
24 vbLf & _
25 "Func Name:" & FUNC_NAME & _
26 vbLf & _
27 "Error No." & Err.Number & vbNewLine
28
29 End If
30 GoTo ExitHandler
31
32End Sub
33
If you try to make ZOrderToBack, replace:
1Selection.ShapeRange.ZOrder msoSendToBack
SHORTCUT TO PAUSE EXCEL EVENTS
Useful in cases below:
- When editting Excel book with macro, you want to launch it without opening event procedure.
- When switching active worksheet, some event may be executed and you fell it troublesome.
CODE
The entire process divides into two parts: caller funtion and core process in a form.
CALLER: IN STANDARD MODULE
1
2'in Tools.bas
3
4'******************************************************************************************
5'*Function :Disable All Excel Events during displaying a F_invalidateEvents form
6'******************************************************************************************
7Public Sub invalidateEvents()
8
9 'Const
10 Const FUNC_NAME As String = "invalidateEvents"
11
12 On Error GoTo ErrorHandler
13
14 'open the form
15 F_invalidateEvents.Show vbModeless
16
17ExitHandler:
18
19 Exit Sub
20
21ErrorHandler:
22
23 MsgBox "An error has occurred and the macro will be terminated." & _
24 vbLf & _
25 "Func Name:" & FUNC_NAME & _
26 vbLf & _
27 "Error No." & Err.Number & vbNewLine
28
29 GoTo ExitHandler
30
31End Sub
32
FORM: CORE PROCESS
1
2' in F_invalidateEvents.frm
3
4Option Explicit
5
6
7'******************************************************************************************
8'******************************************************************************************
9Private Sub UserForm_Initialize()
10
11 'Const
12 Const FUNC_NAME As String = "UserForm_QueryClose"
13
14 'Vars
15
16 On Error GoTo ErrorHandler
17
18 'disable events
19 Application.EnableEvents = False
20
21ExitHandler:
22
23 Exit Sub
24
25ErrorHandler:
26
27 MsgBox "An error has occurred and the macro will be terminated." & _
28 vbLf & _
29 "Func Name:" & FUNC_NAME & _
30 vbLf & _
31 "Error No." & Err.Number & vbNewLine
32
33 GoTo ExitHandler
34
35End Sub
36
37
38'******************************************************************************************
39'******************************************************************************************
40Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
41
42 'Const
43 Const FUNC_NAME As String = "UserForm_QueryClose"
44
45 'Vars
46
47 On Error GoTo ErrorHandler
48
49 'enable events
50 Application.EnableEvents = True
51
52ExitHandler:
53
54 Exit Sub
55
56ErrorHandler:
57
58 MsgBox "An error has occurred and the macro will be terminated." & _
59 vbLf & _
60 "Func Name:" & FUNC_NAME & _
61 vbLf & _
62 "Error No." & Err.Number & vbNewLine
63
64 GoTo ExitHandler
65
66End Sub
67
68'******************************************************************************************
69'******************************************************************************************
70Private Sub CommandButton_Close_Click()
71
72 'Const
73 Const FUNC_NAME As String = "CommandButton_Close_Click"
74
75 'Vars
76
77 On Error GoTo ErrorHandler
78
79 'close form
80 Unload F_invalidateEvents
81
82ExitHandler:
83
84 Exit Sub
85
86ErrorHandler:
87
88 MsgBox "An error has occurred and the macro will be terminated." & _
89 vbLf & _
90 "Func Name:" & FUNC_NAME & _
91 vbLf & _
92 "Error No." & Err.Number & vbNewLine
93
94 GoTo ExitHandler
95
96End Sub
97
DEMO
After calling the form, it disable all events initially, and you can restore it by pressing close button.
AT THE END
I'm going to update this article if I create a new handy shortcut.
See Also
- EXCEL VBA: WITH POLYMORPHISM, BRANCHING A PROCESS WITHOUT USING IF STATEMENT
- EXCEL VBA: I CREATED A TOOL THAT ALL SELECTED EXCEL BOOK HAVE THEIR CURSOR MOVED TO A1.
- EXCEL VBA: I CREATED A QUOTATION CREATION TOOL.
- TOOL DEVELOPMENT ON EXCEL: COMPARING BETWEEN TOOLS USING FUNCTION (NON-MACRO) AND TOOLS USING VBA MACRO
- ACCESS VBA: I CREATED A TOOL EXPORTING TABLE DEFINITIONS DISPLAYED AT DESIGN VIEW IN A TABULAR FORMAT.