ExcelBanter - How to get worksheet change code to work on protected worksheet?

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - How to get worksheet change code to work on protected worksheet? (https://www.excelbanter.com/excel-programming/424181-how-get-worksheet-change-code-work-protected-worksheet.html)

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

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

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

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

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
--------------------------------