Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not running in Protect Sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not running in Protect Sheet
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 "Akash" skrev i en meddelelse ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to allow auto filter after running password protect | Excel Discussion (Misc queries) | |||
Macro not running in Protect Sheet | Excel Programming | |||
Protect sheet in macro | Excel Worksheet Functions | |||
Password Protect Running of Macro | Excel Programming | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming |