Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error on password protected sheet.
hi- I am working on a sheet that has the following code. I get an error
(Run-time error '1004': Unable to set the ColorIndex property of the Interior class) and pointing at the line marked ***(cell.Interior.ColorIndex = icolor1)( I added the *** is not is the code). The workbook also has a code in the module called "auto_open" that is supposed to allow users to use outlining on the protected sheet. the coloring works fine if I remove the auto_open sub but I need to pwd protect and allow the user to operate on the created outlining. Any help will be appreciated. --------In worksheet I have--------- Private Sub Worksheet_Calculate() Dim icolor1 As Long Dim icolor2 As Long Dim cell As Range For Each cell In Range("F12:M400") icolor1 = xlColorIndexNone icolor2 = xlColorIndexAutomatic Select Case cell.Value Case "Red": icolor1 = 3: icolor2 = 3 Case "Green": icolor1 = 4: icolor2 = 4 Case "Blue": icolor1 = 5: icolor2 = 5 Case "White": icolor1 = 2: icolor2 = 2 Case "Gray": icolor1 = 15: icolor2 = 15 Case "x": icolor1 = 1: icolor2 = 1 Case "xx": icolor1 = 40: icolor2 = 40 Case "yy": icolor1 = 36: icolor2 = 36 Case "Not Assessed": icolor1 = 2: icolor2 = 40 Case "Missing Info.": icolor1 = 2: icolor2 = 3 Case Else: 'Whatever End Select *** cell.Interior.ColorIndex = icolor1 cell.Font.ColorIndex = icolor2 Next cell End Sub ---------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Dim R As Range If Not Intersect(Target, Range("F12:M400")) Is Nothing Then Application.EnableEvents = False For Each R In Target.Cells Select Case R.Text Case "Red": icolor = 3 Case "Green": icolor = 4 Case "Blue": icolor = 5 Case "White": icolor = 2 Case "Gray": icolor = 15 Case "x": icolor = 1 Case "xx": icolor = 40 Case "yy": icolor = 36 Case "Not Assessed": icolor = 2 Case Else: 'Whatever End Select R.Interior.ColorIndex = icolor R.Font.ColorIndex = icolor Next R End If EndProc: Application.EnableEvents = True End Sub ------------- In the module3 I have-------------------- Sub auto_open() With Worksheets("Passive Safety") .Protect Password:="password", userinterfaceonly:=True .EnableOutlining = True End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error on password protected sheet.
You will need to unprotect the sheet, run your code, and then protect the
sheet again. If you have a password enter it in betweent he "" next to Password. Option Explicit Private Sub Worksheet_Calculate() Dim icolor1 As Long Dim icolor2 As Long Dim cell As Range ActiveSheet.Unprotect Password:="" For Each cell In Range("F12:M400") icolor1 = xlColorIndexNone icolor2 = xlColorIndexAutomatic Select Case cell.Value Case "Red": icolor1 = 3: icolor2 = 3 Case "Green": icolor1 = 4: icolor2 = 4 Case "Blue": icolor1 = 5: icolor2 = 5 Case "White": icolor1 = 2: icolor2 = 2 Case "Gray": icolor1 = 15: icolor2 = 15 Case "x": icolor1 = 1: icolor2 = 1 Case "xx": icolor1 = 40: icolor2 = 40 Case "yy": icolor1 = 36: icolor2 = 36 Case "Not Assessed": icolor1 = 2: icolor2 = 40 Case "Missing Info.": icolor1 = 2: icolor2 = 3 Case Else: 'Whatever End Select cell.Interior.ColorIndex = icolor1 cell.Font.ColorIndex = icolor2 Next cell ActiveSheet.Protect Password:="" End Sub Hope this helps! -- Cheers, Ryan "Ram B" wrote: hi- I am working on a sheet that has the following code. I get an error (Run-time error '1004': Unable to set the ColorIndex property of the Interior class) and pointing at the line marked ***(cell.Interior.ColorIndex = icolor1)( I added the *** is not is the code). The workbook also has a code in the module called "auto_open" that is supposed to allow users to use outlining on the protected sheet. the coloring works fine if I remove the auto_open sub but I need to pwd protect and allow the user to operate on the created outlining. Any help will be appreciated. --------In worksheet I have--------- Private Sub Worksheet_Calculate() Dim icolor1 As Long Dim icolor2 As Long Dim cell As Range For Each cell In Range("F12:M400") icolor1 = xlColorIndexNone icolor2 = xlColorIndexAutomatic Select Case cell.Value Case "Red": icolor1 = 3: icolor2 = 3 Case "Green": icolor1 = 4: icolor2 = 4 Case "Blue": icolor1 = 5: icolor2 = 5 Case "White": icolor1 = 2: icolor2 = 2 Case "Gray": icolor1 = 15: icolor2 = 15 Case "x": icolor1 = 1: icolor2 = 1 Case "xx": icolor1 = 40: icolor2 = 40 Case "yy": icolor1 = 36: icolor2 = 36 Case "Not Assessed": icolor1 = 2: icolor2 = 40 Case "Missing Info.": icolor1 = 2: icolor2 = 3 Case Else: 'Whatever End Select *** cell.Interior.ColorIndex = icolor1 cell.Font.ColorIndex = icolor2 Next cell End Sub ---------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Dim R As Range If Not Intersect(Target, Range("F12:M400")) Is Nothing Then Application.EnableEvents = False For Each R In Target.Cells Select Case R.Text Case "Red": icolor = 3 Case "Green": icolor = 4 Case "Blue": icolor = 5 Case "White": icolor = 2 Case "Gray": icolor = 15 Case "x": icolor = 1 Case "xx": icolor = 40 Case "yy": icolor = 36 Case "Not Assessed": icolor = 2 Case Else: 'Whatever End Select R.Interior.ColorIndex = icolor R.Font.ColorIndex = icolor Next R End If EndProc: Application.EnableEvents = True End Sub ------------- In the module3 I have-------------------- Sub auto_open() With Worksheets("Passive Safety") .Protect Password:="password", userinterfaceonly:=True .EnableOutlining = True End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error on password protected sheet.
"RyanH" wrote: You will need to unprotect the sheet, run your code, and then protect the sheet again. If you have a password enter it in betweent he "" next to Password. Option Explicit Private Sub Worksheet_Calculate() Dim icolor1 As Long Dim icolor2 As Long Dim cell As Range ActiveSheet.Unprotect Password:="" For Each cell In Range("F12:M400") icolor1 = xlColorIndexNone icolor2 = xlColorIndexAutomatic Select Case cell.Value Case "Red": icolor1 = 3: icolor2 = 3 Case "Green": icolor1 = 4: icolor2 = 4 Case "Blue": icolor1 = 5: icolor2 = 5 Case "White": icolor1 = 2: icolor2 = 2 Case "Gray": icolor1 = 15: icolor2 = 15 Case "x": icolor1 = 1: icolor2 = 1 Case "xx": icolor1 = 40: icolor2 = 40 Case "yy": icolor1 = 36: icolor2 = 36 Case "Not Assessed": icolor1 = 2: icolor2 = 40 Case "Missing Info.": icolor1 = 2: icolor2 = 3 Case Else: 'Whatever End Select cell.Interior.ColorIndex = icolor1 cell.Font.ColorIndex = icolor2 Next cell ActiveSheet.Protect Password:="" End Sub Hope this helps! -- Cheers, Ryan "Ram B" wrote: hi- I am working on a sheet that has the following code. I get an error (Run-time error '1004': Unable to set the ColorIndex property of the Interior class) and pointing at the line marked ***(cell.Interior.ColorIndex = icolor1)( I added the *** is not is the code). The workbook also has a code in the module called "auto_open" that is supposed to allow users to use outlining on the protected sheet. the coloring works fine if I remove the auto_open sub but I need to pwd protect and allow the user to operate on the created outlining. Any help will be appreciated. --------In worksheet I have--------- Private Sub Worksheet_Calculate() Dim icolor1 As Long Dim icolor2 As Long Dim cell As Range For Each cell In Range("F12:M400") icolor1 = xlColorIndexNone icolor2 = xlColorIndexAutomatic Select Case cell.Value Case "Red": icolor1 = 3: icolor2 = 3 Case "Green": icolor1 = 4: icolor2 = 4 Case "Blue": icolor1 = 5: icolor2 = 5 Case "White": icolor1 = 2: icolor2 = 2 Case "Gray": icolor1 = 15: icolor2 = 15 Case "x": icolor1 = 1: icolor2 = 1 Case "xx": icolor1 = 40: icolor2 = 40 Case "yy": icolor1 = 36: icolor2 = 36 Case "Not Assessed": icolor1 = 2: icolor2 = 40 Case "Missing Info.": icolor1 = 2: icolor2 = 3 Case Else: 'Whatever End Select *** cell.Interior.ColorIndex = icolor1 cell.Font.ColorIndex = icolor2 Next cell End Sub ---------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Dim R As Range If Not Intersect(Target, Range("F12:M400")) Is Nothing Then Application.EnableEvents = False For Each R In Target.Cells Select Case R.Text Case "Red": icolor = 3 Case "Green": icolor = 4 Case "Blue": icolor = 5 Case "White": icolor = 2 Case "Gray": icolor = 15 Case "x": icolor = 1 Case "xx": icolor = 40 Case "yy": icolor = 36 Case "Not Assessed": icolor = 2 Case Else: 'Whatever End Select R.Interior.ColorIndex = icolor R.Font.ColorIndex = icolor Next R End If EndProc: Application.EnableEvents = True End Sub ------------- In the module3 I have-------------------- Sub auto_open() With Worksheets("Passive Safety") .Protect Password:="password", userinterfaceonly:=True .EnableOutlining = True End With End Sub Hello Ram B and RyanH, open car used userinterfaceonly: = True then there is no need to unprotect .... I tried the code and I have not found errors. In Worksheet_Change is not necessary to disable the events, format changes do not trigger the event change. The only problem I can imagine is that in routine Private Sub Worksheet_Calculate () ... Range ( "F12: M400") refers to an active sheet that is not Worksheets ( "Passive Safety") ... then try to replace Range ( "F12: M400) with me.Range (" F12: M400) goodbye r |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
no password for protected sheet | Excel Worksheet Functions | |||
PLEASE HELP! Copy sheet with password protected cells debug error | Excel Programming | |||
Importing from Password Protected Sheet | Excel Worksheet Functions | |||
password protected Sheet | Excel Discussion (Misc queries) | |||
VBa, Password protected sheet fails to get unprotected with the same password | Excel Programming |