ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Tab between ActiveX controls (https://www.excelbanter.com/excel-programming/415605-tab-between-activex-controls.html)

Tekhnikos
Tab between ActiveX controls
 
I have several ActiveX textbox controls on a worksheet. How do I allow a
user to use the tab key to move from one text box to the next?

--
Tekhnikos

Bob Phillips[_3_]
Tab between ActiveX controls
 
Option Explicit

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Textbox3"
Const ctlNext As String = "TextBox2"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
'In Excel 97 must select cell before activating another control
If Application.Version < 9 Then ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If fBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub


Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Textbox1"
Const ctlNext As String = "TextBox3"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
'In Excel 97 must select cell before activating another control
If Application.Version < 9 Then ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If fBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub

Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Textbox2"
Const ctlNext As String = "TextBox1"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
'In Excel 97 must select cell before activating another control
If Application.Version < 9 Then ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If fBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub




--
__________________________________
HTH

Bob

"Tekhnikos" wrote in message
...
I have several ActiveX textbox controls on a worksheet. How do I allow a
user to use the tab key to move from one text box to the next?

--
Tekhnikos



Tekhnikos
Tab between ActiveX controls
 
Thanks for the help, I was hoping there was a simple way to do this without
writing code. I appreciate the code.
--
Tekhnikos


"Bob Phillips" wrote:

Option Explicit

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Textbox3"
Const ctlNext As String = "TextBox2"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
'In Excel 97 must select cell before activating another control
If Application.Version < 9 Then ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If fBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub


Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Textbox1"
Const ctlNext As String = "TextBox3"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
'In Excel 97 must select cell before activating another control
If Application.Version < 9 Then ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If fBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub

Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Textbox2"
Const ctlNext As String = "TextBox1"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
'In Excel 97 must select cell before activating another control
If Application.Version < 9 Then ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If fBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub




--
__________________________________
HTH

Bob

"Tekhnikos" wrote in message
...
I have several ActiveX textbox controls on a worksheet. How do I allow a
user to use the tab key to move from one text box to the next?

--
Tekhnikos




Bob Phillips[_3_]
Tab between ActiveX controls
 
I am afraid it isn't possible without code, so this is as good as it gets
<g

--
__________________________________
HTH

Bob

"Tekhnikos" wrote in message
...
Thanks for the help, I was hoping there was a simple way to do this
without
writing code. I appreciate the code.
--
Tekhnikos


"Bob Phillips" wrote:

Option Explicit

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Textbox3"
Const ctlNext As String = "TextBox2"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
'In Excel 97 must select cell before activating another
control
If Application.Version < 9 Then
ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If fBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub


Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Textbox1"
Const ctlNext As String = "TextBox3"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
'In Excel 97 must select cell before activating another
control
If Application.Version < 9 Then
ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If fBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub

Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Textbox2"
Const ctlNext As String = "TextBox1"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
'In Excel 97 must select cell before activating another
control
If Application.Version < 9 Then
ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If fBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub




--
__________________________________
HTH

Bob

"Tekhnikos" wrote in message
...
I have several ActiveX textbox controls on a worksheet. How do I allow
a
user to use the tab key to move from one text box to the next?

--
Tekhnikos

Tekhnikos
Tab between ActiveX controls
 
I used your code and it has been working fine. Now some of the users are
getting Compile errors-Can't find project or library. The error is happening
at the following line:
ActiveSheet.OLEObjects(ctlPrev).Activate

Ideas would be greatly appreciated. It is very odd as it is not happening
to all users. I checked their References, it appears the correct References
are checked.
--
Tekhnikos


"Bob Phillips" wrote:

I am afraid it isn't possible without code, so this is as good as it gets
<g

--
__________________________________
HTH

Bob

"Tekhnikos" wrote in message
...
Thanks for the help, I was hoping there was a simple way to do this
without
writing code. I appreciate the code.
--
Tekhnikos


"Bob Phillips" wrote:

Option Explicit

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Textbox3"
Const ctlNext As String = "TextBox2"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
'In Excel 97 must select cell before activating another
control
If Application.Version < 9 Then
ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If fBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub


Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Textbox1"
Const ctlNext As String = "TextBox3"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
'In Excel 97 must select cell before activating another
control
If Application.Version < 9 Then
ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If fBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub

Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Textbox2"
Const ctlNext As String = "TextBox1"

Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
'In Excel 97 must select cell before activating another
control
If Application.Version < 9 Then
ActiveSheet.Range("A1").Select
'Activate the appropriate control based on key(s) pressed
If fBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub




--
__________________________________
HTH

Bob

"Tekhnikos" wrote in message
...
I have several ActiveX textbox controls on a worksheet. How do I allow
a
user to use the tab key to move from one text box to the next?

--
Tekhnikos


All times are GMT +1. The time now is 12:15 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com