Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visually check Protection code
I wish to check that a complicated (for me) protection routine is
coded and operating correctly. Addin code allows a User to select their workbook(s) and then continues to open their selection. As it does so, code ends with .Protect .EnableSelection = xlUnlockedCells Can I say something like: Sub Test_Protection() Dim ws As Worksheet, c As Range For Each ws In ActiveWorkbook.Worksheets With ws For Each c In .UsedRange.Columns("A:G") If Not c.Locked = True Then 'User is permitted to edit this cell, so color it: c.Interior.ColorIndex = 34 End If Next End With Next End Sub This doesn't work for me. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visually check Protection code
if you unprotect the worksheet it should work.
-- Regards, Tom Ogilvy "Stuart" wrote in message ... I wish to check that a complicated (for me) protection routine is coded and operating correctly. Addin code allows a User to select their workbook(s) and then continues to open their selection. As it does so, code ends with .Protect .EnableSelection = xlUnlockedCells Can I say something like: Sub Test_Protection() Dim ws As Worksheet, c As Range For Each ws In ActiveWorkbook.Worksheets With ws For Each c In .UsedRange.Columns("A:G") If Not c.Locked = True Then 'User is permitted to edit this cell, so color it: c.Interior.ColorIndex = 34 End If Next End With Next End Sub This doesn't work for me. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visually check Protection code
Thanks, but I had tried that. It unprotected the sheet
but my code failed to change the cell's colour. Setting up cell "B45" and running this amended code seems to work: Sub Test_Protection2() Dim ws As Worksheet, c As Range For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect ' For Each c In .UsedRange.Columns("A:G") ' If Not c.Locked = True Then ' 'User is permitted to edit this cell, so color it: ' c.Interior.ColorIndex = 34 ' End If ' Next If Not .Range("B45").Locked = True Then .Range("B45").Interior.ColorIndex = 34 End If End With Next End Sub Now why might this work, but not the first version, please? Regards. "Tom Ogilvy" wrote in message ... if you unprotect the worksheet it should work. -- Regards, Tom Ogilvy "Stuart" wrote in message ... I wish to check that a complicated (for me) protection routine is coded and operating correctly. Addin code allows a User to select their workbook(s) and then continues to open their selection. As it does so, code ends with .Protect .EnableSelection = xlUnlockedCells Can I say something like: Sub Test_Protection() Dim ws As Worksheet, c As Range For Each ws In ActiveWorkbook.Worksheets With ws For Each c In .UsedRange.Columns("A:G") If Not c.Locked = True Then 'User is permitted to edit this cell, so color it: c.Interior.ColorIndex = 34 End If Next End With Next End Sub This doesn't work for me. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visually check Protection code
Apologies, because I think the error lies in the original
"protection" routine. I had been trying to avoid selecting each worksheet before applying the "protection" code. Looks as though avoiding 'select' results in the code failing to protect the sheet. Regards. "Tom Ogilvy" wrote in message ... if you unprotect the worksheet it should work. -- Regards, Tom Ogilvy "Stuart" wrote in message ... I wish to check that a complicated (for me) protection routine is coded and operating correctly. Addin code allows a User to select their workbook(s) and then continues to open their selection. As it does so, code ends with .Protect .EnableSelection = xlUnlockedCells Can I say something like: Sub Test_Protection() Dim ws As Worksheet, c As Range For Each ws In ActiveWorkbook.Worksheets With ws For Each c In .UsedRange.Columns("A:G") If Not c.Locked = True Then 'User is permitted to edit this cell, so color it: c.Interior.ColorIndex = 34 End If Next End With Next End Sub This doesn't work for me. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protection and Spell check | Excel Worksheet Functions | |||
Spell check and protection | Excel Discussion (Misc queries) | |||
Protection of check boxes in excel 2002 | Excel Discussion (Misc queries) | |||
Spell Check w/ Protection | Excel Discussion (Misc queries) | |||
VBA code delete code but ask for password and unlock VBA protection | Excel Programming |