Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro stops working when sheet is protected
Hi
I hope that this question isnt a duplicate. I tried searching all over with no success so I thought that I would try posting. Basically, I have created a spreadsheet template that will be used to track purchases. I found a macro that automatically enters the date into columns A and H whenever data is entered into B and I respectively. The code is in General Declarations: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then With Target If .Value < "" Then ..Offset(0, -1).Value = Format(Date, "dd/mm/yyyy") End If End With End If If Not Intersect(Target, Me.Range("I:I")) Is Nothing Then With Target If .Value < "" Then ..Offset(0, -1).Value = Format(Date, "dd/mm/yyyy") End If End With End If ws_exit: Application.EnableEvents = True End Sub I would now like to lock and protect columns A and H (as well as a few other select cells). Whenever I try to protect the sheet no matter what has been locked, the date stops being automatically entered. An error message does not ever pop up and it never asks if I would like to debug it. The date just does not appear. I do not necessarily have to use a macro. I have just found that this particular macro works the best. In the past I have gotten errors and or the date would automatically update every time I opened the file. I just want some way to track the date I ordered something and the date the order was received. Does anyone know a way I can keep the macro and just protect the cells? If that isnt possible is there a good function that I could use in the stead of the code? Id like to be able to protect the cells because I wont be the only one using it. Thanks for any help or guidance you can offer. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro stops working when sheet is protected
Unprotect and reprotect the sheet...
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: me.unprotect Application.EnableEvents = False If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then With Target If .Value < "" Then ..Offset(0, -1).Value = Format(Date, "dd/mm/yyyy") End If End With End If If Not Intersect(Target, Me.Range("I:I")) Is Nothing Then With Target If .Value < "" Then ..Offset(0, -1).Value = Format(Date, "dd/mm/yyyy") End If End With End If ws_exit: me.protect Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "RefLib1978" wrote: Hi I hope that this question isnt a duplicate. I tried searching all over with no success so I thought that I would try posting. Basically, I have created a spreadsheet template that will be used to track purchases. I found a macro that automatically enters the date into columns A and H whenever data is entered into B and I respectively. The code is in General Declarations: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then With Target If .Value < "" Then .Offset(0, -1).Value = Format(Date, "dd/mm/yyyy") End If End With End If If Not Intersect(Target, Me.Range("I:I")) Is Nothing Then With Target If .Value < "" Then .Offset(0, -1).Value = Format(Date, "dd/mm/yyyy") End If End With End If ws_exit: Application.EnableEvents = True End Sub I would now like to lock and protect columns A and H (as well as a few other select cells). Whenever I try to protect the sheet no matter what has been locked, the date stops being automatically entered. An error message does not ever pop up and it never asks if I would like to debug it. The date just does not appear. I do not necessarily have to use a macro. I have just found that this particular macro works the best. In the past I have gotten errors and or the date would automatically update every time I opened the file. I just want some way to track the date I ordered something and the date the order was received. Does anyone know a way I can keep the macro and just protect the cells? If that isnt possible is there a good function that I could use in the stead of the code? Id like to be able to protect the cells because I wont be the only one using it. Thanks for any help or guidance you can offer. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro stops working when sheet is protected
Thank you! That worked perfectly. Not to regress too much into my
adolescence, but you totally rule! "Jim Thomlinson" wrote: Unprotect and reprotect the sheet... Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: me.unprotect Application.EnableEvents = False If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then With Target If .Value < "" Then .Offset(0, -1).Value = Format(Date, "dd/mm/yyyy") End If End With End If If Not Intersect(Target, Me.Range("I:I")) Is Nothing Then With Target If .Value < "" Then .Offset(0, -1).Value = Format(Date, "dd/mm/yyyy") End If End With End If ws_exit: me.protect Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "RefLib1978" wrote: Hi I hope that this question isnt a duplicate. I tried searching all over with no success so I thought that I would try posting. Basically, I have created a spreadsheet template that will be used to track purchases. I found a macro that automatically enters the date into columns A and H whenever data is entered into B and I respectively. The code is in General Declarations: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then With Target If .Value < "" Then .Offset(0, -1).Value = Format(Date, "dd/mm/yyyy") End If End With End If If Not Intersect(Target, Me.Range("I:I")) Is Nothing Then With Target If .Value < "" Then .Offset(0, -1).Value = Format(Date, "dd/mm/yyyy") End If End With End If ws_exit: Application.EnableEvents = True End Sub I would now like to lock and protect columns A and H (as well as a few other select cells). Whenever I try to protect the sheet no matter what has been locked, the date stops being automatically entered. An error message does not ever pop up and it never asks if I would like to debug it. The date just does not appear. I do not necessarily have to use a macro. I have just found that this particular macro works the best. In the past I have gotten errors and or the date would automatically update every time I opened the file. I just want some way to track the date I ordered something and the date the order was received. Does anyone know a way I can keep the macro and just protect the cells? If that isnt possible is there a good function that I could use in the stead of the code? Id like to be able to protect the cells because I wont be the only one using it. Thanks for any help or guidance you can offer. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro stops working when sheet is protected
Actually my wife rules, but when she is not around I pretend like I do... ;-)
-- HTH... Jim Thomlinson "RefLib1978" wrote: Thank you! That worked perfectly. Not to regress too much into my adolescence, but you totally rule! "Jim Thomlinson" wrote: Unprotect and reprotect the sheet... Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: me.unprotect Application.EnableEvents = False If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then With Target If .Value < "" Then .Offset(0, -1).Value = Format(Date, "dd/mm/yyyy") End If End With End If If Not Intersect(Target, Me.Range("I:I")) Is Nothing Then With Target If .Value < "" Then .Offset(0, -1).Value = Format(Date, "dd/mm/yyyy") End If End With End If ws_exit: me.protect Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "RefLib1978" wrote: Hi I hope that this question isnt a duplicate. I tried searching all over with no success so I thought that I would try posting. Basically, I have created a spreadsheet template that will be used to track purchases. I found a macro that automatically enters the date into columns A and H whenever data is entered into B and I respectively. The code is in General Declarations: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then With Target If .Value < "" Then .Offset(0, -1).Value = Format(Date, "dd/mm/yyyy") End If End With End If If Not Intersect(Target, Me.Range("I:I")) Is Nothing Then With Target If .Value < "" Then .Offset(0, -1).Value = Format(Date, "dd/mm/yyyy") End If End With End If ws_exit: Application.EnableEvents = True End Sub I would now like to lock and protect columns A and H (as well as a few other select cells). Whenever I try to protect the sheet no matter what has been locked, the date stops being automatically entered. An error message does not ever pop up and it never asks if I would like to debug it. The date just does not appear. I do not necessarily have to use a macro. I have just found that this particular macro works the best. In the past I have gotten errors and or the date would automatically update every time I opened the file. I just want some way to track the date I ordered something and the date the order was received. Does anyone know a way I can keep the macro and just protect the cells? If that isnt possible is there a good function that I could use in the stead of the code? Id like to be able to protect the cells because I wont be the only one using it. Thanks for any help or guidance you can offer. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto filter sort ascending not working while sheet protected | Excel Worksheet Functions | |||
UDF is not working on a protected sheet in Excel 2003 | Excel Discussion (Misc queries) | |||
Filters not working on Protected sheet | Excel Discussion (Misc queries) | |||
Hyperlinks not working after sheet is protected.... | Excel Discussion (Misc queries) | |||
Find Function not working on protected sheet | Excel Worksheet Functions |