Return cells that contain formulas on a protected sheet

[M. Authement]

I am creating a function that alters formulas in a user selected range. In
order to avoid looping through each cell in the range I was using the
SpecialCells method but this will not work if the sheet is protected. Is
there another way to return the formula-containing cells only without
looping through the entire selected range (which could be the entire
sheet!). I am trying to avoid cracking the password and then reprotecting
the sheet.

 

[Jim Rech]

I am trying to avoid cracking the password and then reprotecting the
I don't think you have to. Here Sheet1 is already protected with a
password. The first line switches on "userinterfaceonly" and then Special
Cells works.

Sub a()
Sheet1.Protect , , , , True
Cells.SpecialCells(xlCellTypeFormulas).Select
End Sub

 

[M. Authement]

Thanks Jim! It never occurred to me that I could turn the 'userinterface
only' mode on after the sheet was protected. I have learned my 'something
new' for today

 

[Dave Peterson]

Be careful.

MS changed the way .protect worked in xl2002 (IIRC). In xl2k and below, you
could specify the userinterfaceonly:=true with providing the password.

In xl2002+, you have to specify the password.

(I _think_ it was xl2002 that changed this behavior. But maybe it was
xl2k???????)

 

[M. Authement]

Dave, you have rained on my parade :-(

I am using XL2003 and you are correct, I am being asked to supply a
password.

Does anyone have any other thoughts on how to reduce the selected range to
only the cells that contain formulas on a protected sheet? If not, I may
just test for a protected sheet and notify the user that the sheet has to be
unprotected before the procedure can be run.

 

[Dave Peterson]

You could loop through all the cells in the .usedrange building that range as
you go.

dim myFRng as range
dim myRng as range
dim myCell as range

set myRng = nothing
on error resume next
set myrng = intersect(selection, activesheet.usedrange)
on error goto 0

if myrng is nothing then
msgbox "Select cells in the used range!
exit sub
end if

set myfrng = nothing
for each mycell in myrng.cells
if mycell.hasformula then
if myFRng is nothing then
set myfrng = mycell
else
set myfrng = union(myfrng, mycell)
end if
end if
next mycell

if myfrng is nothing then
'no formulas in that selection
else
'it has formulas
end if

Could be pretty slow on sheets with lots of data.