Forum Discussion

Dalhart7177's avatar
Dalhart7177
Copper Contributor
Mar 08, 2022

Attendance Spreadsheet using a Macro

Hello Everyone,

I am not new to Excel, but only use the basic functions of the spreadsheet, it is probably one Office product where I have the least experience.  I am trying to update our student attendance spreadsheet so that when the cell in the corresponding date column when clicked or changed will show the time in that particular cell.  Column A has all of the student names and columns B to the end of school year (June 15) will have a default value of absent in each cell.  I was able to get it to function using the formula which I will try and past into this, but wondering how I could have it work for each column without having to manually enter it each day.  Thanks,  Del Dobbs

 

Macros and VBA

4 Replies

  • Dino50's avatar
    Dino50
    Copper Contributor
    Mar 09, 2022

    Dalhart7177 

    A suggestion

    Create a named range (let's call it 'Calendar'), including all columns ad rows of your list.

    Then use a simple DoubleClick event like this

     

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

    Application.EnableEnvents = False

    If Not Intersect(Range("Calendar"), Target) Is Nothing Then
    Target.Value = Time
    End If

    Application.EnableEvents = True

    Cancel = True

    End Sub

     

    You have no need of Application.EnableEvents = True and Application.EnableEvents = False if you have no Private Sub Worksheet_Change(ByVal Target As Range) routine.

    • Dalhart7177's avatar
      Dalhart7177
      Copper Contributor
      Mar 08, 2022

      HansVogelaar 

      I have multiple people coming in at the same time so that is more time consuming than doing a click and selecting or a double click to change.  Thanks for the suggestion

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Mar 09, 2022

        Dalhart7177 Simply selecting a cell (whether by clicking in it or by using the keyboard) is dangerous - it is far too easy to do that by accident.

        Double-clicking would be an option - it is a more deliberate act.

        Remove the code in the worksheet module (if it's still there), then paste the following code into it:

        Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column > 1 And Target.Row > 1 Then
        Cancel = True
        Target.Value = Time
    End If
End Sub

Resources