Move (tab) between objects

  • Thread starter Thread starter andy
  • Start date Start date
A

andy

Pardon this post if it is a duplicate. I submitted a post
this morning, but do not see it, even after doing a search.

I have a sheet which I have put objects (radio buttons,
combo boxes, and text boxes). I have unchecked the protect
for these objects, and then protected the sheet itself. I
hope in this way to be able to have the user move (tab)
between only the objects on the sheet. When on an object,
and I press tab, or shift/tab, it does not move (advance
or go back an object), why? How do I get the user to go to
the next available object without having to use the mouse?

Thank you in advance.
God bless you
 
Andy,

This was my response to the previous post.




Here is a copy of a post I gave yesterday in response to a similar
question. The OP has posted back that it worked well.


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 by 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 must select 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)
 
Thank you for your reply.

I put the code you suggested behind one of my objects, and
received an error which threw me out and closed Excel. I
then created a new project, named the boxes the same as
you have in your example, and used the code, and again I
received an error and was thrown out of Excel.

Do you have any suggestions? Did you have this code in a
file, and could you please send me the file? That way I
could study it to see what I did wrong.

Thank you in advance
God bless you
 
Andy,

I think there was a very small bit of code missing from my previous reply.
Here is an amended version , based upon 3 textboxes, TextBox1, 2 and 3.

Private Sub TextBox1_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 must select cell before activating another
control.
If Application.Version < 9 Then Sheet1.Range("A1").Select
''' Activate the appropriate control based on key(s) pressed.
If bBackwards Then
TextBox3.Activate
Else
TextBox2.Activate
End If
Application.ScreenUpdating = True
End Select
End Sub

Private Sub TextBox2_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 must select cell before activating another
control.
If Application.Version < 9 Then Sheet1.Range("A1").Select
''' Activate the appropriate control based on key(s) pressed.
If bBackwards Then
TextBox1.Activate
Else
TextBox3.Activate
End If
Application.ScreenUpdating = True
End Select
End Sub

Private Sub TextBox3_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 must select cell before activating another
control.
If Application.Version < 9 Then Sheet1.Range("A1").Select
''' Activate the appropriate control based on key(s) pressed.
If bBackwards Then
TextBox2.Activate
Else
TextBox1.Activate
End If
Application.ScreenUpdating = True
End Select
End Sub

If you still have problems, I will mail you my example.

Regards

Bob
 
Bob,

I thank God for you being willing to share your
expertise! I pasted the code into a new sheet and all
worked as I wanted. I will now go back and apply to my
workbook. Thank you

God bless you
andy
 
Andy,

It's my pleasure. Sorry for the original error, lesson ... always test.

Regards

Bob
 
Back
Top