These days running a macro off a control button seems to be old school and many people have started running macros off graphics.
Category Archives: Macros
VBA AutoComplete
When you are typing code in the VBA code window you can press
Ctrl + Space Bar
to have Excel finish the word for you. Eg type
appli
And then press Ctrl + Space Bar to have Excel finish the word Application.
Related Posts
Excel VBA Command to Clear the Clipboard
Stop users pasting after the macro is run
When using copy and paste in a macro it is a good idea to clear the clipboard at the end of the macro. If you don’t, the user could use paste to paste the last thing you had copied in the macro.
Excel Hiding and Unhiding Error Fix
Sometimes when you hide or unhide, rows or columns, you can get an error message saying that Excel can’t move objects off the sheet. The solution is in the macro below.
Activating a sheet in VBA
Here’s the problem. You have a number of sheets that are named after Department codes. Those sheets contain the details for each department. You have reports throughout the model that refer to these department codes. You want to be able to select a cell that contains a department code and click a button that will take you to that department’s sheet.
Running a Macro From the Quick Access Toolbar
The Quick Access Toolbar (QAT) can really speed up your work in Excel. You can even attach macros to the QAT.
VBA Functions
If want to see a list of the functions available in the VBA language just type
vba.
in the code window
You don’t need to include vba. when using the function in code.
If you see a function with a $ in its name it means it returns a string (text) eg UCase$
(Thanks to John Walkenbach for this tip.)
Click here to see how you can run a Macro off a Quick Access Toolbar icon.
VBA macros that run other macros
When you become more advanced with macros and VBA programming (Visual Basic for Applications), you realise that you can create re-useable macros.
Unhide all sheets in Excel
In Excel if you need to hide data or workings sheets you can hide multiple sheets in one action. Unfortunately Excel won’t let you unhide multiple sheets in one step (even in the latest version). To get around this limitation you can use a macro that unhides all the sheets.
Restricting the Scroll Area in Excel
Non-VBA and VBA solution
If you need to limit where a user can scroll to in a sheet you can change a setting in the VBA screen to restrict access to a specific range.
Excel and the VBA Editor
Make the VBA editor screen your friend
When you first start out using VBA and writing VBA (macro) code it is useful that the VBA Editor helps you quickly identify when you have made a mistake. If a line of code has an error it will display a dialog box that explains the error and turns the line of code red.
Excel and Hyperlinks
Sometimes you want them, sometimes you don't
Hyperlinks are a great tool as they allow you to speed up and simplify navigation within a file. Sometimes hyperlinks can be frustrating. See how to remove some of those frustrations below.
Speeding Up Excel Macros
Tips and tricks for faster macros
There are two commands you can insert at the top of your code to speed up your macros.
Handling sheet passwords with Excel VBA
Protect and Unprotect quickly
When you are using sheet passwords in Excel it can be handy to have a macro to unprotect all the sheets in one step. This can make maintenance easier.
Protecting Your Excel VBA Macro Code
Applying a password to VBA
The more you use macros the more important they become and the more you want to make sure the VBA* code doesn’t get changed by someone who shouldn’t change it. You may also want to stop people viewing your code.
Stopping drag and drop in Excel via VBA
The ability to drag and drop in Excel is great when you know what you are doing. The downside is that it is also easy for inexperienced users to affect the structure of your spreadsheet by using drag and drop techniques.
Excel Macro Keyboard Shortcut
Macros can really improve your productivity in Excel. When you record a macro you have the option to define a shortcut key. Did you know you can also define a shortcut key for non-recorded macros?
Fixing Excel’s Pivot Table headings
Say goodbye to Sum of
Pivot Tables are incredibly powerful and easy to use. Unfortunately their headings can include the terms “Sum of” or “Count of”. This is not always what you’d like to present to users. A macro to the rescue.
Print just the first page in Outlook [VIDEO]
Save paper and time
If I print emails I typically only want the first page. To do that in Outlook takes a few clicks each time.
Take Excel to the UPPER class
All capitals made easy
Word has a keyboard shortcut to convert lowercase to uppercase. Shift + F3. Excel doesn’t. Macros to the rescue again.
Hiding sheets when opening an Excel File
Only show the sheets you want
If you need to hide certain sheets every time an Excel file is opened then a macro may be your solution. Maybe the sheets are working sheets and don’t need to be visible to the user.
Printing certain sheets in an Excel file
Speed up printing
If you need to print certain sheets in a file in one step you can set up a macro to do it automatically and flexibly.
Macro to save all open Excel files
Time saver
If you have a number of linked Excel files and you make a structural change (insert rows or columns) you need to save all the open files to ensure that the links are updated and retained. This can be tedious as there is no Save All option in Excel. Word has a Save All option, but not Excel.
How to make the most of Excel Macros
How to use and install macros
I get many questions from Australian CPA’s and sometimes the solution involves a macro. Not everyone knows how to install and run a macro. This post will take you through the basics.
DO NOT PASS GO TO in Excel
A quick way to select cells
Excel’s Go To feature provides a quick way to select certain types of cells. For example, if you wanted to apply the same fill colour to all formula cells on a sheet, you can do that in five easy steps using Go To.
Delete range names with #REF errors
A macro to the rescue
Here is an example of a simple macro that solves a problem in Excel 2003 and earlier versions.
Range names can be corrupted if a cell that they refer to gets deleted. This doesn’t mean that the cell value gets deleted, but the cell itself is removed from the sheet.
A Classic Pivot Table
Old School Pivot Table
Pivot Tables changed in Excel 2007 and 2010. The default setting doesn’t let you drag and drop the field names on the Pivot Table itself. You could do that in Excel 2003 and you can still do it in the newer versions. All you need to do is change a setting.
Unhide all sheets in a file macro [VIDEO]
Adding functionality with a macro
Excel allows you to easily hide a group of sheets BUT frustratingly, it won’t let you unhide a group of sheets. You have to unhide them one sheet at a time.