Using controls on worksheets

Hi all,

I decided I need a bit more content on my site for the average to intermediate Excel users:

Using controls on worksheets

Apart from input cells chained to cells with formulas, there are other options in Excel to make your spreadsheet model more interactive. You can add option buttons, check boxes and list boxes to your sheet to offer choices to the user. Moreover, you can add scroll bars and spinners to quickly control values in cells. These elements are called controls because they enable your user to control the content of your model. This article shows how to put controls to use in your model.

Enjoy the read and let me know what you think!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Preventing auto_open and Workbook_Open events from running

As a fulltime developer I oftentimes open files containing VBA and want to be able to run code. At the same time I sometimes want to prevent Workbook_Open event code or an Auto_Open macro from running.

With Excel 97 to 2003, this was simple: hold the shift key when you click “Enable macros”.

With Excel 2007 and 2010 it is less obvious how to do this, holding shift whilst clicking the enable button does not help. In the article mentioned below I have detailed out the exact steps required to prevent these macros from running under all possible circumstances.

Link: Preventing auto_open and Workbook_Open events from running

Enjoy the read and let me know what you think!!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

The Mystery of the Disappearing Range Name

Hi all,

As some of you may be aware, Excel 2007 has been exhibiting an obscure but very annoying bug. On some rare occasions, Range Names may disappear from your workbook after saving it.
I have never been able to detect any pattern in this behaviour, it just happens on and off.

I’m happy to say Microsoft created a hotfix for this bug.

###EDIT 2009-07-06 15:10 GMT: fixed a small mistake, you need SP1, NOT SP2###
Make sure you have Office 2007 updated to at least SP1; visit Microsoft Update to check if you need it.
After that, visit the hotfix page and request your hotfix download.

For more information about range names, read my series of articles on them here.

And if you use range names frequently (or plan to in future) do yourself a favour and download Name Manager.

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Adding RibbonX code to an Office OpenXML file using VBA

My previous post went a bit unnoticed, but I bet this one might interest some of the Office developers…

In that post, I showed how to access and modify existing parts of an Office OpenXML package. This opens up the path for us to add ribbon customisation code to an Office file. For this to happen, a couple of modifications were needed to the code in the class module I showed earlier. Fellow Excel MVP Ken Puls was kind enough to make some modifications to the class module, which I refined a little. The results are on this page

Enjoy!
Regards,
Jan Karel Pieterse

My own Euler problem

Hi everyone,

With all these Euler posts I thought, why not post my own?
Suppose the following:
I have a products table set up like this:

CodeProdName Price
0001Product1 556.68
0002Product2977.41
0003Product3350.62
0004Product4509.16
0005Product5748.4
0006Product6802.96

(list goes on to as much as 5000 products)
And I want to show this table in a userform and give the user some filtering possibilities (lets say on the first two columns, which may be unsorted).
Currently I load the list into a variant and add the items one at the time into a three column listbox.
As the user enters a search string, I have an If statement inside the loop to detect if an item needs to be added to the listbox or not. This (as you can imagine) is a slow process.
My task for you: how would you solve this problem so the filtering is as fast as possible?

Regards,

Jan Karel pieterse
www.jkp-ads.com

Styles in Excel

Hi all,

I’ve just published a new article on my website. Here is the introduction:

“This article explains how you can use styles to ease maintenance of your spreadsheet models.

Microsoft has made it very easy to dress up your worksheets with all sorts of fill patterns, borders and other frills. Because formatting of cells is often done in an ad-hoc manner, many spreadsheet pages look messy.

By consistently using cell styles (instead of changing parts of the cell’s formatting) you will be forced to think about the structure of your work. Religiously using styles may even force you to reconsider the overall structure of the entire spreadsheet model: The quality of the computational model itself may be positively affected.

I therefore consider Styles as being underused, underestimated and under exposed.”

Read on…

Enjoy!

Regards,

Jan Karel Pieterse
www.jkp-ads.com