How does the program detect the user's control

accessman2 · #1 (**permalink**) 09-08-06, 14:01

Hi,

In VBA,
Can we detect whether or not the user click "Tab" key, or Arrow-keys, or mouse pointer to move/select the cell?

Thanks.

MikeTheBike · #2 (**permalink**) 09-11-06, 08:03

Hi accessman2

I am not sure what you mean by "Tab" key, but I have assumed it is the sheet tabs ?

To detect worksheet Tab Click (and Ctl+PageUp/Down) you can use the following events in the 'ThisWorkbook' module

Code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    MsgBox "Tab Clicked (Sheet changed TO " & Sh.Name & ")"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "Sheet changed  (" & Sh.Name & ")  Cell " & Target.Address
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    MsgBox "Tab Clicked (Sheet changed FROM " & Sh.Name & ")"
End Sub

and to detect moving around a sheet (Curser keys, Tab key, move click) you can use the following events in the individual sheet module(s). ALso sheet changes

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "Changed"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "Moved"
End Sub

Does that help ?

MTB

Internet Services

Web Development

Digital Marketing

Consumer Tech