Daily Dose of Excel

Hi all,

For a project I needed a quick way to display the content of an array to the user. I didn’t want to use a worksheet, but opted for a userform. The data I wanted to show was contained in an array. The array contained something like:

So I figured I’d put a listbox on a userform and make sure the column widths of the listbox resize with the data I want shown.
I wanted it to look like this:

That proved far from easy…

Read on…

Enjoy!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Hi all

New page on my site with a very useful add-in from Jim Rech.
http://www.rondebruin.nl/vbebookmarks.htm

VBEBookmarks.dll is a simple COM Add-in created by Jim Rech for the Microsoft Office 2000-2007 Visual
Basic Editor which lets you bookmark up to five locations in VBA projects for easy navigation among them.

Have fun

Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi All,

As many of you know I give away a number of tools for Excel through my site www.jkp-ads.com. Today I have updated the two most popular downloads:

Name Manager (which I created together with Charles Williams, www.decisionmodels.com):

Most important change: the unused names filter now includes objects in its search, as well as VBA code. It makes the filter much slower, but way more useful in my opinion. Also, I have added the Greek character set so Name Manager doesn’t (wrongfully) think range names with Greek characters are corrupt.

So far, Name Manager has been downloaded about 50,000 times since I posted it on my site.

Flexfind

I have updated the user interface of Flexfind so (in my opinion) it is easier to use. Also, I have mimicked the find all behaviour of Excel: if you select multiple items in the found items list, Flexfind will create a (multiple) selection of areas of the found cells.

Flexfind is less popular than Name Manager, the download count is at about 23,000.

Enjoy!

Of course I am open to any comments, suggestions and -most importantly- lots of praise

Regards,
Jan Karel Pieterse
www.jkp-ads.com

Hi all,

Let me start this post with a shameless plug: Charles Williams and I developed Name Manager. From my website alone this tool is good for about 60 downloads a day.

Just a couple of weeks ago, all of a sudden Charles and I started receiving complaints about compile errors, which neither of us could reproduce. Because the version we had available back then used the treeview control from the Windows common controls library, we blamed that control. And indeed, removing the control from the form that housed it fixed the problem for some of our users. But not for all.

Since this kind of errors is extremely hard to troubleshoot, I thought it might be useful to share our experiences.

Luckily we found a user who was willing to help us trouble-shoot the matter. We asked him to do all sorts of things: try on a different client, try logging on as administrator, removing accounts from client, you name it.
After exchanging a host of emails and screenshots and trying all sorts of variations we discovered the culprit: problematic .EXD files in one of the system folders of the client computer (these are just an example, your system may show others too or even none at all):

These files are typically stored in this location:

C:\Documents and Settings\[UserName]\Application Data\Microsoft\Forms\

So far, removing these files from that folder has resolved the issue for the people that experienced trouble with Name Manager.

So, if you distribute an addin to other users and you get a complaint about compile errors in your work: start off by asking the user to weed out the Forms folder I showed above. If it doesn’t help, it certainly wont hurt!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Hi all

When you open your Excel 97-2003 file in Excel 2007, one that creates its own menus,
the first thing you notice is that you don’t see your menus. Where’d they go? Then you discover
them - shunted over on the Add-ins tab. Not exactly the ideal user interface.
How are your users going to find them there?

What you want is an application that runs as a first class citizen in Excel 2007, one that takes full advantage
of the ribbon, and yet runs as it always did in Excel 2003. Yet you do not want to maintain two versions of
your app. Two versions are always a nightmare, so difficult to keep in sync.

So, is this possible? Is it possible to have one version that’s at home in Excel 2007 and Excel 2003? Yes it is;
there are two approaches from me and Jim Rech of this problem on this new page on my website.

http://www.rondebruin.nl/compatiblemenu.htm

If you have suggestions or other feedback let me know.

Ron de Bruin
http://www.rondebruin.nl/tips.htm

You know the situation: You have carefully setup a workbook with intricate Validation schemes. But then along comes your user and he copies and pastes at will. Result: Validation zapped, workbook structure violated.

What to do? The only way I find to be reliable is to catch all possible paste operations. But this isn’t very easy, since there are a zilion ways to paste.

I have put together a sample workbook and an explanatory article on how one could go about protecting a workbook by intercepting paste operations.

Let me know what you think!!

Regards,

Jan Karel Pieterse
www.jkp-ads.com