Business Analytics

To populate a combo box on a userform in Excel you need to use VBA. Firstly add a combo box to a userform in the VBA window (Alt-F11) of your spreadsheet.

The VBA code below shows how to populate a 2 column combo box. Add this code to the Userform Initialize event to populate the combo box when the form is loaded.

You might need to add a Reference to make the ADODB part of this work. Go to Tools – References in the VBA window and find the highest number Microsoft ActiveX Data Objects 2.X Library. Select the highest 2.X library.

Read more…

To select a sheet in Excel using VBA use the following syntax: Sheets(“Sheetname”).select

If you ever have to deal with long strings of freetext it’s likely you’ve come across random line feed and carriage returns in that text causing you headaches when it comes to manipulating the text.

Once you know how it’s quite a simple problem to deal with. This article describes how to remove the carriage returns and line feeds in SQL, Excel and Access. Read more…