Need to protect columns from user edits but not VBA edits

Viewed 32

Based on choices from a drop-down list in Column G the user makes, columns I and J are filled with data - user name and time/date respectively. We need to protect columns I and J from user edits, but allow their contents to be updated based on updates of column G (the user makes a different choice from the drop-down).

Example Image

I don't have the protect code yet. I don't know how to do that. I do have the code I'm using to update the cell values based on the user's choice.

    Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUserNameRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRange = Range("G2:G306")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running
Application.EnableEvents = False

'Column for the date/time
Set myUserNameRange = Range("I" & Target.Row)
Set myDateTimeRange = Range("J" & Target.Row)

'Column for last updated date/time
Set myUpdatedRange = Range("J" & Target.Row)

'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then

    myDateTimeRange.Value = Now

End If
If myUserNameRange.Value = "" Then
    myUserNameRange.Value = Application.UserName

End If

'Update the updated date/time value
myUpdatedRange.Value = Now
myUserNameRange.Value = Environ("Username")
'myUserNameRange.Value = Application.UserName

'Turn events back on
Application.EnableEvents = True
End Sub

As I said before. I need to enter protect code that will allow the above VBA code to change the contents of columns I and J should the user decide to make a different choice from the drop-down in column G. It should just keep the user from changing the contents of the values in columns I and J.

0 Answers
Related