Macro interferes with tabbing order

[Maury Markowitz]

I've been seeing this problem for a little while now, but I've only just
discovered what was causing it.

I have a form with a number of fields where users type in large numbers. To
make this easier, I wrote a macro called "addZerosToNumbersMacro" that
translates keys like "T" into three zeros (thousand) and "M" into six zeros
(million). I put this into the onChange event in any field where it makes
sense. Works great!

Except for one thing. If you type in the number longhand and press tab, the
field validates and moves onto the next field in the tab order. For instance,
entering "1-2-0-0-0-tab" will type in 12000 and move to the next field.
However, typing in "1-2-t-tab" does NOT move on to the next field. The cursor
stays there.

I'd appreciate any thoughts on why this might happen. I have a feeling it
has to do with the .SelStart (see below), but if that's the case, I'm not
sure how to avoid it.

The macro calls this function:

Public Function AddZerosToNumbers()
Dim currentControl As Control
Set currentControl = Screen.ActiveControl
If IsNull(currentControl.name) Then Exit Function

On Error GoTo EXITOUT

Dim didIt As Boolean
didIt = False

With currentControl
Dim lngPos, decPos As Long

' map out any number keys if they added them
lngPos = InStr(UCase(.Text), "H")
If lngPos > 0 Then
.Text = Left$(.Text, lngPos - 1) & "00" & Mid(.Text, lngPos + 1)
didIt = True
End If
lngPos = InStr(UCase(.Text), "K")
If lngPos > 0 Then
.Text = Left$(.Text, lngPos - 1) & "000" & Mid(.Text, lngPos + 1)
didIt = True
End If
lngPos = InStr(UCase(.Text), "T")
If lngPos > 0 Then
.Text = Left$(.Text, lngPos - 1) & "000" & Mid(.Text, lngPos + 1)
didIt = True
End If
lngPos = InStr(UCase(.Text), "M")
If lngPos > 0 Then
.Text = Left$(.Text, lngPos - 1) & "000000" & Mid(.Text, lngPos
+ 1)
didIt = True
End If

' and then move the insert point, either to the decimal, or the end
If didIt Then
decPos = InStr(.Text, ".")
If decPos > 0 Then
.SelStart = decPos - 1
Else
.SelStart = Len(.Text)
End If
End If
End With

EXITOUT:
End Function

 

[Maury Markowitz]

Nevermind... I sat at watched the user and they were not hitting tab. They
were hitting T and expecting that to expand the number AND move to the next
field.

I guess I could do that, but it seems like a bad idea. We don't expect it to
move to the next field if you press "0", so why would you if you press three
"0"'s?

Maury

 

[germaine.oliver]

It may not be a bad idea..if you can save a user keystrokes
predictably, that's a good idea. For example, when I enter my phone
number, I like it when it auto-tabs. I think it's sloppy programming
to make me hit tab after entering a US area code...

So if a user would never hit anything after the "t" or "m", it might
be a good plan to have it move to the next field. But then you have to
work a little harder (codewise) to figure out what the next field is
(or use Sendkeys, which is generally not a good idea).

Just MHO...