Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting a Range in a list
Whats wrong with this code? I am using it on a sheet that has an Excel
list. I cant protect the worksheet as it makes the list inactive. The code gives the user a message and applys an undo whenever they attempt to type over a formula. I'm trying to allow the users to delete whole rows and using this code "If Target.Columns.Count = Columns Then". If I take the delete rows bit out everything works fine, with it in it works okay but slows the spreadsheet down massivley making it unworkable. Here is the code: Dim myRng As Range Set myRng = Me.Range("Protect") On Error GoTo errHandler: If Intersect(Target, myRng) Is Nothing Then 'let 'em do it Else With Application .EnableEvents = False .Undo End With MsgBox "I've asked you not to change this Field!" End If On Error GoTo errHandler: If Target.Columns.Count = Columns Then Else With Application .EnableEvents = False .Undo End With MsgBox "I've asked you not to change this Field!" End If errHandler: Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting a Range in a list
I believe the code Nick mentioned was
If Target.Columns.Count = Columns.Count Are you letting the user delete rows in the "Protected" area? Mike F "John" wrote in message oups.com... Whats wrong with this code? I am using it on a sheet that has an Excel list. I cant protect the worksheet as it makes the list inactive. The code gives the user a message and applys an undo whenever they attempt to type over a formula. I'm trying to allow the users to delete whole rows and using this code "If Target.Columns.Count = Columns Then". If I take the delete rows bit out everything works fine, with it in it works okay but slows the spreadsheet down massivley making it unworkable. Here is the code: Dim myRng As Range Set myRng = Me.Range("Protect") On Error GoTo errHandler: If Intersect(Target, myRng) Is Nothing Then 'let 'em do it Else With Application .EnableEvents = False .Undo End With MsgBox "I've asked you not to change this Field!" End If On Error GoTo errHandler: If Target.Columns.Count = Columns Then Else With Application .EnableEvents = False .Undo End With MsgBox "I've asked you not to change this Field!" End If errHandler: Application.EnableEvents = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting a Range in a list
Maybe replace this line
If Target.Columns.Count = Columns Then with If Target.Columns.Count = me.Columns.count Then And maybe setting up a flag would be an easier way to see what's happening. I think... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Dim OkToContinue As Boolean Set myRng = Me.Range("Protect") On Error GoTo errHandler: OkToContinue = False If Intersect(Target, myRng) Is Nothing Then OkToContinue = True End If If Target.Columns.Count = Me.Columns.Count Then OkToContinue = True End If If OkToContinue = True Then 'let 'em do it Else With Application .EnableEvents = False .Undo End With MsgBox "I've asked you not to change this Field!" End If errHandler: Application.EnableEvents = True End Sub John wrote: Whats wrong with this code? I am using it on a sheet that has an Excel list. I cant protect the worksheet as it makes the list inactive. The code gives the user a message and applys an undo whenever they attempt to type over a formula. I'm trying to allow the users to delete whole rows and using this code "If Target.Columns.Count = Columns Then". If I take the delete rows bit out everything works fine, with it in it works okay but slows the spreadsheet down massivley making it unworkable. Here is the code: Dim myRng As Range Set myRng = Me.Range("Protect") On Error GoTo errHandler: If Intersect(Target, myRng) Is Nothing Then 'let 'em do it Else With Application .EnableEvents = False .Undo End With MsgBox "I've asked you not to change this Field!" End If On Error GoTo errHandler: If Target.Columns.Count = Columns Then Else With Application .EnableEvents = False .Undo End With MsgBox "I've asked you not to change this Field!" End If errHandler: Application.EnableEvents = True End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting a Range in a list
This will protect the list from individual cell changes, but will allow a
list member to be deleted only if the entire row is selected. It will also allow multiple rows to be deleted at one time, so it is possible to delete the entire list if all their rows are selected. Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Set myRng = Me.Range("Protect") Application.EnableEvents = False On Error GoTo errHandler: If Intersect(Target, myRng) Is Nothing Then 'let 'em do it ElseIf Target.Columns.Count = Columns.Count Then 'let 'em do it Else Application.Undo MsgBox "I've asked you not to change this Field!" End If On Error GoTo errHandler: errHandler: Application.EnableEvents = True End Sub Mike F "Mike Fogleman" wrote in message ... I believe the code Nick mentioned was If Target.Columns.Count = Columns.Count Are you letting the user delete rows in the "Protected" area? Mike F "John" wrote in message oups.com... Whats wrong with this code? I am using it on a sheet that has an Excel list. I cant protect the worksheet as it makes the list inactive. The code gives the user a message and applys an undo whenever they attempt to type over a formula. I'm trying to allow the users to delete whole rows and using this code "If Target.Columns.Count = Columns Then". If I take the delete rows bit out everything works fine, with it in it works okay but slows the spreadsheet down massivley making it unworkable. Here is the code: Dim myRng As Range Set myRng = Me.Range("Protect") On Error GoTo errHandler: If Intersect(Target, myRng) Is Nothing Then 'let 'em do it Else With Application .EnableEvents = False .Undo End With MsgBox "I've asked you not to change this Field!" End If On Error GoTo errHandler: If Target.Columns.Count = Columns Then Else With Application .EnableEvents = False .Undo End With MsgBox "I've asked you not to change this Field!" End If errHandler: Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List with total row and protecting | Excel Worksheet Functions | |||
Protecting a sheet with a list on it | Excel Discussion (Misc queries) | |||
Protecting an Excel List | Excel Programming | |||
Protecting a Used Range | Excel Programming | |||
Protecting a worksheet containing XML List | Excel Discussion (Misc queries) |