Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting around Protected Worksheet (number format)
Hi..
I have this command which put a timestamp on a cell every time you write something in another cell.. I then use a NumberFormat command to put the timestamp in H:MM.. But when i protect my worksheet (this Worsheet Needs to be protected), it gives an error.. I am trying to get a way around it.. My first idea is to have an other worksheet that would =sheet1! .. This worsheet (2) wouldnt be protected, so the number format macro would work.. My problem is that since the worsheet (2) is not the active, it does not recalculate the timestamp function.. Here is the Command: Private Sub Worksheet_Activate() Call Recalculate End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("P13").Address Then Range("H13").Value = Now End If If Target.Address = "$H$13" Then Target.NumberFormat = "h:mm" End If End Sub I am sure that there are different ways to solve this problem..Anybody would know a way around this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting around Protected Worksheet (number format)
Not sure I totally follow, if the sheet is protected how does the "P13" cell
end up being changed. Perhaps in the event with code you could unprotect, write the new value, then re-protect Or maybe leave your cell(s) unlocked. or write to the other sheet If Target.Address = Range("P13").Address Then worksheets("Sheet2").Range("H13").Value = Now End If or use the CodeName if "Sheet2" might get renamed Sheet2.Range("H13").Value = Now Regards, Peter T "Al 305" wrote in message ... Hi.. I have this command which put a timestamp on a cell every time you write something in another cell.. I then use a NumberFormat command to put the timestamp in H:MM.. But when i protect my worksheet (this Worsheet Needs to be protected), it gives an error.. I am trying to get a way around it.. My first idea is to have an other worksheet that would =sheet1! .. This worsheet (2) wouldnt be protected, so the number format macro would work.. My problem is that since the worsheet (2) is not the active, it does not recalculate the timestamp function.. Here is the Command: Private Sub Worksheet_Activate() Call Recalculate End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("P13").Address Then Range("H13").Value = Now End If If Target.Address = "$H$13" Then Target.NumberFormat = "h:mm" End If End Sub I am sure that there are different ways to solve this problem..Anybody would know a way around this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting around Protected Worksheet (number format)
Hi Peter..
Sorry about the info.. Both P13 cells and H13 cells were unlocked, but i was still getting the error.. but with you code, i m able to divert the result in sheet2 (which i wont protect and try to Hide), and then, from Sheet1 (H13), refer to the info in sheet 2... At least it works... Thks.. Unless you have a code that would say something like: (in worksheet1) if there is an input in P13 then Unlock Worsheet 1 Put Now() in H13 NumberFormat H13: H:MM Lock Worksheet 1 What do you think? On Mar 10, 10:32 am, "Peter T" <peter_t@discussions wrote: Not sure I totally follow, if the sheet is protected how does the "P13" cell end up being changed. Perhaps in the event with code you could unprotect, write the new value, then re-protect Or maybe leave your cell(s) unlocked. or write to the other sheet If Target.Address = Range("P13").Address Then worksheets("Sheet2").Range("H13").Value = Now End If or use the CodeName if "Sheet2" might get renamed Sheet2.Range("H13").Value = Now Regards, Peter T "Al 305" wrote in message ... Hi.. I have this command which put a timestamp on a cell every time you write something in another cell.. I then use a NumberFormat command to put the timestamp in H:MM.. But when i protect my worksheet (this Worsheet Needs to be protected), it gives an error.. I am trying to get a way around it.. My first idea is to have an other worksheet that would =sheet1! .. This worsheet (2) wouldnt be protected, so the number format macro would work.. My problem is that since the worsheet (2) is not the active, it does not recalculate the timestamp function.. Here is the Command: Private Sub Worksheet_Activate() Call Recalculate End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("P13").Address Then Range("H13").Value = Now End If If Target.Address = "$H$13" Then Target.NumberFormat = "h:mm" End If End Sub I am sure that there are different ways to solve this problem..Anybody would know a way around this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting around Protected Worksheet (number format)
Sorry about the info.. Both P13 cells and H13 cells were unlocked,
but i was still getting the error.. Are you sure, if H13 is unlocked sheet protection shouldn't be a problem. Here's some basic unprotect/re-protect code, depending on your version you may want to include additional protect options. Private Sub Worksheet_Change(ByVal Target As Range) Const PWRD As String = "abc" On Error GoTo errExit If Target.Address = Range("P13").Address Then Application.EnableEvents = False Me.Unprotect PWRD With Range("H13") .Value = Now .NumberFormat = "h:mm" End With Me.Protect Password:=PWRD, _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True End If errExit: Application.EnableEvents = True '' uncomment for testing ' If Err.Number Then ' MsgBox Err.Description ' Stop ' Resume ' End If End Sub Regards, Peter T "Al 305" wrote in message ... Hi Peter.. Sorry about the info.. Both P13 cells and H13 cells were unlocked, but i was still getting the error.. but with you code, i m able to divert the result in sheet2 (which i wont protect and try to Hide), and then, from Sheet1 (H13), refer to the info in sheet 2... At least it works... Thks.. Unless you have a code that would say something like: (in worksheet1) if there is an input in P13 then Unlock Worsheet 1 Put Now() in H13 NumberFormat H13: H:MM Lock Worksheet 1 What do you think? On Mar 10, 10:32 am, "Peter T" <peter_t@discussions wrote: Not sure I totally follow, if the sheet is protected how does the "P13" cell end up being changed. Perhaps in the event with code you could unprotect, write the new value, then re-protect Or maybe leave your cell(s) unlocked. or write to the other sheet If Target.Address = Range("P13").Address Then worksheets("Sheet2").Range("H13").Value = Now End If or use the CodeName if "Sheet2" might get renamed Sheet2.Range("H13").Value = Now Regards, Peter T "Al 305" wrote in message ... Hi.. I have this command which put a timestamp on a cell every time you write something in another cell.. I then use a NumberFormat command to put the timestamp in H:MM.. But when i protect my worksheet (this Worsheet Needs to be protected), it gives an error.. I am trying to get a way around it.. My first idea is to have an other worksheet that would =sheet1! .. This worsheet (2) wouldnt be protected, so the number format macro would work.. My problem is that since the worsheet (2) is not the active, it does not recalculate the timestamp function.. Here is the Command: Private Sub Worksheet_Activate() Call Recalculate End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("P13").Address Then Range("H13").Value = Now End If If Target.Address = "$H$13" Then Target.NumberFormat = "h:mm" End If End Sub I am sure that there are different ways to solve this problem..Anybody would know a way around this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit text format in non-protected cells in protected worksheet | Excel Discussion (Misc queries) | |||
how do i format inserted cells in protected worksheet? | Excel Worksheet Functions | |||
Setting Cell Number Format With A Worksheet Function | Excel Worksheet Functions | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions |