Unprotect not working

[Stuart]

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.

 

[Rob Bovey]

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]

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.