August 12, 2007

Textbox Tips

Here's a few things you can do with textboxes on userforms. As you can see, I have a userform with a textbox and 2 buttons. Those buttons don't matter for this demo, the textbox does what is required.

Password Characters
I'm not sure how useful this really is. If you are worried about someone looking over your shoulder while you type something in, I'd be inclined to go have a cup of coffee and wait until they go away. Maybe I'll show how I get around key loggers too someday :-)

Select the Textbox and push F4 if the Property Window in the VBE if not already visible. Find Password Characters, then enter a character of choice to hide what you type in the textbox. An asterisk seems to be the one everyone uses. Looks nice if nothing else.

Limit Number of Characters
This is getting better. You could check the number of characters once OK is pushed but it might a better idea to do this while the user is typing (imagine if you had several textboxes and had to retype them, this way is more user friendly)

Assuming our textbox is called Textbox1, place this code withing the userform to disallow anything more than 10 characters.

Private Sub TextBox1_Change()
    If Len(TextBox1.Text) > 10 Then
        TextBox1.Text = Left(TextBox1.Text, 10)
        MsgBox "Don't enter more than 10 digits!", , "Input Error"
        Exit Sub
    End If
End Sub

Enter more than the limit and a mesage box appears.

Limit Type of Characters
I chose a telephone number rather than password for this example because I want something that consists of numbers only. The below code will see to that.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii < 48 Or KeyAscii > 57 Then
        MsgBox "Only digits are allowed!", , "Input Error"
        KeyAscii = 0
    End If
End Sub

Enter a letter and an error messsage appears.

There's a lot more things you can do of course but these are three of my favorites.

Special Comment
Jon Peltier wrote to me and suggested a more user friendly alternative. Rather than message boxes straight off, his idea is to change the color the text box plus use a caption as an indication, then use a message box only if the user continues anyway. Here is his code below.

Function ValidateTextbox1(sMsg As String)
   ValidateTextbox1 = True
   Textbox1.BackColor = iFILL_UNHIGHLIGHT
   If ... Then
       ValidateTextbox1 = False
       Textbox1.BackColor = iFILL_HIGHLIGHT
       sMsg = "Suitable error message here"
   End If
End Function

Private Sub TextBox1_Change()
   Dim sMsg As String
   If Not ValidateTextbox1(sMsg) Then
       lblError.Caption = sMsg
   End If
End Sub

Private Sub btnOK_Click()
   Dim sMsg As String
   If Not ValidateTextbox1(sMsg) Then
       lblError.Caption = sMsg
       MsgBox sMsg, vbExclamation, "Input Error"
   End If
End Sub

Good design is the mark of a true professional - thanks Jon!  

Posted by andrewe at 19:10

August 05, 2007

What's Your Address?

No, I'm not trying to pick anyone up. I get enough exercise anyway :-)

I have been working with ranges a lot recently, over and over again. And it has come to my attention that I like to know what range is selected at any given time. You can look at the Name Box, but that only show the active cell.

So I thought this would be a nice thing to add to one of my favourite and most used addins - the New Book Navigator. (I really should rename this somethime, huh?)

Anyway, at the right of the toolbar, you can see this. Look at the second caption from the right from now.

Select somewhere else and you see something like this.

That's getting there, but let's add some extra stuff. Push the caption and a userform will appear to allow you switch between A1 or R1C1 styles, and Absolute, Relative or Mixed References. Or you can copy the range as per the caption to the clipboard, by pushing both the caption and Ctrl key at the same time. That's starting to look better, right?.

One last thing. Although you can briefly see the number or rows and columns while selecting a range, it remains displayed when using the toolbar on the far right caption. And you can see the number of selected cells (blank or otherwise) and areas if you hover your mouse cursor above as a tootip.

Download it here.

There's more stuff on the way too :-)
Posted by andrewe at 17:07