Selecting the Contents of a Text Box in a User Form

[Montana DOJ Help Desk]

Word 2000

I have a user form that contains a number of command buttons and two text
boxes. One text box displays the records in a random access file. The
other text box is named tbGotoRecord, and it allows the user to input a
record number so that they can go directly to that record.

Everything on the form works perfectly, with one small annoyance. If the
user enters a number in the tbGotoRecord text box and then clicks the Go
button, the correct record will be pulled up, but the contents of the
tbGotoRecord text box will no longer be selected. To go to another record,
the user needs to click back into the text box. Not a big deal, but I'd
like to fix it if I can.

For tbGotoRecord the Tab Index = 0, and Tab Stop = True. How can I make the
code select the entire contents of tbGotoRecord after each time the user
clicks the Go button?

Thanks for any help that you can provide!

-- Tom

State of Montana
Department of Justice Help Desk

"Making the world a safer place."

 

[Jay Freedman]

Hi Tom,

Use this bit of code:

With tbGotoRecord
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With

 

[Montana DOJ Help Desk]

Jay,

Thanks for the reply. Your code works great when the Go button in the form
is clicked. However, if the user exits the field by hitting the TAB key or
the ENTER key on the keyboard, the contents of the text box will not be
selected. What is happening is that it goes to the next control in the tab
list--even though the tbGotoRecord_AfterUpdate() event calls the same code
as clicking on the Go button. Is there any way to prevent this from
happening when the user exits the field by hitting the TAB or Enter key?

-- Tom

State of Montana
Department of Justice Help Desk

"Making the world a safer place."

 

[Jay Freedman]

Hi Tom,

Well, you never mentioned anything but the Go button in your first post... I
have a feeling that there's a lot more you aren't mentioning. For example,
how does the user close your userform? What other controls are there, and
what are they supposed to do?

You really don't want to use the AfterUpdate event, because it's always
followed by the Exit event which will undo the SetFocus. You may be able to
do what you want in the Exit event handler, or in the Enter event handlers
of the other controls. Be careful not to set up a situation where the user
can't get out of the tbGotoRecord box at all.

 

[Montana DOJ Help Desk]

Jay,

I didn't mention the other controls in my original post because I thought
that it would just confuse the issue, and since the same section of code is
being called, I figured the solution would work for all cases anyway. I was
partially correct on that point.

All the buttons that call the same code as the Go button, work just fine
with the solution that you posted. The only time it doesn't work is when
the user exits the tbGotoRecord box by hitting the ENTER key or the TAB key.
I had thought that since the AfterUpdate event calls the same code used by
the Go button, that it would work for those instances as well. But that is
not the case. As you pointed out, AfterUpdate is followed by the Exit
event, which undoes SetFocus.

After looking at the issue some more, I don't think it can be made to work
the way I was envisioning. The AfterUpdate and Exit events cause the focus
to be lost. So I tried doing it through the Enter events of the command
buttons, and that causes the command buttons not to work. I think that what
I have now, with it working whenever a command button is clicked, is the
best I'm going to do.

If you're curious, here is a more in-depth explaination of the form and what
it does.

The form reads an error log. There is a large text box which displays the
current record (error) in the log. The small text box named tbGotoRecord
allows the user to input a record number, and clicking Go moves directly to
that record. There are 7 command buttons in all:

First Record
Previous Record
Next Record
Last Record
Go
Lookup Error
Close

The first 5 buttons all do essentially the same thing--they move to another
record--so they all call the GotoRecord subroutine. However, with the first
4 buttons (I call these collectively the "navigation buttons", the record
number can be easily calculated, whereas the Go button has to get the value
input by the user in the tbGotoRecord box, and that value has to be checked
to make sure that the user has not entered an invalid value (A negative
number, a number greater than the total number of records in the file, or a
non-numeric character). The GotoRecord subroutine knows when a navigation
button has been clicked because NavButton = True. Otherwise, NavButton =
False. When NavButton = True, the checks on the tbGotoRecord value are
skipped, and when NavButton = False, the checks are run.

I have the AfterUpdate event calling the same routine as the Go button and
the navigations buttons because my goal was to make exiting the field with
the ENTER key and the TAB key the functional equivalent of clicking the Go
button. While I have not been able to get this working as desired, it is
interesting to note that when going to the first record in the file, it does
work. That is, the user can hit the ENTER key or the TAB key on the
keyboard, and the value in tbGotoRecord will be selected. I think that this
has something to do with the fact that when the first record in the file is
reached, the FirstRecord and PreviousRecord buttons get deactived (ditto for
the NextRecord and LastRecord buttons when the last record in the file is
reached). I've tried to pin down why this happens to see if I can somehow
exploit it to achieve the effect I'm going for, but thus far, I've not been
able to make it work with any record except the first one.

The Lookup Error button just opens the Help file for currently displayed
error in another window. The Close button unloads the form, and there is a
terminate event for the form that makes sure the open file (from which the
error data is being read) gets closed.

-- Tom

State of Montana
Department of Justice Help Desk

"Making the world a safer place."

 

[Jay Freedman]

Hi Tom,

I think I can solve half your problem... In the properties for the Go
button, set the Default property to True. That will make the Enter key
a synonym for clicking that button.

You might be able to handle the Tab key by writing code in the KeyDown
procedure for the tbGoToRecord box, testing for the Tab key, and
calling the navigation code if that was the key pressed (otherwise do
nothing).

 

[Montana DOJ Help Desk]

Jay,

Your suggestion that I set the Go button Default property to True did the
trick for the ENTER key.

I still can't get it to work for the TAB key, and I'm not sure that it will
be possible. I tried the KeyDown event of the tbGotRecord box as you
suggested, and I thought that it was going to work, but I ran into a problem
that I didn't anticipate, although it seems obvious in retrospect. When the
user tries to type a number into the tbGotoRecord box, the KeyDown event is
triggered, it runs as expected, and at conclusion of the procedure you get a
"bonk" sound, but no value is entered into the tbGotoRecord box. In other
words, the user is prevented from entering a value into the box.

Oh well, it was an interesting exercise nonetheless, and thanks to your
help, I'm happy with the way it is working now.

However, having been through this exercise, I now have a couple of more
questions regarding how to get the codes for various keys. I looked in the
VBA Help and read about ANSI, ASCII, CHR and the character set, SendKeys,
FindKeys, KeyDown, KeyUp, KeyPress, KeyCode, etc., and I never did find the
codes for the ENTER and TAB keys in Help. I ended up finding them by doing
a Google search. Is there a topic in the VBA Help that lists the codes for
keys like TAB, ENTER, PAGE UP, PAGE DOWN, etc.?

At one point, I tried to use KeyCode in a regular subroutine within the form
(so it was outside of a KeyDown or KeyUp event) and it caused an error. Is
there an equivalent command to KeyCode that can be used outside of an event
procedure?

Finally, I have been looking in the VBA Help for a command that would be
able to return a value representing the last keyboard key that was pressed
by the user, but I haven't found anything like that yet. Is there any such
command or function?

-- Tom

State of Montana
Department of Justice Help Desk

"Making the world a safer place."

 

[Jay Freedman]

Hi Tom,

I think the missing bit is that when the KeyDown event finds a Tab
character, you need to change the KeyCode parameter's value to 0. That
"eats" the Tab so the cursor doesn't jump out of the textbox. If any
other character is found, you do nothing, and that character just
passes through. Like this:

Private Sub tbGotoRecord_KeyDown(ByVal KeyCode As _
MSForms.ReturnInteger, _
ByVal Shift As Integer)
If KeyCode = vbKeyTab Then
KeyCode = 0
Navigate ' call a separate function to navigate
End If
End Sub

You're right that the Help is very spotty about listing values of
constants. A better source for this is the Object Browser (F2 in the
VBA editor). You can put any part of the topic of interest into the
search box, and the browser will return all the objects and constants
that contain that text. For example, look for "Tab" and you'll find --
among other things -- that you can use vbKeyTab (a member of the
KeyCodeConstants enumeration) or wdKeyTab (a member of the WdKey
enumeration), both of which are constants with the value 9 (which is
also the ASCII value of a tab character).

 

[Montana DOJ Help Desk]

Jay,

Thanks for all your help! Yep, that was the problem. That's what I was
trying to do the other night, but I never thought about setting KeyCode = 0.

The other problem I had when I was working on this the other night was that
when the Tab key was used, the computer would ding at me, but nothing would
show up in the text box. I added Me.Repaint to the bottom of the routine
and that fixed the problem. Oddly, this was only a problem when stepping
through the code--the form does not need to be repainted if the macros are
run normally.

I'm glad that I decided to ask about this problem, because while it wasn't
really a big deal, I did learn a lot during the processes of fixing the
problem. And now that I have the solution, I decided to get a little fancy.
I re-wrote the routine as follows so that it works with Page Up, Page Down,
Home, End, Left Arrow, Right Arrow, Up Arrow, and Down Arrow.

Private Sub tbGotoRecord_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
ByVal Shift As Integer)

Select Case KeyCode

Case 9, 13
KeyCode = 0
GotoRecord

Case 33, 38, 39
KeyCode = 0
NavControl = 3
CalculateRecordNumber

Case 34, 37, 40
KeyCode = 0
NavControl = 2
CalculateRecordNumber

Case 35
KeyCode = 0
NavControl = 4
CalculateRecordNumber

Case 36
KeyCode = 0
NavControl = 1
CalculateRecordNumber

End Select

End Sub

Sub CalculateRecordNumber()

If NavControl = 1 Then CurrentRecord = 1
If NavControl = 2 Then CurrentRecord = Seek(FileNumber) - 2
If NavControl = 3 Then CurrentRecord = Seek(FileNumber)
If NavControl = 4 Then CurrentRecord = (LOF(FileNumber) / TypeSize)

If CurrentRecord < 1 Then CurrentRecord = 1
If CurrentRecord > LastRecord Then CurrentRecord = LastRecord

GotoRecord

End Sub

-- Tom

State of Montana
Department of Justice Help Desk

"Making the world a safer place."

 

[Jay Freedman]

Oh no, I've created another VBA Monster! <chuckle>

Here are two more suggestions, both aimed at making the code more
self-documenting:

(1) Use the named constants from the KeyCodeConstants collection
instead of the numeric values:

Select Case KeyCode
Case vbKeyTab, vbKeyReturn
...

Case vbKeyPageUp, vbKeyUp, vbKeyRight
...

Case vbKeyPageDown, vbKeyLeft, vbKeyDown
...

Case vbKeyEnd
...

Case vbKeyHome
...

(2) Create an enumeration data type for NavControl instead of the
numeric values 1 to 4. Look up the Enum keyword. You can also replace
the four If statements in CalculateRecordNumber with a Select Case
structure (which can use enumerated values in the case expressions).

If you come back to this code after a few months, when you've
forgotten the details of how you designed it, you'll be glad for any
breadcrumb you've left to show how it works! (In my case, sometimes it
takes only a few days...)

 

[Montana DOJ Help Desk]

I've been a VBA monster for a while now. In fact, I've been terrorizing
this newsgroup with question for the past year--on the SAME project!

I like your idea of using the named constants in the code. I actually
included the named constants in my comments within the code, but I removed
those comments when I posted the code just to shorten up the post.

I'll give your other suggestions a try when I get the chance. Hopefully,
that will be tonight, but might not be for a couple of days.

-- Tom

State of Montana
Department of Justice Help Desk

"Making the world a safer place."

 

[Montana DOJ Help Desk]

I gave the Enum idea a try. I can see where that would be handy in a lot of
cases, but I ended up not using it because I found that I don't need to
create an enumeration. I just changed NavControl to a string data type, and
then set it to "First Record", "Previous Record", etc.

But I did decide to use the Select Case in CalculateRecordNumber, as you
suggested, because it's a little easier to understand, and I can make the
checks on the CurrentRecord value only when needed. So the routine is now
as follows:

Sub CalculateRecordNumber()

Select Case NavControl

Case "First Record"
CurrentRecord = 1

Case "Previous Record"
CurrentRecord = Seek(FileNumber) - 2
If CurrentRecord < 1 Then CurrentRecord = 1

Case "Next Record"
CurrentRecord = Seek(FileNumber)
If CurrentRecord > LastRecord Then CurrentRecord = LastRecord

Case "Last Record"
CurrentRecord = (LOF(FileNumber) / TypeSize)

End Select

GoToRecord

End Sub

-- Tom

State of Montana
Department of Justice Help Desk

"Making the world a safer place."