Worksheet Controls

[Fabio Coatis]

1 - Is there a way to establish the tab order of worksheet controls, or even
have the Tab or Enter key select them continuously ( as they do in cells)?

2- Can I have a number inside a TextBox formatted?

Thanks in advance

[Rob Bovey]

Hi Fabio,

<<1 - Is there a way to establish the tab order of worksheet controls, or even
have the Tab or Enter key select them continuously ( as they do in cells)?>>

There's no automatic way of doing it that I know. You have to do it yourself
using the KeyDown event for each of the controls on your worksheet. If the
KeyCode argument of the KeyDown event for a given control tells you that TAB or
ENTER has been pressed, you explicitly activate the control that you want to be
next in line. Here's a simple example:

Private Sub TextBox2_KeyDown( _
ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
''' Check if the TAB or ENTER key was pressed.
If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
''' If this is Excel 97 you must select a cell
''' before attempting to activate another control.
If Val(Application.Version) < 9 Then
Me.Range("A1").Select
End If
''' Move the focus appropriately.
If CBool(Shift And 1) Then
''' The user was holding down the SHIFT key
''' move back to the previous control.
TextBox1.Activate
Else
''' Move to the next control.
TextBox3.Activate
End If
End If
End Sub

<<2- Can I have a number inside a TextBox formatted?>>

Yes, but again you need to handle this yourself. Here's an example for
currency formatting:

Private Sub TextBox1_Change()
Dim szValue As String
szValue = Trim$(TextBox1.Text)
If Len(szValue) > 0 And IsNumeric(szValue) Then
TextBox1.Text = Format$(CDbl(szValue), "$#,##0.00")
End If
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

"Fabio Coatis" <xa...@uol.com.br> wrote in message
news:ueW#FtC9$GA.289@cppssbbsa04...

[Fabio Coatis]

Thanks Rob,

Your post was extremely helpful.

"Rob Bovey" <Rob_...@msn.com> escreveu na mensagem
news:OZlJtbD9$GA.243@cppssbbsa04...