Argthtjtr

I have an excel spreadsheet set up as a timeclock . The employee opens their timeclock file for the week and fills in the week ending date in the designated spot. From here on out, only the mouse is used for input. The employee selects the proper cell for the day of the week and the action of punching in, going to lunch, back from lunch and going home. After they position the cursor over the correct cell, they click on a button (a button linked to a macro) that fills in the correct time for the action. I can't figure out how to keep them from just going to the cell they want and typing in whatever time they want. I tried adding protecting the cell with a password as part of the button macro but haven't been able to get that to work. Anybody have a way to protect the cells so the employee can't alter the times? Or, is there just an easier way to do this all together?

Your users have access to write to the Excel file...

I don't think you can technically enforce this within Excel; I think you want an alternative software. I admire your skill with Excel macros though.

To elaborate:

• If the user can modify the cell, then it isn't protected from the user simply modifying the cell on their own (typing in a value).


• If the user cannot modify the cell, then it is protected from the user just typing in a value, but they also cannot cause the correct information to be written to it from a macro.

This is distinct from encrypting cells, which prevents even reading without the password.

I think there is a lot of time-card software out there, and most of it can probably export collected data into Excel. I would expand my horizons beyond spreadsheets if I were in your position.

You may disallow changes to cells by a VBA event handler:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    With Target

        If If Not Intersect(Target, Range("A1:B10")) Is Nothing Then Application.Undo

    End With

    Application.EnableEvents = True

End Sub

I don't know if setting the value of cells via a VBA macro causes this event
to fire. I know that it doesn't fire for changes induced by formulas.
But you may easily define a global variable for allowing changes to be set
before changing the cell and unset it afterward.

You might also try to trap the Worksheet_SelectionChange event, to prevent the
user from positioning the cursor inside the cell. You may for the disallowed
cells re-position the cursor to some predefined cell (such as A1),
or write some more complicated code to remember the last allowed cursor position
and reposition to it.