| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get worksheet change code to work on protected worksheet?
As an aside, I'm getting so much better at modifying code that I get
from the ng archives now. Long time coming since I'm just not a natural at all this stuff <g! I was able to modify the code below and it works great except when I protect the worksheet. -------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler If Target.Column = 3 Then ' COLUMN 3=COLUMN C Application.EnableEvents = False If Not IsEmpty(Target) Then Cells(Target.Row, 2).Value = Now ' COLUMN THAT HAS DATE ADDED TO IT ' Cells(Target.Row, 1).NumberFormat = 'yyyy.mm.dd.ddd., hh"h"mm' Else Cells(Target.Row, 2).ClearContents ' COLUMN THAT HAS DATE ADDED TO IT, make sure it's the same as above. End If End If ErrHandler: Application.EnableEvents = True End Sub -------------------------------- Is there any way to get have it work without leaving the sheet unprotected? Thx! :oD |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to get worksheet change code to work on protected worksheet?
All the event code will work except for any attempt to change locked cells
on a protected sheet. Simply unprotect, change cells, and reprotect. ' if need to write to cells then Me.Unprotect "abc" ' do stuff Me.Protect "abc" ' record a macro for any non default arg's ' end if Regards, Peter T "StargateFan" wrote in message ... As an aside, I'm getting so much better at modifying code that I get from the ng archives now. Long time coming since I'm just not a natural at all this stuff <g! I was able to modify the code below and it works great except when I protect the worksheet. -------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler If Target.Column = 3 Then ' COLUMN 3=COLUMN C Application.EnableEvents = False If Not IsEmpty(Target) Then Cells(Target.Row, 2).Value = Now ' COLUMN THAT HAS DATE ADDED TO IT ' Cells(Target.Row, 1).NumberFormat = 'yyyy.mm.dd.ddd., hh"h"mm' Else Cells(Target.Row, 2).ClearContents ' COLUMN THAT HAS DATE ADDED TO IT, make sure it's the same as above. End If End If ErrHandler: Application.EnableEvents = True End Sub -------------------------------- Is there any way to get have it work without leaving the sheet unprotected? Thx! :oD |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to get worksheet change code to work on protected worksheet?
Hi,
To make the changes then fleetingly unprotect and then reprotect Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler If Target.Column = 3 Then ' COLUMN 3=COLUMN C Application.EnableEvents = False If Not IsEmpty(Target) Then ActiveSheet.Unprotect Password:="Mypass" Cells(Target.Row, 2).Value = Now ' COLUMN THAT HAS DATE ADDEDTO IT ' Cells(Target.Row, 1).NumberFormat = 'yyyy.mm.dd.ddd., hh"h"mm' Else Cells(Target.Row, 2).ClearContents ' COLUMN THAT HAS DATE ADDED TO IT, make sure it's the same as above. End If ActiveSheet.Protect Password:="Mypass" End If ErrHandler: Application.EnableEvents = True End Sub Mike "StargateFan" wrote: As an aside, I'm getting so much better at modifying code that I get from the ng archives now. Long time coming since I'm just not a natural at all this stuff <g! I was able to modify the code below and it works great except when I protect the worksheet. -------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler If Target.Column = 3 Then ' COLUMN 3=COLUMN C Application.EnableEvents = False If Not IsEmpty(Target) Then Cells(Target.Row, 2).Value = Now ' COLUMN THAT HAS DATE ADDED TO IT ' Cells(Target.Row, 1).NumberFormat = 'yyyy.mm.dd.ddd., hh"h"mm' Else Cells(Target.Row, 2).ClearContents ' COLUMN THAT HAS DATE ADDED TO IT, make sure it's the same as above. End If End If ErrHandler: Application.EnableEvents = True End Sub -------------------------------- Is there any way to get have it work without leaving the sheet unprotected? Thx! :oD |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to get worksheet change code to work on protected worksheet?
On Mon, 16 Feb 2009 06:10:24 -0800, Mike H
wrote: Hi, To make the changes then fleetingly unprotect and then reprotect Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler If Target.Column = 3 Then ' COLUMN 3=COLUMN C Application.EnableEvents = False If Not IsEmpty(Target) Then ActiveSheet.Unprotect Password:="Mypass" Cells(Target.Row, 2).Value = Now ' COLUMN THAT HAS DATE ADDEDTO IT ' Cells(Target.Row, 1).NumberFormat = 'yyyy.mm.dd.ddd., hh"h"mm' Else Cells(Target.Row, 2).ClearContents ' COLUMN THAT HAS DATE ADDED TO IT, make sure it's the same as above. End If ActiveSheet.Protect Password:="Mypass" End If ErrHandler: Application.EnableEvents = True End Sub Mike Wow, Mike, that's awesome! I don't know why I'm always so amazed by Excel, it excels at what it does ... <g I left it without a password in this instance so that every time you go to enter something or use one of the toolbar buttons, you don't have to enter a password. So I just modified the above slightly to this: -------------------------------- '--------------------------------------------------------------------------- ' This code here does allow cell entry without leaving sheet unprotected. '--------------------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler If Target.Column = 3 Then ' COLUMN 3=COLUMN C Application.EnableEvents = False If Not IsEmpty(Target) Then 'ActiveSheet.Unprotect Password:="Put a password here if you need one ..." ActiveSheet.Unprotect ' unprotects sheet to make entry Cells(Target.Row, 2).Value = Now ' COLUMN THAT HAS DATE ADDEDTO IT ' Cells(Target.Row, 1).NumberFormat = 'yyyy.mm.dd.ddd., hh"h"mm' Else Cells(Target.Row, 2).ClearContents ' COLUMN THAT HAS DATE ADDED TO IT, make sure it's the same as above. End If ' ActiveSheet.Protect Password:="Put a password here if you need one ...." ActiveSheet.Protect ' re-protects sheet after entry End If ErrHandler: Application.EnableEvents = True End Sub -------------------------------- Thanks! :oD "StargateFan" wrote: As an aside, I'm getting so much better at modifying code that I get from the ng archives now. Long time coming since I'm just not a natural at all this stuff <g! I was able to modify the code below and it works great except when I protect the worksheet. -------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler If Target.Column = 3 Then ' COLUMN 3=COLUMN C Application.EnableEvents = False If Not IsEmpty(Target) Then Cells(Target.Row, 2).Value = Now ' COLUMN THAT HAS DATE ADDED TO IT ' Cells(Target.Row, 1).NumberFormat = 'yyyy.mm.dd.ddd., hh"h"mm' Else Cells(Target.Row, 2).ClearContents ' COLUMN THAT HAS DATE ADDED TO IT, make sure it's the same as above. End If End If ErrHandler: Application.EnableEvents = True End Sub -------------------------------- Is there any way to get have it work without leaving the sheet unprotected? Thx! :oD |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to get worksheet change code to work on protected worksheet?
On Mon, 16 Feb 2009 09:22:26 -0500, StargateFan
wrote: On Mon, 16 Feb 2009 06:10:24 -0800, Mike H wrote: Hi, To make the changes then fleetingly unprotect and then reprotect Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler If Target.Column = 3 Then ' COLUMN 3=COLUMN C Application.EnableEvents = False If Not IsEmpty(Target) Then ActiveSheet.Unprotect Password:="Mypass" Cells(Target.Row, 2).Value = Now ' COLUMN THAT HAS DATE ADDEDTO IT ' Cells(Target.Row, 1).NumberFormat = 'yyyy.mm.dd.ddd., hh"h"mm' Else Cells(Target.Row, 2).ClearContents ' COLUMN THAT HAS DATE ADDED TO IT, make sure it's the same as above. End If ActiveSheet.Protect Password:="Mypass" End If ErrHandler: Application.EnableEvents = True End Sub Mike Wow, Mike, that's awesome! I don't know why I'm always so amazed by Excel, it excels at what it does ... <g I left it without a password in this instance so that every time you go to enter something or use one of the toolbar buttons, you don't have to enter a password. So I just modified the above slightly to this: -------------------------------- '--------------------------------------------------------------------------- ' This code here does allow cell entry without leaving sheet unprotected. '--------------------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler If Target.Column = 3 Then ' COLUMN 3=COLUMN C Application.EnableEvents = False If Not IsEmpty(Target) Then 'ActiveSheet.Unprotect Password:="Put a password here if you need one ..." ActiveSheet.Unprotect ' unprotects sheet to make entry Cells(Target.Row, 2).Value = Now ' COLUMN THAT HAS DATE ADDEDTO IT ' Cells(Target.Row, 1).NumberFormat = 'yyyy.mm.dd.ddd., hh"h"mm' Else Cells(Target.Row, 2).ClearContents ' COLUMN THAT HAS DATE ADDED TO IT, make sure it's the same as above. End If ' ActiveSheet.Protect Password:="Put a password here if you need one ..." ActiveSheet.Protect ' re-protects sheet after entry End If ErrHandler: Application.EnableEvents = True End Sub -------------------------------- Thanks! :oD [snip] Darn <sigh. I spoke too soon ... Yes, the above allows code to be entered, but it takes away half the functionality of the code below. I have to unprotect the sheet before entering anything with the code below, but if I delete anything, the affected cells get their contents wiped which the above doesn't do. Not that it's going to happen often but it would be nice to have the functionality of the code below re keeping the cells cleared with the convenience of not having to unprotect/protect manually. Is it possible, by any chance? Thanks once again. :oD ================================================== ===== [snip] Original: -------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler If Target.Column = 3 Then ' COLUMN 3=COLUMN C Application.EnableEvents = False If Not IsEmpty(Target) Then Cells(Target.Row, 2).Value = Now ' COLUMN THAT HAS DATE ADDED TO IT ' Cells(Target.Row, 1).NumberFormat = 'yyyy.mm.dd.ddd., hh"h"mm' Else Cells(Target.Row, 2).ClearContents ' COLUMN THAT HAS DATE ADDED TO IT, make sure it's the same as above. End If End If ErrHandler: Application.EnableEvents = True End Sub -------------------------------- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need a work around for a protected worksheet | Setting up and Configuration of Excel | |||
| AutoFilter can't work after the worksheet is being protected!!! | Excel Programming | |||
| How to make TAB key work in a protected worksheet | Excel Programming | |||
| Can't Get Objects To Work In A Protected Worksheet | Excel Worksheet Functions | |||
| Why doesn't spell check work on a protected worksheet? | Excel Worksheet Functions | |||
Linear Mode
