In some cases you might find that the RefreshAll command isn’t working as you’d hoped. For example, if you have a pivot table looking up data from a SQL database connection you need the pivot table to refresh after the connection but RefreshAll won’t necessarily do that.   To ensure one connection is refreshed at Read More …

When you are data cleansing within your VBA routine a common action you might need to do is re-map text values to a different name so they match another dataset you’re joining them to.   You can do this in VBA without having to specify each individual value in the code. In this example we Read More …

You can password-protect a sheet from being edited very easily, by selecting Protect Sheet from the Review menu on the ribbon.     However this standard protection doesn’t stop the user from being able to view the sheet, it just prevents them from being able to edit the sheet, choosing whether to enable filtering, formatting, etc.   Imagine Read More …

A little known, quite funky Application command in VBA is to set your spreadsheet to talk to you. You can either input a fixed message like the example below, or use strings to trigger Excel to speak out loud the contents of a cell on a particular event.     Application.Speech.Speak “Have a nice day”

Hiding the Sheet Tabs is something that can be very effective and useful if you are sharing your Excel workbook with colleagues. You may not want them going in and meddling with your calculation sheets, you may have added navigation buttons for the users instead or you may just want the workbook to look more Read More …

If your Excel file includes a load of VBA modules and you’re sharing it with other people, or others have access to your network, it is always worth sticking a password on to protect it. Amongst other reasons, this prevents people stealing your hard work and claiming it for themself, or trying to make a Read More …

In this article we use some VBA to remember which worksheet you have navigated away from, so that a ‘Go Back’ button can be placed to return to it.   We start with a menu page that has buttons navigating to our 4 worksheets.     1) Press Alt+F11 to open your VBA window. From Read More …

To stop users from unhiding sheets which they shouldn’t be accessing, hide them using VBA and they can only be unhidden using VBA.   In this example we have a Summary sheet and a Calculations sheet. We don’t want users meddling with the Calculations sheet.   You can right-click the sheet tab at the bottom Read More …

There are a few fairly simple bits of code you can use to delay Excel VBA code from running, or choose for it to run only on particular days or particular times. Maybe you are kicking off the Excel macro as a Scheduled Task, or maybe it runs when a button is clicked. If someone Read More …

There might be several reasons why you want to check whether multiple cells have been selected by your user, but here’s one example of a survey where we ask the employee which office they are based in.     You can use VBA to check whether the user has clicked a cell in the tick Read More …