Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Error with Sheet Protection Enabled?
Hi All,
I am at a loss, I get an error in my sub when I try and run it with the sheet protection enabled. I've made sure the range of cells are not locked but it has no affect. I'm sure that it has something to do with the fact that it is Workbook_SheetSelectionChange type sub but I don't know how to rectify. Any assistance would be welcome. 'This sub changes only the active cell with in the range green and bolds the text in the cell beside the active green cell Public Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) If Intersect(Target, Range("D7:D9,F7:F9")) Is Nothing Then Exit Sub 'If the selected cell is not in the range exit the sub Static DataField As Range If Not DataField Is Nothing Then DataField.Interior.ColorIndex = xlNone End If 'changes the offset cell text to bold Target.Interior.ColorIndex = 4 With Target.Interior.ColorIndex = 4 ActiveCell.Offset(0, 1).Font.Bold = True ActiveCell.Offset(0, 1).Font.ColorIndex = 3 End With If Range("D7,F7").Interior.ColorIndex = xlNone Then Range("E7,G7").Font.Bold = False End If If Range("D8,F8").Interior.ColorIndex = xlNone Then Range("E8,G8").Font.Bold = False End If If Range("D9,F9").Interior.ColorIndex = xlNone Then Range("E9,G9").Font.Bold = False End If Set DataField = Target End Sub Thanks in advance. Pete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Error with Sheet Protection Enabled?
Public Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, ByVal Target As Excel.Range) If Intersect(Target, Range("D7:D9,F7:F9")) Is Nothing Then Exit Sub 'If the selected cell is not in the range exit the sub Static DataField As Range sh.unprotect password:="abcd" If Not DataField Is Nothing Then DataField.Interior.ColorIndex = xlNone End If 'changes the offset cell text to bold Target.Interior.ColorIndex = 4 With Target.Interior.ColorIndex = 4 ActiveCell.Offset(0, 1).Font.Bold = True ActiveCell.Offset(0, 1).Font.ColorIndex = 3 End With If Range("D7,F7").Interior.ColorIndex = xlNone Then Range("E7,G7").Font.Bold = False End If If Range("D8,F8").Interior.ColorIndex = xlNone Then Range("E8,G8").Font.Bold = False End If If Range("D9,F9").Interior.ColorIndex = xlNone Then Range("E9,G9").Font.Bold = False End If Set DataField = Target sh.protect password:="abcd" End Sub -- Regards, Tom Ogilvy pcsis wrote in message news:cDuEb.111666$bC.102120@clgrps13... Hi All, I am at a loss, I get an error in my sub when I try and run it with the sheet protection enabled. I've made sure the range of cells are not locked but it has no affect. I'm sure that it has something to do with the fact that it is Workbook_SheetSelectionChange type sub but I don't know how to rectify. Any assistance would be welcome. 'This sub changes only the active cell with in the range green and bolds the text in the cell beside the active green cell Public Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) If Intersect(Target, Range("D7:D9,F7:F9")) Is Nothing Then Exit Sub 'If the selected cell is not in the range exit the sub Static DataField As Range If Not DataField Is Nothing Then DataField.Interior.ColorIndex = xlNone End If 'changes the offset cell text to bold Target.Interior.ColorIndex = 4 With Target.Interior.ColorIndex = 4 ActiveCell.Offset(0, 1).Font.Bold = True ActiveCell.Offset(0, 1).Font.ColorIndex = 3 End With If Range("D7,F7").Interior.ColorIndex = xlNone Then Range("E7,G7").Font.Bold = False End If If Range("D8,F8").Interior.ColorIndex = xlNone Then Range("E8,G8").Font.Bold = False End If If Range("D9,F9").Interior.ColorIndex = xlNone Then Range("E9,G9").Font.Bold = False End If Set DataField = Target End Sub Thanks in advance. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error when opening a macro-enabled Excel 2007 file | Excel Discussion (Misc queries) | |||
Excel Data Protection Best Practice: AKA: Real Sheet Protection | Excel Discussion (Misc queries) | |||
Cell/worksheet protection with macros enabled | Excel Worksheet Functions | |||
ON AN EXCEL SPREAD SHEET TOOLS/PROTECTION/ (4TABS are not enabled | Excel Discussion (Misc queries) | |||
import data greyed out when protection enabled | Excel Discussion (Misc queries) |