Excel - Macro not running in Protect Sheet

Asked By Akash

17-Jan-08 06:37 AM

Hi,

I have a macro, With the help of this macro, as soon as u write
anything in Column A it would automatically  throws a Current Date in
Column G:


Dim LastRow, LastCol, CurRow, CurCol As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
If LastCol = 1 Then
CurRow = ActiveCell.Row
CurCol = ActiveCell.Column
'MsgBox ActiveCell.Row
'MsgBox ActiveCell.Column

'MsgBox Target

If Sheet1.Cells(LastRow, 7).Value = "" Then
Sheet1.Cells(LastRow, 7).Value = Date
Cells(LastRow, 4).Select
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
LastRow = ActiveCell.Row
LastCol = ActiveCell.Column
'MsgBox ActiveCell.Row
'MsgBox ActiveCell.Column

End Sub


There are other formulas in the Excel Sheet. I had protected the sheet
to hide the formula. but as soon as i protected the sheet, my macro is
not working.


Can u help me how can i run the macro even after i protect the sheet.

Is there any way.

Thanks

Akash

16-Jan-08 08:15 AM

Hi Akash

You can not change any cell which is protected.

So you have to unprotect the cell before you can add the date.

Private Sub Worksheet_Change(ByVal Target As Range)
If LastCol = 1 Then
CurRow = ActiveCell.Row
CurCol = ActiveCell.Column
'MsgBox ActiveCell.Row
'MsgBox ActiveCell.Column

'MsgBox Target
With Sheets(Sheet1)

If .Cells(LastRow, 7).Value = "" Then
.Unprotect
.Cells(LastRow, 7).Value = Date
.Cells(LastRow, 4).Select
.Protect
End If
End With
End If
End Sub

Regards,

Per