Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotect not working
I'm in a loop opening each workbook in turn, and with each book
I state: For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect 'Find which type of BofQ we are dealing with, and in which 'column the '£' is found. For Each C In Range("A1:Z100") If C.Value = SEARCHFOR Then £Col = C.Column Exit For End If Next 'Get the page 'identifiers' for this sheet ReDim Pagevarr(1 To 1) Set rng = Columns(1).SpecialCells(xlConstants, xlTextValues) The code is failing on this last line, with the error message saying that I cannot use the Set statement on a protected worksheet. Why is .Unprotect not working here please? Have half an idea that selecting the sheet might solve this. If so, can I avoid the use of Select? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.679 / Virus Database: 441 - Release Date: 07/05/2004 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotect not working
Hi Stuart,
The code is failing on this last line, with the error message saying that I cannot use the Set statement on a protected worksheet. Why is .Unprotect not working here please? I suspect the .Unprotect is working fine. The problem is that you have used Columns(1) instead of .Columns(1) (note the dot in front of the Columns method). Therefore Columns(1) is referring to the currently active worksheet, not neccessarilly the same as the one the ws variable is referring to. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Stuart" wrote in message ... I'm in a loop opening each workbook in turn, and with each book I state: For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect 'Find which type of BofQ we are dealing with, and in which 'column the '£' is found. For Each C In Range("A1:Z100") If C.Value = SEARCHFOR Then £Col = C.Column Exit For End If Next 'Get the page 'identifiers' for this sheet ReDim Pagevarr(1 To 1) Set rng = Columns(1).SpecialCells(xlConstants, xlTextValues) Have half an idea that selecting the sheet might solve this. If so, can I avoid the use of Select? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.679 / Virus Database: 441 - Release Date: 07/05/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotect not working
Blast, missed it. I'd been altering a similar routine
written some time ago, where Select had been used. Should have fully qualified my references. Many thanks. Regards. "Rob Bovey" wrote in message ... Hi Stuart, The code is failing on this last line, with the error message saying that I cannot use the Set statement on a protected worksheet. Why is .Unprotect not working here please? I suspect the .Unprotect is working fine. The problem is that you have used Columns(1) instead of .Columns(1) (note the dot in front of the Columns method). Therefore Columns(1) is referring to the currently active worksheet, not neccessarilly the same as the one the ws variable is referring to. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Stuart" wrote in message ... I'm in a loop opening each workbook in turn, and with each book I state: For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect 'Find which type of BofQ we are dealing with, and in which 'column the '£' is found. For Each C In Range("A1:Z100") If C.Value = SEARCHFOR Then £Col = C.Column Exit For End If Next 'Get the page 'identifiers' for this sheet ReDim Pagevarr(1 To 1) Set rng = Columns(1).SpecialCells(xlConstants, xlTextValues) Have half an idea that selecting the sheet might solve this. If so, can I avoid the use of Select? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.679 / Virus Database: 441 - Release Date: 07/05/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.679 / Virus Database: 441 - Release Date: 07/05/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I unprotect a spreadsheet? | Excel Worksheet Functions | |||
unprotect a worksheet | Excel Worksheet Functions | |||
Unprotect worksheet via VBA | Excel Worksheet Functions | |||
Unprotect | Excel Discussion (Misc queries) | |||
Unprotect Sheets | Excel Worksheet Functions |