Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return cells that contain formulas on a protected sheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return cells that contain formulas on a protected sheet
I am trying to avoid cracking the password and then reprotecting the
sheet. 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 -- Jim "M. Authement" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return cells that contain formulas on a protected sheet
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 :-) "Jim Rech" wrote in message ... I am trying to avoid cracking the password and then reprotecting the sheet. 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 -- Jim "M. Authement" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return cells that contain formulas on a protected sheet
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" wrote: 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 :-) "Jim Rech" wrote in message ... I am trying to avoid cracking the password and then reprotecting the sheet. 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 -- Jim "M. Authement" wrote in message ... 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. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return cells that contain formulas on a protected sheet
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" wrote in message ... 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" wrote: 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 :-) "Jim Rech" wrote in message ... I am trying to avoid cracking the password and then reprotecting the sheet. 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 -- Jim "M. Authement" wrote in message ... 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. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return cells that contain formulas on a protected sheet
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. "M. Authement" wrote: 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" wrote in message ... 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" wrote: 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 :-) "Jim Rech" wrote in message ... I am trying to avoid cracking the password and then reprotecting the sheet. 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 -- Jim "M. Authement" wrote in message ... 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. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enabling formulas in a semi-protected sheet | Excel Worksheet Functions | |||
Copying formulas into protected cells. | Excel Discussion (Misc queries) | |||
Protected sheet disables return key. | Excel Discussion (Misc queries) | |||
HOW CAN I HAVE THE SHEET TO BE PROTECTED, FORMULAS HIDDEN AND YET GIVE AN OPTION IN VB TO HAVE A TOGGLE TO PROTECT/ UNPROTECT/HIDE/UNHIDE CELLS | Excel Programming | |||
Return boolean if sheet is currently protected | Excel Programming |