Worksheet controls

[VegasNath]

Is it possible to lock a worksheet but maintain the ability to only: a. tab through worksheet controls & b. arrow up & down to select from combo / list boxes?

Or would data validation lists be better suited?

[HansV]

If you protect a sheet, ActiveX controls remain active and enabled, but (as always) you can't tab through them.

[VegasNath]

So you can tab through ActiveX controls on a userform but not a worksheet? Is there any solution (excluding userform) where tab and arrow keys can be used to move between and select from ........?

[HansV]

You'd have to create an On KeyDown event procedure for each of the ActiveX controls to trap the Tab key and to activate the "next" or "previous" control. It's not very attractive - see the thread Tab between ActiveX controls.

[VegasNath]

Hans,
Thanks for the pointer... I have attempted to adapt to my needs...

Code: Select all

Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Listbox3"
Const ctlNext As String = "ListBox2"

Select Case KeyCode
Case vbKeyTab
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1)
'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 ListBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Listbox1"
Const ctlNext As String = "ListBox3"

Select Case KeyCode
Case vbKeyTab
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1)
'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 ListBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Listbox2"
Const ctlNext As String = "ListBox1"

Select Case KeyCode
Case vbKeyTab
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1)
'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

The code all steps through as expected without error, however the selected control does not move. Any idea what I have done wrong please?

[HansV]

It doesn't work for me either. I think you should forget about this. Either live with the fact that the user can't tab between ActiveX controls, or do without them.

BTW, in a protected sheet, pressing tab in a cell takes the user to the next unlocked cell.

[VegasNath]

It doesn't work for me either. I think you should forget about this. Either live with the fact that the user can't tab between ActiveX controls, or do without them.

BTW, in a protected sheet, pressing tab in a cell takes the user to the next unlocked cell.

I tend to agree, but this is painful. I just want to be able to tab between [Cells or Forms Controls or ActiveX Controls] and use the arrow keys for selections. It seems that my only option is a userform?

[HansV]

Users can tab between cells. And they can press Alt+down arrow in a cell with a data validation to make the list drop down, then use the up and down arrow keys to select an item, and press Enter to confirm the selection.