Tab stops in excel - HELP!!

  • Thread starter Thread starter Fredy
  • Start date Start date
F

Fredy

Does anyone know how to set tab stops in excel 2000? I am
building a form, but i cannot get the tab key to move to
the next field and it is aggravating! HELP!!
 
Fredy,

The controls have a TabIndex property which allows you to control the order.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks, i see that property in the VB editor, but when i
look for that property in the excel design mode, it
doesn't appear. I am building the form with control boxes
right on top of the excel spreadsheet, not in the VB
editor. I cannot find a way to make the tab work. Any
ideas????
 
Fredy,

Sorry I thought you meant a userform.

As you correctly note, there is no TabIndex property for worksheet controls.
This because the TabOrder property is an inherited property, that is it
comes from the container that the control is situated in. The userform
container supplies this property, a worksheet container does not.

There is a solution though (Can't say I have tried it personally) that was
previously posted b y Rob Bovey. I attach it here.


'---------------------------------------------------------------------------
------------
You can still tab amongst controls on a worksheet, you just have to code it
yourself using
each control's KeyDown event procedure.

In the sample event procedure below I'll assume a hypothetical situation
where we have three textboxes: TextBoxPrevious, TextBoxCurrent, and
TextBoxNext. This event procedure shows you how to use VBA to emulate
tabbing behavior. Pressing Tab moves from TextBoxCurrent to TextBoxNext and
pressing Shift+Tab moves from TextBoxCurrent to TextBoxPrevious. The Up and
Down arrow keys and the Enter key are given similar behavior.

Private Sub TextBoxCurrent_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
ByVal Shift As Integer)
Dim bBackwards As Boolean
Select Case KeyCode
''' These are the only keys we care about.
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
''' Determine if we need to move backwards.
bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
''' In Excel 97 we must select a cell before activating another
control.
Sheet1.Range("A1").Select
''' Activate the appropriate control based on key(s) pressed.
If bBackwards Then TextBoxPrevious.Activate Else
TextBoxNext.Activate
Application.ScreenUpdating = True
End Select
End Sub
'---------------------------------------------------------------------------



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Wow! It worked. Thanks so much! Do you know how i could
write a simple formula to add the values of certain
textboxes and display in another? I don't even know which
event to use (change(), keydown(), etc). Do you know?
Also, what can you show me what a simple "if this value is
between this and this then this field = this, else equals
this). I am not at all familiar with VBA.

thanks,
 
Wow! It worked. Thanks so much! Do you know how i could
write a simple formula to add the values of certain
textboxes and display in another? I don't even know which
event to use (change(), keydown(), etc). Do you know?
Click to expand...

You could use the Textbox_Enter event and just add them up there

Private Sub TextBox3_Enter()

Textbox3.Text = Format(Cstr(Cdbl(Textbox1.Text) +
Cdbl(Textbox2.Text)),"#,##0.00")

End Sub
Also, what can you show me what a simple "if this value is
between this and this then this field = this, else equals
this). I am not at all familiar with VBA.

thanks,
Click to expand...

If Field1 < Field2 And Field1 > Field3 Then
Fieldx = Fieldy
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top