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

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

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