Tab to next control box

[Guest]

I've added a few control boxes to a spreadsheet and created some related
macros and links to data, without going through VB and first creating a user
form. I can't seem to find a way to allow the user to tab from one control
box to the next as data is to be entered. Can this setup be done without
having first created a user form? Can a user form be created after the
setting up control boxes?

 

[Héctor Miguel]

hi, Tiffany !

I've added a few control boxes to a spreadsheet and created some related macros and links to data ...
I can't seem to find a way to allow the user to tab from one control box to the next as data is to be entered ...

assuming embedded controls are: 'TextBox1', 'TextBox2' y 'TextBox3'
following lines will activete from active textbox to next -> using {tab} <-
[or... if user press {shift} + {tab} will activate previous]
[if there is NO next or previous... focus goes back to worksheet active selection]

if any doubt [or further information]... would you please comment ?
regards,
hector.

=== in 'that' worksheet code module ===
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then SendKeys "{esc}"
If KeyCode = vbKeyTab Then
If Shift = 0 Then OLEObjects("textbox2").Activate Else SendKeys "{esc}"
End If
End Sub
Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then SendKeys "{esc}"
If KeyCode = vbKeyTab Then
If Shift = 0 Then OLEObjects("textbox3").Activate Else OLEObjects("textbox1").Activate
End If
End Sub
Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then SendKeys "{esc}"
If KeyCode = vbKeyTab Then
If Shift = 1 Then OLEObjects("textbox2").Activate Else SendKeys "{esc}"
End If
End Sub

 

[Guest]

Works well, Hector, thanks so much. In general, is it more beneficial/worth
the time to create user forms in Visual Basic?


--
TASR

hi, Tiffany !

I've added a few control boxes to a spreadsheet and created some related macros and links to data ...
I can't seem to find a way to allow the user to tab from one control box to the next as data is to be entered ...

assuming embedded controls are: 'TextBox1', 'TextBox2' y 'TextBox3'
following lines will activete from active textbox to next -> using {tab} <-
[or... if user press {shift} + {tab} will activate previous]
[if there is NO next or previous... focus goes back to worksheet active selection]

if any doubt [or further information]... would you please comment ?
regards,
hector.

=== in 'that' worksheet code module ===
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then SendKeys "{esc}"
If KeyCode = vbKeyTab Then
If Shift = 0 Then OLEObjects("textbox2").Activate Else SendKeys "{esc}"
End If
End Sub
Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then SendKeys "{esc}"
If KeyCode = vbKeyTab Then
If Shift = 0 Then OLEObjects("textbox3").Activate Else OLEObjects("textbox1").Activate
End If
End Sub
Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then SendKeys "{esc}"
If KeyCode = vbKeyTab Then
If Shift = 1 Then OLEObjects("textbox2").Activate Else SendKeys "{esc}"
End If
End Sub

 

[Héctor Miguel]

hi, Tiffany !

Works well, Hector, thanks so much.
In general, is it more beneficial/worth the time to create user forms in Visual Basic?

glad it worked for you

AFAIK, there is no a general *better way* to do something (but)...
if you need to use/embed several controls in worksheets...
it is generally recommended to move them and use/design userforms
(you will preserve your workbooks integrity as far as you can)

regards,
hector.

__ previous __

hi, Tiffany !

I've added a few control boxes to a spreadsheet and created some related macros and links to data ...
I can't seem to find a way to allow the user to tab from one control box to the next as data is to be entered ...

assuming embedded controls are: 'TextBox1', 'TextBox2' y 'TextBox3'
following lines will activete from active textbox to next -> using {tab} <-
[or... if user press {shift} + {tab} will activate previous]
[if there is NO next or previous... focus goes back to worksheet active selection]

if any doubt [or further information]... would you please comment ?
regards,
hector.

=== in 'that' worksheet code module ===
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then SendKeys "{esc}"
If KeyCode = vbKeyTab Then
If Shift = 0 Then OLEObjects("textbox2").Activate Else SendKeys "{esc}"
End If
End Sub
Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then SendKeys "{esc}"
If KeyCode = vbKeyTab Then
If Shift = 0 Then OLEObjects("textbox3").Activate Else OLEObjects("textbox1").Activate
End If
End Sub
Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then SendKeys "{esc}"
If KeyCode = vbKeyTab Then
If Shift = 1 Then OLEObjects("textbox2").Activate Else SendKeys "{esc}"
End If
End Sub