Protect a column ins workbook with macros

[sindhuja]

Hi,

Any assistance on my requirement pls…

Have a spreadsheet to with date time stamp macro to record the timings.
But need is lock the column once this is done.

For example I have a column (column 2) with the validation MET / MISS. Upon selecting a value in column 2 date time stamp will be captured in next column (column 3).

Now I need to lock the column 3 (should not allow the date time stamp to edit).

Since am using below macro for date time stamp am unable to protect the sheet.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("B2:A65000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With

With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("D2:J65000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With

With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("F2:J65000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With

End Sub


Help me out pls..

-Sindhuja

[lucas]

Lock the cells and protect the sheet?

[sindhuja]

Hi Lucas,

Thanks for the assistance..

I have tried the option you have mentioned. If I protect the sheet the macro is not working..

That is the problem and also I have to lock whole column…

- Sindhuja

[lucas]

When your macro runs, have it unprotect the sheet.....run the code...reprotect the sheet.

[sindhuja]

Used macros for the date time stamp.. So on the worksheet change event i used the coding…

am not sure how to unprotect and reprotect the sheet using the coding…
Used the commands

Activesheet.unprotect at the start and
Activesheet.protect at the end…

Even then its not working out for me...

[lucas]

This seems to work. Open it and type something in the highlighted cell and then try to change the date in the c column.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Me.Unprotect
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("B2:A65000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With

With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("D2:J65000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With

With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("F2:J65000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
Me.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True
End Sub

[sindhuja]

Sorry if am bothering you again and again.. its allowing to enter only once in a sheet... its showing error message if imake another try...

i have attached my original spreadsheet for the reference...

[lucas]

That's ok, I hope this helps you understand how things work. It only worked once on your sheet because you had the rest of column B locked when protected.

I selected the entire sheet and unlocked all cells(right click-format cells-clear the check under the protection tab under lock or unlock.

I then selected just column C and did the same except I locked them. then protect the sheet and run your items in column B, etc.

[sindhuja]

Hi Lucas...

Now i understood and it worked out for me...

thanks a lot....

-Sindhuja