Dependent and Non Dependent Comboboxes

Cascading combo boxes have been done online and I had a problem recently which knocked me for 6 as we say here in Australia.  Anyone who deals in cascading combo boxes will know only too well that if you are using a list and the Indirect formula to manage the combo boxes then the process can get very unruly very quickly.  One of the issues with comboboxes is that they are set up to work in sequence.  Combo box 1 feeds into combobox 2 which feeds combo box 3.  The combo boxes need to be filled out in that order.

Read More

Excel Change Print Area with VBA

Setting the print area on a moving range can be a difficult and frustrating thing to trap.  You may wish to trap a specific range with your print area or have the print range only consider certain columns.  Let's say you have a couple of helper columns in an Excel workbook and don't want these columns to be printed but you want the columns to be visible.  You could manually set the print area to exclude the print area but now you have more data which you have added and you would like the print area to update in kind.

Read More
Tags , , , ,

VBA Check if Sheet Exists

I have a solution for checking if an Excel worksheet exists or not.  This can come in handy if you are creating a new sheet based on  a dataset.  You check for the new sheet and if the sheet exists you can alter the old sheet if the sheet does not exist then you can create a new sheet.

If Not Evaluate("ISREF('" & [A1] & "'!A1)") Then

Where Excel cell [A1] contains the name of the sheet you are checking for.

Now if this procedure was part of a loop you may wish to check all of the cells from say A1 to the last used row in column A.  Then the following might be more relevant.

Read More
Tags , , , ,

Copy and Paste an Excel Chart into PowerPoint with VBA

Creating Powerpoint presentations automatically is handy if you want the power to control another office application.  In this post I will copy a chart in Excel and paste it into a fresh instance of Power Point.  I will show two examples, once where the data will be pasted into a presentation with a title and one without.   This is a building blocks approach, creating a procedure which pastes one chart might seem like time better spent done manually but if there were 20 charts it would be faster for VBA to do the work for you. 

Read More

Import Website Data to Excel

I was recently asked to repair a webquery which had gone bad.  The company controlling the website had changed the layout of the content.  I decided to extract the data from the website into excel using VBA and a call on the HTML tables inside of the website itself.  Obviously this technique works best where there are dedicated tables on a website.  It does not discriminate as all data from the page is imported but with some smart manipulation you can inject some VBA to extract the relevant tables into a fresh worksheet.

Read More

Excel VBA to Sort an Array

I was recently researching the sorting of an array.  I sat in my reading chair and read through John Walkenback’s excellent Power Programming tome on the subject and did some research online when I came across the brilliant .Net feature System Collections ArrayList.  What a find it was as this feature allows you to sort an array and send that array right back into Excel.

Read More

Excel Merge Sheets to Master

This article draws on the post from the site which is dedicated to merging worksheets in a workbook into a single master sheet Consolidate Sheets.  This technique can be significantly simplified with a VBA macro.   I have posted this type of reply on forums dozens of times.  Howeve this reply differs just slightly.  The following will consolidate data so the data tables appear side by side in the consolidaiton tab.  So if Sheet 1 appears:

Read More