VBA keyboard shortcuts

[Padawan]

Running Excel 2002, Windows XP Pro

My worksheet has some users employing Dragon voice recognition software. This allows them to use voice commands to envoke keyboard strokes.

I have several combo boxes on this sheet.

Is there a way to set accelerator keys (as on a command button) or tab stops or something to allow the user to access the combo boxes from the keyboard instead of using their mouse?

THANK YOU!

 

[Padawan]

No replys from yesterday.

Renewing the thread.

Can anyone help?

THANK YOU.

 

[erik.van.geit]

Hello,
jxderg77,

You can invoke macros with shortcuts
You can call userforms with macros
You can put comboboces on userforms

This is a workaround at least

Macro a()
Userform1.Show
End Sub

 

[parry]

Hi John, you can use the up and down arrows to scroll through a list. However, the control needs to be selected before this can occur and I dont believe theres a keyboard option to select a particular control. While you can tab between controls on a UserForm, there is no such option for a worksheet.

However, you can assign macros to run when a particular key combo is pressed. The following example macro cycles through all controls on Sheet1 when you press the F12 key. You will need to change the name of the sheet to the one you use - the rest of the code should not need to be altered unless you want the key combo changed.

***Place in ThisWorkbook Module***

Private Sub Workbook_Open()
'Monitor F12 key and run macro MoveNextControl
Application.OnKey "{F12}", "MoveNextControl"

'Create a dynamic array of controls
Call CreateArray
End Sub

***Place in a standard module***

Public CurCtrl As Integer 
Public TotalCtrl As Integer 
Public ArrCtrl() As OLEObject 

Sub MoveNextControl()

If CurCtrl < 1 Then
    CurCtrl = 1
ElseIf CurCtrl >= UBound(ArrCtrl) Then
    CurCtrl = 1
Else
    CurCtrl = CurCtrl + 1
End If

ArrCtrl(CurCtrl).Activate

End Sub

Sub CreateArray()
Dim Ctrl As OLEObject

'Change Sheet1 to the name of your sheet
For Each Ctrl In ThisWorkbook.Sheets("Sheet1").OLEObjects
    
        'resize the array
        On Error Resume Next
        ReDim Preserve ArrCtrl(1 To UBound(ArrCtrl) + 1)
        If Err.Number <> 0 Then
        ReDim ArrCtrl(1 To 1)
        End If
        On Error GoTo 0
        
        'Add control to the array
        Set ArrCtrl(UBound(ArrCtrl)) = Ctrl
  
Next Ctrl

TotalCtrl = UBound(ArrCtrl)

End Sub

PS: You will need to save the workbook, then close and re-open it for this code to work.

EDIT: Sorry, I didnt paste the public variables. Should work now.