Help locking a column to prevent editing.

[Snafu]

Hi,

I am new to using VBA in Excel and trying to crash course myself into it the same way I did when first learning JavaScript many years ago. Unfortunately unlike JavaScript there seems to be a lacking of websites online with great advice for how to complete my specific task, so I ask you to help. I would like to describe my task in full to help you understand my problem.

My task
I am trying to create an excel document with 11 tabs. On each tab is a series of cells that must be 'ticked' or crossed off to indicate the task has been checked. (This can be done by any input so in this case I have chosen a font and not a tick box system or macro as it would take too long)

Of these 11 tabs, 8 have identical rows, each row (right to left) is used to display over time the checks completed. The first column ('A', the column with the task names) does not change in any situation and must not be allowed to be changed while every other cell on the document must be editable.

On top of this task there should be a drop down box with names of personnel who carry out these tasks. A way to timestamp a cell would be needed so when the person enters/selects their name from the drop down box it inputs the time in which this was completed. (timestamp doesn't need to be a locked cell)

Things I have already completed:
- Getting a name dropdown cell for personnel using Data Validation list drop down.
- Timestamp function to work in unison with the drop down list.

I need help on the following for this task:
- A way to lock column 'A' that doesn't throw up an error. Currently if I lock the sheet and make all but column 'A' unlocked (via cell formatting) it provides me with an error. Or a way of coding the sheet so this doesn't cause an issue.
- A set of code for the future if the timestamp row needs to be locked from editing once first input to avoid tampering. (this currently isn't required but it may be in the future)

My code I currently use:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim rChange As Range    On Error GoTo ErrHandler
    Set rChange = Intersect(Target, Range("B2:ZZ2"))
    If Not rChange Is Nothing Then
        Application.EnableEvents = False
        For Each rCell In rChange
            If rCell > "" Then
                With rCell.Offset(13, 0)
                    .Value = Now
                    .NumberFormat = "dd/MM/yyyy - hh:mm:ss"
                End With
            Else
                rCell.Offset(13, 0).Clear
            End If
        Next
    End If
ExitHandler:
    Set rCell = Nothing
    Set rChange = Nothing
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

Thank you all for your time reading this and helping.
-
Snafu

[Kenneth Hobs]

Welcome to the forum!

What I like to do is to add this to ThisWorkbook object's Open event.

Sheet1.Protect "ken", UserInterfaceOnly:=True

The key is UserInterface=True to allow code to change locked cell values but not the user. Change ken to suit, sheet codename or tab name (WorkSheets("Sheet1"), and add other protect options.

I would use a range offest to poke a date/time value into a locked cell. I would make the sheet visible and then xlveryhidden.

Don't forget to protect VBAProject at the end of your project.

[Snafu]

Thank you for responding. I'm not quite sure I understand your answer though.

I have put in your code and it works exactly as required when used in conjunction with normal cell formatting protection, thank you.

I would like you to explain the visible / xlveryhidden combination. I read up before posting this about what xlveryhidden is and what it does but I'm still not fully grasping it's use here. Maybe it's sleep deprivation (12 hour shifts).

[Kenneth Hobs]

It is about the visible property set for each sheet. If those are not hidden, then you need not worry. Some hide sheets with passwords or data they don't want users to even see or change.

e.g. One sheet is left visible while all others are hidden when the file closes. When opened, some sheets are unhidden if the allowed user opens it or a password is returned properly. This makes sure that the file has macros enabled.