Announcement

Collapse
No announcement yet.

Special time based Vba request ..

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Special time based Vba request ..



    Hey all,

    I would like to know if it would be possible to find out the time difference between last Sheet Selection Change and the current Sheet Selection Change.

    for instance I am at any given cell , in this case A4 and then after 5 seconds I decided to go to cell A5

    Could a code output me value of 5 seconds?

    Or from here on I decided to go from cell A5 to A6 in 1 second, could the code output me value of 1?

    Thank you and will appreciate.

  • #2


    Hi,

    This is relatively simple to achieve by using the Timer function which returns a Single representing the number of seconds elapsed since midnight.

    See the following code which should be inserted into your worksheet module...


    Code:
    Option Explicit
    
    Private sngTimer As Single
    Private strLastCellAddress As String
    
    Private Sub Worksheet_Activate()
    
    sngTimer = 0
    
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If sngTimer = 0 Then
       ' No previous cell clicked.
       Debug.Print "Initial click cell " & Target.Address & " Time Difference = 0 seconds"
       
       strLastCellAddress = Target.Address
       sngTimer = Timer
    Else
       Debug.Print "Time Difference between clicking cell " & strLastCellAddress & " cell " & Target.Address & " =" & _
                   Str(Timer - sngTimer) & " seconds"
       
       strLastCellAddress = Target.Address
       sngTimer = Timer
    End If

    See the attached workbook 'cell_clicks_time_difference.xlsm' for the working code.

    Regards,

    Tom Rowe...
    Attached Files

    Comment

    Working...
    X