Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet protection
Several days ago I did a newsgroup query and got an excellent response to my coding problem that really works well. Now I want to protect the worksheet and when I do I get a run time error at line 9. Is there a line of code that I can insert to allow me to do this? Thank You Carl Private Sub Worksheet_Change(ByVal _ Target As Excel.Range) Dim rng as Range If Target.Count 1 Then Exit Sub If Target.row = 2 and Target.Row <=10 then If Target.column = 5 Then If Not IsEmpty(Target) Then If IsNumeric(Target) Then Application.EnableEvents = False set rng = Target.Offset(0,4) rng.Value = rng.value + Target.Value Target.ClearContents Application.EnableEvents = True End If End If End If End If End Sub *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet protection
Hello Rev,
You can unprotect the worksheet in VBA before you make modifications to the worksheet, then reprotect it after you are done with your modifications. Like the following: Worksheets("Sheet1").Unprotect 'Code to change the worksheet Worksheets("Sheet1").Protect You could do this around all of your code if you want, if you make several modifications in several places, or you could just put this around the code that actually modifies the worksheet. Look up the .protect and .unprotect methods in the VBA help for additional information. Hope this helps, Kris -----Original Message----- Several days ago I did a newsgroup query and got an excellent response to my coding problem that really works well. Now I want to protect the worksheet and when I do I get a run time error at line 9. Is there a line of code that I can insert to allow me to do this? Thank You Carl Private Sub Worksheet_Change(ByVal _ Target As Excel.Range) Dim rng as Range If Target.Count 1 Then Exit Sub If Target.row = 2 and Target.Row <=10 then If Target.column = 5 Then If Not IsEmpty(Target) Then If IsNumeric(Target) Then Application.EnableEvents = False set rng = Target.Offset(0,4) rng.Value = rng.value + Target.Value Target.ClearContents Application.EnableEvents = True End If End If End If End If End Sub *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Protection Best Practice: AKA: Real Sheet Protection | Excel Discussion (Misc queries) | |||
Excel Data Protection- AKA: Sheet/Macro Password Protection | Setting up and Configuration of Excel | |||
Sheet protection | Excel Discussion (Misc queries) | |||
Sheet protection error msg - Unrequested sheet activation | Excel Programming | |||
Sheet Protection | Excel Programming |