The Switch function

Posted on February 21, 2005 by Juan Pablo Gonzalez

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:

Select Case True
Case expr-1
value-1
Case expr-2
value-2
‘…
Case expr-n
value-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

Posted on February 19, 2005 by Juan Pablo Gonzalez

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:

Toolbar

The toolbar with the toggle on:

Toolbar working

The ‘Settings’ form:

Settings form

And the result:

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)

Posted on February 17, 2005 by Juan Pablo Gonzalez

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, _
Compare:=vbTextCompare)

‘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

Posted on December 7, 2004February 6, 2017 by Juan Pablo Gonzalez

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

Option Explicit

'Popup objects
Private cmdBar As CommandBar
Private WithEvents cmdCopyButton As CommandBarButton
Private WithEvents cmdPasteButton As CommandBarButton
 
'Useform to use
Private fmUserform As Object
 
'Control array of textbox
Private colControls As Collection
 
'Textbox Control
Private WithEvents tbControl As MSForms.TextBox
'Adds all the textbox in the userform to use the popup bar
Sub Initialize(ByVal UF As Object)
   Dim Ctl As MSForms.Control
   Dim cBar As clsBar
   For Each Ctl In UF.Controls
      If TypeName(Ctl) = "TextBox" Then
       
         'Check if we have initialized the control array
        If colControls Is Nothing Then
            Set colControls = New Collection
            Set fmUserform = UF
            'Create the popup
           CreateBar
         End If
          
         'Create a new instance of this class for each textbox
        Set cBar = New clsBar
         cBar.AssignControl Ctl, cmdBar
         'Add it to the control array
        colControls.Add cBar
      End If
   Next Ctl
End Sub
  
Private Sub Class_Terminate()
   'Delete the commandbar when the class is destroyed
  On Error Resume Next
   cmdBar.Delete
End Sub
 
'Click event of the copy button
Private Sub cmdCopyButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
   fmUserform.ActiveControl.Copy
   CancelDefault = True
End Sub
 
'Click event of the paste button
Private Sub cmdPasteButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
   fmUserform.ActiveControl.Paste
   CancelDefault = True
End Sub
 
'Right click event of each textbox
Private Sub tbControl_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
      ByVal X As Single, ByVal Y As Single)
       
   If Button = 2 And Shift = 0 Then
      'Display the popup
     cmdBar.ShowPopup
   End If
End Sub
 
Private Sub CreateBar()
   Set cmdBar = Application.CommandBars.Add(, msoBarPopup, False, True)
   'We’ll use the builtin Copy and Paste controls
  Set cmdCopyButton = cmdBar.Controls.Add(ID:=19)
   Set cmdPasteButton = cmdBar.Controls.Add(ID:=22)
End Sub
 
'Assigns the Textbox and the CommandBar to this instance of the class
Sub AssignControl(TB As MSForms.TextBox, Bar As CommandBar)
   Set tbControl = TB
   Set cmdBar = Bar
End Sub

Option Explicit

'Popup objects

Private cmdBar As CommandBar

Private WithEvents cmdCopyButton As CommandBarButton

Private WithEvents cmdPasteButton As CommandBarButton

'Useform to use

Private fmUserform As Object

'Control array of textbox

Private colControls As Collection

'Textbox Control

Private WithEvents tbControl As MSForms.TextBox

'Adds all the textbox in the userform to use the popup bar

Sub Initialize(ByVal UF As Object)

Dim Ctl As MSForms.Control

Dim cBar As clsBar

For Each Ctl In UF.Controls

If TypeName(Ctl) = "TextBox" Then

'Check if we have initialized the control array

If colControls Is Nothing Then

Set colControls = New Collection

Set fmUserform = UF

'Create the popup

CreateBar

End If

'Create a new instance of this class for each textbox

Set cBar = New clsBar

cBar.AssignControl Ctl, cmdBar

'Add it to the control array

colControls.Add cBar

End If

Next Ctl

End Sub

Private Sub Class_Terminate()

'Delete the commandbar when the class is destroyed

On Error Resume Next

cmdBar.Delete

End Sub

'Click event of the copy button

Private Sub cmdCopyButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)

fmUserform.ActiveControl.Copy

CancelDefault = True

End Sub

'Click event of the paste button

Private Sub cmdPasteButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)

fmUserform.ActiveControl.Paste

CancelDefault = True

End Sub

'Right click event of each textbox

Private Sub tbControl_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _

ByVal X As Single, ByVal Y As Single)

If Button = 2 And Shift = 0 Then

'Display the popup

cmdBar.ShowPopup

End If

End Sub

Private Sub CreateBar()

Set cmdBar = Application.CommandBars.Add(, msoBarPopup, False, True)

'We’ll use the builtin Copy and Paste controls

Set cmdCopyButton = cmdBar.Controls.Add(ID:=19)

Set cmdPasteButton = cmdBar.Controls.Add(ID:=22)

End Sub

'Assigns the Textbox and the CommandBar to this instance of the class

Sub AssignControl(TB As MSForms.TextBox, Bar As CommandBar)

Set tbControl = TB

Set cmdBar = Bar

End Sub

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:

Dim cBar As clsBar

Private Sub UserForm_Initialize()
   Set cBar = New clsBar
   cBar.Initialize Me
End Sub

Dim cBar As clsBar

Private Sub UserForm_Initialize()

Set cBar = New clsBar

cBar.Initialize Me

End Sub

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