The Switch function

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.


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:

Select Case True
Case expr-1
Case expr-2
Case expr-n
End Select

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

Dim Choice As Long

Choice = Switch(OptionButton1.Value, 0, OptionButton2.Value, 1, OptionButton3.Value, 2)

Comment Control

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:

Toolbar working

The ‘Settings’ form:

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.

Limit a Listbox (Another approach)

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:

Option Explicit

Private Sub tbxFind_Change()
   Dim vList As Variant
   ‘Read the original list
  vList = Range(“A2”, Cells(Rows.Count, 1).End(xlUp)).Value
   ‘Convert it to a 1D array
  vList = Application.Transpose(vList)
   ‘Filter it
  vList = Filter(SourceArray:=vList, _
                  Match:=tbxFind.Value, _
                  Include:=True, _
   ‘Send it to the listbox
  lbxCustomers.List = vList
End Sub

Private Sub UserForm_Initialize()
   ‘Read the original list
  lbxCustomers.List = Range(“A2”, Cells(Rows.Count, 1).End(xlUp)).Value
End Sub

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.

Using Copy/Paste in userforms

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.

Copy/Paste toolbar

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