People always ask, how to do you lock Excel? In fact you have to unlock Excel and then protect the sheet. The default setting for all cells is locked, so you need to unlock input cells.
Tag Archives: Macros
Another Useful Print Macro
And it is recorded
Not all recorded macros are re-usable. This print selection one is.
Excel Macros and Merged Cells
Merged cells are a problem
I don’t like merged cells. Here is one more reason.
Save and Close extra
Handling read only and new workbooks
In my previous post I created a macro from scratch that saved and closed the current file. The macro required that the file had been saved before and wasn’t read only. This post handles those two situations so you run the macro on any file and it will only work when required.
Save and Close in Excel
Let's create a macro
I started using Excel in the late 80’s on a Mac. It had a Save and Close button. When I discovered VBA in Excel on the PC, the very first macro I ever made was save and close.
I thought this would be a good example to take you through creating a macro from scratch and sharing a technique to make it easy to use.
Use an Outlook template for an email
Why is it so hard to do?
Macros are designed to reduce keystrokes and mouse clicks. Here is a perfect example. It takes seven separate mouse clicks to use an Outlook email template as a new email. Here is a macro that does it in one.
Excel VBA to confirm a range is selected
You need to type TypeName
One of the most powerful statements in VBA is Selection. This holds whatever the user has selected just before they ran the macro. Normally it is a range, but what if it isn’t?
Excel VBA to select the first cell in a range
Top left cell
If you need to refer to the first cell (top, left cell) in a range there is an easy way to do it.
VBA tip – please don’t leave your Next blank
Include the variable
In a For Next loop you don’t have to include the variable in the Next statement. But ….
Activating a Sheet When an Excel File is Opened
Its a macro
There are times when you would like to have the same sheet visible each time a file is opened. You can achieve that with a Workbook Event macro.
Excel Macro to Insert a Formula
When I am creating a file for my training or for my blog or other articles that I write I regularly use the FORMULATEXT function to display the formula in a cell on the right of the actual formula. To save time I created a macro to do the work for me.
Extract Email from Copied Outlook Address
Macro to the rescue
I frequently copy an email address from Outlook to Excel and most times it looks like John Smith<jsmith@email.com>. To be used as an email I need to extract from between < and >. To do that in a single cell is tedious, so I wrote a macro to do it for me.
Increase Font Size in VBA Window
Easy change
Yes, you can make the text in the VBA window easier to read.
It is also great for training.
(This tip may not work on 4K monitors.)
To open the VBA Window press Alt + F11.
Click the Tools menu and then Options – see image below.
Click the Editor Format tab and change the Size drop down to 14, or whatever you want – see image below.
Click OK and the font size will now be increased in the code window.
See below for a comparison between 10 and 14 point.
Much easier to read!
Free Webinar Recording – Recorded Macros Tips Tricks and Traps
Getting started with recorded macros
It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Click the materials Button below to download the pdf manual and example file.
Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names.
Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel.
This is the first in a series of webinars dedicated to macros. Future paid sessions this month (August 2019) will expand on the techniques taught in this session. Click here to register.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
Excel ISDATE Function
A few solutions
Unfortunately Excel doesn’t have an ISDATE function. Excel’s macro language, VBA does, but there is no spreadsheet function that let’s you know if a cell contains a date. Well there is a partial workaround and you can also use VBA.
Autofit Rows with a Maximum Row Height
Follow up post
In March I wrote a post on using a macro to apply column Autofit on a sheet but with a maximum column width. This is a follow up post as someone requested the same functionality for row height.
Autofit columns with a limit
A macro to the rescue
The column Autofit on the whole sheet is a great Excel feature. But if you have a few columns that have lots of text it can make using it problematic as you need to manually adjust those wide columns. Here’s a macro to make it easier.
Navigation in the VBA Window
An Excel shortcut
I recently found a useful keyboard shortcut for the VBA code window. I tried an Excel shortcut that helped in the VBA window.
Free Excel Webinar Recording – Excel Yourself 2018
Feedback score 89%
In December 2018 I reviewed four of my articles from the INTHEBLACK magazine from 2018. Download the materials using the button below and watch the video.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
Includes extra content not included in the magazine.
This year’s smorgasbord webinar includes
- how to handle validations
- dynamically highlighting the current row in a table (uses a macro)
- centralising logic – how and why to do it – alternatives to the IF function
- creating a slope chart (includes a macro to speed up the process)
As always there will be a few more tips and tricks shared in the session.
First and Last Cell Address in a Range
Following on from my last two VBA posts here is how you can identify the cell addresses of the first and last cells in a range.
VBA to Find the Row and Column Number Limits of a Range
First and last row and column numbers
In my last blog post I found the last used row and column numbers on the active sheet. This post lets us find the row and column extremities of a specific range.
Excel VBA to Find the Last Used Row or Column
UsedRange used wisely
When writing VBA code it is common to need to know the last used row and/or the last used column. These values provide the edges of the sheet contents.
Range Names and Macros
Range names rule
When you record a macro that refers to a particular cell or range on a particular sheet in Excel the range reference is hard coded into VBA (macro) code. Unfortunately this means if rows or columns are inserted or deleted in the reference range the code is not updated. There is an easy way to get around this.
Fixing too many different cell formats
A VBA solution or two
In a recent webinar I was asked about the “Too many different cell formats” error. This tends to be an error in Excel 2010 and earlier versions. In many cases this error is caused by having too many custom Styles.
Entering Headings via a Macro in Excel
Handle a range in one go
Let’s say you are creating a new table in a new sheet using a macro and you need to create the headings in row 1. There is a reasonably easy way to do it.
Free Excel Webinar – recording Macros Tips Tricks and Traps
Getting started with macros
It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Click the materials Button below to download the pdf manual and example file.
Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names.
Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel.
This is the first in a series of webinars dedicated to macros. Future paid sessions this month will expand on the techniques taught in this session.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
Expand to Fit to One Page in Excel
Macro solution
Excel will automatically decrease the print zoom % to fit to one page, but it won’t increase the zoom % to fit to one page. E.g. if you want to print on A3 instead of A4. I had a request to do this, so I wrote a macro to do it.
Declaring Variables in Excel VBA
Using the Dim statement
In a Linkedin Excel Group recently there was a discussion about whether or not you should use the Dim statement to declare your variables. The argument was that you don’t have to and someone had managed to successfully create some code without declaring variables.
Are There Hidden Rows or Columns?
A macro answers the question
I was working on a project for a client and receiving multiple files. Some of the sheets had hidden rows or columns. I realised there is no easy way to find out if a sheet has hidden rows or columns, so I wrote a macro.
Deleting Duplicated Names in Excel
Names scoped at Worksheet level are often duplicates
When you copy a sheet that contains range names you usually end up making a duplicate of those names at the Worksheet level. I have written a macro that removes all duplicated sheet-based range names in a file.