I use the thin all borders format a lot. But there are times when I need to use the thin outline (outside) borders. This border is not as straight forward to apply to a range.
Tag Archives: vba
Macro to Format a Spill Range in Excel
VBA and spill ranges can work together
Currently spill ranges do not spill formats. Hopefully Microsoft will add this functionality soon. In the meantime, here is a macro that will copy the format from the top left cell of the spill range to the rest of the spill range.
Slope Chart Macro
About 10 years ago I did a short blog post on Slope charts. I was reviewing my 10 year old posts and remembered I had written a macro to simplify the process of creating a Slope Chart. So here it is.
Stop Excel Control being Clicked
Textbox hack
In a protected worksheet users can still click on checkbox and option button controls. A warning message will pop up if the control’s linked cells are locked. There is a technique you can use to stop users clicking on these controls. This involves a macro that you can run just before you protect the sheet.
Hiding Multiple Sheets in Excel Using xlVeryHidden
Flexible solution
It is easy to hide multiple sheets in Excel. Unfortunately, it now just as easy to unhide those sheets. You can hide sheets and make it harder to unhide them. You can use a setting called xlVeryHidden (no kidding) that won’t display the sheet name if you right click a sheet tab and choose unhide sheets.
Excel VBA and the TRUNC Function
Excel has a TRUNC function that truncates numbers. For example =TRUNC(15.75) returns 15. It doesn’t round it just removes the fraction leaving the whole number. Excel VBA does not have the TRUNC function. It does have the Fix function that works the same.
Excel VBA to Get to the Top Left of the Screen
Sometimes when you are using Excel VBA you need the screen to always display at the top, left of the sheet. Here is how you do it.
Excel VBA to Sort Sheets by Name
In an earlier post I shared some VBA code to sort sheets by their colour. In this post I have tweaked the code to sort the sheets by their name.
Excel VBA Sort Sheet Tabs by Colour
Get your sheet tabs organised
Using colours on your sheets can help you navigate and organise your file. If you want to sort your sheets by colour you can use a macro to speed up the process.
Excel File is Open VBA Function
Useful function
When working with files in VBA code, you may need to know if a file is already open. A function can return TRUE if a file is currently open and FALSE if it isn’t.
Sheet Exists Function for Excel VBA
A function macro to help another macro
Often when you are working with Excel VBA you need to confirm if a sheet exists based on the tab name. This Function can be used to do that. It works with the active file (workbook).
Outlook macro to stop sending an email without an attachment [VIDEO]
Automatic attachment check via VBA
I don’t do many Outlook macros, but this one is really useful and I have used it for a long time. It looks at your outgoing email and checks to see if you have the word attach in it and then checks to see if you have an attachment. It warns you if you don’t.
Excel Data Validation Blind Spot
Macro to fix it
One of the problems with Excel’s Data Validation is that it is possible to have an invalid entry in a data validation cell. This can be caused by Paste Special Values or linked drop downs that don’t update if an earlier drop down is changed. To easily identify invalid cells you can use a macro.
Excel VBA to Navigate to Sheets
A shortcut menu makes it easy
If you right click the arrows on the left of the sheet tabs at the bottom, left of the Excel screen you can see a list of all the sheets in the file. You can use VBA to show this list anywhere.
Excel Macro to Clean the Data
Before Power Query, this is how we cleaned data
Yes, I know you should use Power Query to clean data and I demonstrated how to do that in my previous post. Sometimes it is easier to record a macro because a macro can clean the data in place.
Clearing Unlocked Cells in Excel
A macro makes it easy
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.
Page Breaks in Excel
Turn them off
When you set your print area or use the Page Break Preview View, Excel will show you the page breaks on the grid. If this annoys or distracts you, here is how to remove them.
It’s a one-line macro that turns off the page breaks in the current sheet.
Sub TurnOffPageBreaks() ActiveSheet.DisplayPageBreaks = False End Sub |
If you are new to macros then this blog post can take you through how to use them.
The above code can be copied and pasted into the code window.
If you want to turn page breaks off for all the sheets in the file use the code below.
Sub TurnOffAllPageBreaks() Dim ws For Each ws In Worksheets ws.DisplayPageBreaks = False Next ws End Sub |
I hope these code snippets are useful.
Excel Macros and Merged Cells
Merged cells are a problem
I don’t like merged cells. Here is one more reason.
Missing Chart Data in Excel
Another macro to the rescue
The default setting for charts in Excel is to hide the data on the chart if it is hidden on the sheet. I forgot that recently when I created a few charts using a workings area to hold the chart data. I later hid the workings with column grouping. Oops – when you hide the data in the charts go blank.
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.
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.