Daily Dose of Excel

While working with userforms, you sometimes end up with more controls than could fit on the screen. Altough you should try to keep the forms simple and uncluttered, well, there are circumstances where this is not possible.

Consider a form with 3 frames, like the following.

As you can see, Frame3 is not completely visible. One possible solution is to use the Userform’s Scrollbars. The first thing that needs to be done is change the properites of the form, to display the vertical scrollbar, so change the

to

If you run the userform again, the scrollbar will appear, but it won’t work yet. That is because we still need to tell it how much we need to scroll. To do this, we change the

and/or

properties. This form has a

of

, so we need to set the

property to a value larger than this. Setting it to

works fine, as you can see in the image.

To get the hyperlink of a cell you can use a simple UDF, like this:

You can then use it in your worksheet like

=RETURNHYPERLINK(A13)

I check for the Address and the SubAddress properties because a hyperlink to a “place in this document” for example is not stored in the Address property (which appears empty), but in the SubAddress.

Connecting to “non-standard” databases can become quite an interesting task. A while ago I wanted to run some queries on MrExcel’s Message Board, which runs on a PHP / MySQL environment.

Getting the ODBC driver wasn’t a problem, just go here (There used to be a MyOLE driver, but I guess it’s not supported anymore). It was filling the values that was a challenging task.

Here’s a screenshot of what the ODBC config page looks like:

The server name was kind of easy. It turned out to be the same URL as the ftp address. Then, there was the chase for the username and password. I have to admit that I finally restored to cheating, modifying some of the php pages so they would print them to the page, to see what the database was *really* using, because for some strange reason, the ones that we had were not working.

After that you should be able to get a list of the available databases in that server.

When you have that working you can use it just as you would any other ODBC connection. Here, for example, I got a list of the last 10 posts and who wrote them.

Aaron Blood over at XL-Logic has some interesting challenges. One of them caught my eye, and here’s my stab at it.

It’s the ‘Advanced Range Detection‘.

What Aaron has to say:

I want to process the above range (Table 1) with a macro so all the text is highlighted. Easy enough right?

So I create a macro to find the text cells and change the background color to green.

…

The problem is, in Table 2 above, I want the green background applied to the entire sentenceu or at least just the cells the that the sentence carries into. The challenge is to create a macro that can do the highlighting so it appears like Table 3 below. Good Luck!

You can download the file here.

Here’s what my code produces:

Note: I wrote and tested the code in Excel 2003. It should work ok in 2000 and XP, but I’m not sure about the way those two versions handle the pixel count at the left of each cell, so the end result may vary from the image. However, there’s a TOLERANCE constant that may be adjusted, as well as a version specific function that can be modified to account for the difference between them.

Have any comments on the code ? improvements ? other ways to attack this problem ? I’d like to hear them. For example, I showed the file to Stephen Bullen to ask him about one thing, and he suggested a different approach using a userform and a label (which produces a slight different result on the measure of the string… not sure why though…), but avoids all the API calls that my code uses.

Every once in a while there’s a question in the newsgroups on how to create a Table of contents for a workbook. The easiest solution to this is some VBA that loops through the Worksheets() or Sheets() collection, and places that information in the TOC sheet.

There’s another way, using the GET.WORKBOOK() XLM function, and some defined names.

First, insert a sheet (preferably the first sheet in the workbook) and call it Contents.

Now, press Ctrl F3, and define two names, one

BookName

that refers to

and a second one,

Sheets

that refers to

that creates an array of each sheet in the workbook.

And create another name

Now, onto the contents:

In A2, put

and drag it down all the way to say, A50.

Now, the hyperlink in column B gets a little tricky, because the HYPERLINK() function requires us to put the Workbook name in it… kind of strange, but it does, like this

Now, why doesn’t just

work ? ask MS that… , anyway, back to the subject… put this in B2 and drag it down:

After some quick formatting, I ended up with this:

The main advantage of this method: It updates itself automatically when sheets are renamed, moved, deleted, etc.

The main disadvantage; if your workbook grows larger than the number of cells that you originally used, the Table of contents will display incomplete.

One of the cool things that I’ve found while playing in VB.Net is the ErrorProvider control. It’s a very “clean” way of giving feedback to the users, without having to bother them with messages or “get in the way” popups.

So I created this class that works in VBA, that basically does the same thing. I didn’t implement it completely (for example, the ErrorProvider control in VB.Net allows you to display errors inside a DataGrid, mine doesn’t, at least for now )

Here’s a couple of screenshots that I created:

It shows the four basic properties (one of which is missing in the .Net version) that can be modified:

• Blink Rate: Amount of milliseconds between blinks.
• Blink Style: Can be “Always blink”, “Blink if different error” and “Never blink”
• Blink Times: This is the one missing in .Net. Number of times that the image should blink.
• Icon: (Double click on the image to change it). Full path of the image to display (for best results, use a 16 x 16 icon)

Now, how to use the class.

Here’s the code behind the sample userform:

Here is a link to the file. Enjoy !

I rediscovered this very cool function while writing the CommentsControl add-in, and I thought that it’s, unfortunately, rarely used.

Here’s how MS defines it:

Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.

Syntax

Switch(expr-1, value-1[, expr-2, value-2 ... [, expr-n,value-n]])

Basically, think of it as a special Select Case statement, like this:

The main advantage is that it is compact and easy to write, but the main disadvantage is that it evaluates *all* the expressions, like IIF(), so if one of them returns an error, Switch() will fail.

In my case, I used it to test 3 OptionButtons, like this

Have you ever tried to change the way comments appear in Excel ? you know, remove the username information, or change the background color, or even change the color of the comment indicator and have things *stay* that way ?

Well, I’ve heard that quite a few times, so I decided to build something to help. It’s a simple Add-In called ‘Comment Control’. It allows you to change the text that appears by default when you insert a comment (or don’t have text at all), change the font, the background color and the color of the comment indicator.

It works with a simple toggle button, so you can turn it on / off easily.

It will NOT affect existing comments, only new ones.

Here are some screen shots:

The toolbar:

The toolbar with the toggle on:

The ‘Settings’ form:

And the result:

You can download it from here.

If you want to look at the code, the password is ‘a’ (No quotes). Tell me what you think !

Edit: Updated on 2/21/2005, no longer requires the comdlg32.ocx file to work.

Yesterday, Dick showed how to limit a ListBox by using the Change event of a TextBox.

Well, there’s another way to do the same thing, using the Filter function, available in VB6 (meaning from Excel 2000 and above).

So, using the same userform, I put all the Northwind customers in column A. Then, I put the following code in the userform module:

The Filter function works like this:

Filter(sourcesrray, match[, include[, compare]])

It requires a sourcearray, which is a one dimensional array (which is the only issue with the function, you can’t filter more “complex” arrays), and a string, ‘match’. It will search all the items in the array and return those that include that string.

Note that it doesn’t work the same way as the ‘Like’ operator, because it doesn’t use any wildcards.

You can make it *exclude* the items that have the match string by changing the ‘Include’ parameter (it assumes a value of True by default).

The last argument, ‘Compare’, enables you to do a binary comparison or text comparison of the texts, or use the value set in the Option Compare statement. In this case, I force it to text comparison, so ‘AAA’ equals ‘aaa’.

This method should work much faster than comparing each item in the array one by one.

Ok, first post over here… I hope I can add some useful stuff, it’s getting hard with all these tremendous authors !

When using a userform with some textbox, it is usually useful to allow the user to right-click on the control to allow access to some “standard” commands, like Copy and Paste, just like other Windows application provide. Unfortunately, there is not a direct way to do this, you have to create the CommandBar, and control the actions that happen after that, as well as including the code for each textbox that you want to control.

I wrote this class module to help in this process. Simply add the class module to your project, add three lines of code to your userform module and you’re done !

Here’s how to do it. First, insert a new class module and name it ‘clsBar’. Now, copy and paste this code in there

Now, to see how this works, create a new userform, and add a couple of textbox controls to it. Switch to the code pane and enter this code there:

Now run the userform. If you right click either of the controls, you’ll see a popup bar that allows you to copy and paste the contents of the control.

I’ll explain how the code works in a future post.