How to Disable Mouse Click only for specific Excel worksheet through VBA?

Question

I'm working on a project where the requirement is not to let the users use mouse to move from one cell to another, they can only move through the Tab button. So, I have no idea how can I disable the mouse click on particular excel sheet and allow the users to use only the Tab key.

Thanks in Advance!

@jeeped, `Application.Interactive = False` disables also tab navigation, so it doesnt seem to fulfill the OP's requirement — A.S.H, Oct 06 '15 at 11:24
@A.S.H - yes, my own playing around confirmed that. Reversed. — , Oct 06 '15 at 11:26
See here. http://stackoverflow.com/questions/17111648/how-to-lock-excel-cells-in-vba — MatthewD, Oct 06 '15 at 12:27

A.S.H · Answer 1 · 2015-10-06T12:45:20.130

In this answer I suppose that your goal is not to lock the cells from editing, but only to disable selecting the cells using the mouse. That is, user is still allowed to edit a cell, but she needs to first select it using Tab or Arrow buttons, and then she can edit it by pressing F2 or typing.

Add the following code to your Worksheet's code module:

Option Explicit
Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
' The keyword PtrSafe is needed only if you are working on a Win64 platform
' Remove it if you are using Win32

Private lastSelection As Range
Private Sub Worksheet_Activate()
   On Error Resume Next
   Set lastSelection = Selection.Cells(1)
   If Err.Number <> 0 Then Set lastSelection = Cells(1, 1)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim e As Variant
    For Each e In Array(vbKeyUp, vbKeyDown, vbKeyLeft, vbKeyRight, vbKeyTab, vbKeyReturn, vbKeyHome, vbKeyEnd, vbKeyPageDown, vbKeyPageUp)
        If CBool(GetAsyncKeyState(e) And &H8000) Then 'You got here without using the mouse
            Set lastSelection = Target
            ' do other stuff if needed
            Exit Sub
        End If
    Next
   ' Selection was changed via mouse. Rollback
   If lastSelection Is Nothing Then Set lastSelection = Cells(1, 1)
   lastSelection.Activate
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As  Boolean)
    Cancel = True
End Sub

paul bica · Answer 2 · 2015-10-06T15:33:25.803

A couple of options

.

1. Without protecting the sheet - forces all clicks back to cell A1 (in sheet's module)

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    With Target
        If .Column <> 1 Or .Row <> 1 Or .CountLarge > 1 Then Application.Undo
    End With
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    With Target
        If .Column <> 1 Or .Row <> 1 Or .CountLarge > 1 Then Cells(1, 1).Select
    End With
    Application.EnableEvents = True
End Sub

2. Protecting the sheet - doesn't allow cell navigation

Private Sub Worksheet_Activate()
    Me.Protect
    Me.EnableSelection = xlNoSelection
End Sub

How to Disable Mouse Click only for specific Excel worksheet through VBA?

2 Answers2