this seems to be that it should be easy, but i am having
a heck of a time and any help would be greatly
appreciated!

what i have is a protected worksheet. but i want the
users to be able to do a mass replace on their formulas
if they wish. you cannot do that unless you unprotect
the worksheet.

first, i thought i would create a command button on my
custom toolbar that simply unprotects the worksheet, uses
the bulit-in dialog box of xlDialogFormulaReplace, and
then protect the worksheet again. well, it works just
fine, except if you use it, it disables the normal Edit
menu Find/Replace capability for the whole workbook. and
i don't want to do that. it doesn't give you any errors,
just won't work when you select them from the menu.
nothing happens at all.

so, i thought i would make the button unlock the
worksheet, then use the Execute method of the popup
command that is Replace, from the Edit menu. and then
lock the worksheet back up.

well, this works great, i prefer the replace dialog you
get from the Edit menu anyway. but, it does the Execute,
and doesn't stop, it isn't modal. so it just unlocks,
shows the dialog box, and locks right back up before you
can use the dialog box!!

surely someone else has done this!! if so PLEASE let me
know how you got around it!

thanks much!!!

I can't reproduce that behavior.

Any chance you're protecting too many sheets?

Option Explicit
Sub testme()

With ActiveSheet
.Unprotect Password:="hi"
Application.Dialogs(xlDialogFormulaReplace).Show
.Protect Password:="hi"
End With

End Sub

didn't affect any other sheet (xl2002).

alfaista wrote:

this seems to be that it should be easy, but i am having
a heck of a time and any help would be greatly
appreciated!

what i have is a protected worksheet. but i want the
users to be able to do a mass replace on their formulas
if they wish. you cannot do that unless you unprotect
the worksheet.

first, i thought i would create a command button on my
custom toolbar that simply unprotects the worksheet, uses
the bulit-in dialog box of xlDialogFormulaReplace, and
then protect the worksheet again. well, it works just
fine, except if you use it, it disables the normal Edit
menu Find/Replace capability for the whole workbook. and
i don't want to do that. it doesn't give you any errors,
just won't work when you select them from the menu.
nothing happens at all.

so, i thought i would make the button unlock the
worksheet, then use the Execute method of the popup
command that is Replace, from the Edit menu. and then
lock the worksheet back up.

well, this works great, i prefer the replace dialog you
get from the Edit menu anyway. but, it does the Execute,
and doesn't stop, it isn't modal. so it just unlocks,
shows the dialog box, and locks right back up before you
can use the dialog box!!

surely someone else has done this!! if so PLEASE let me
know how you got around it!

thanks much!!!

--

Dave Peterson

no, it is the only protected worksheet in the workbook. it only has 2 sheets at the moment.

when you did this bit of code, did it not "re-protect" the worksheet before you did anything with the find/replace dialog box? this is the problem i had with that function, the box wasn't modal so it just re-protected the worksheet before i could replace anything

thank so much for responding

well, i am confused. i tried it again with nothing being changed and the message telling me that the workbook was protected. so i pasted your code on top of mine, since i didn't use With, and nothing
so i tried again, this time i used By Columns instead, and it worked.
i switched back to By Rows, and it works.

now, it seems to work fine.

i've no idea, but, thanks!!




----- Dave Peterson wrote: ----

I can't reproduce that behavior

Any chance you're protecting too many sheets

Option Explici
Sub testme(

With ActiveShee
.Unprotect Password:="hi
Application.Dialogs(xlDialogFormulaReplace).Sho
.Protect Password:="hi
End Wit

End Su

didn't affect any other sheet (xl2002)

alfaista wrote
this seems to be that it should be easy, but i am havin
a heck of a time and any help would be greatl
appreciated
what i have is a protected worksheet. but i want th
users to be able to do a mass replace on their formula
if they wish. you cannot do that unless you unprotec
the worksheet
first, i thought i would create a command button on m
custom toolbar that simply unprotects the worksheet, use
the bulit-in dialog box of xlDialogFormulaReplace, an
then protect the worksheet again. well, it works jus
fine, except if you use it, it disables the normal Edi
menu Find/Replace capability for the whole workbook. an
i don't want to do that. it doesn't give you any errors
just won't work when you select them from the menu
nothing happens at all
so, i thought i would make the button unlock th
worksheet, then use the Execute method of the popu
command that is Replace, from the Edit menu. and the
lock the worksheet back up
well, this works great, i prefer the replace dialog yo
get from the Edit menu anyway. but, it does the Execute
and doesn't stop, it isn't modal. so it just unlocks
shows the dialog box, and locks right back up before yo
can use the dialog box!
surely someone else has done this!! if so PLEASE let m
know how you got around it
thanks much!!

--

Dave Peterso

I'd call that an excellent fix!

Take credit for it and walk away fast.

alfaista wrote:

well, i am confused. i tried it again with nothing being changed and the message telling me that the workbook was protected. so i pasted your code on top of mine, since i didn't use With, and nothing.
so i tried again, this time i used By Columns instead, and it worked.
i switched back to By Rows, and it works.

now, it seems to work fine.

i've no idea, but, thanks!!!


----- Dave Peterson wrote: -----

I can't reproduce that behavior.

Any chance you're protecting too many sheets?

Option Explicit
Sub testme()

With ActiveSheet
.Unprotect Password:="hi"
Application.Dialogs(xlDialogFormulaReplace).Show
.Protect Password:="hi"
End With

End Sub

didn't affect any other sheet (xl2002).

alfaista wrote:
this seems to be that it should be easy, but i am having
a heck of a time and any help would be greatly
appreciated!
what i have is a protected worksheet. but i want the
users to be able to do a mass replace on their formulas
if they wish. you cannot do that unless you unprotect
the worksheet.
first, i thought i would create a command button on my
custom toolbar that simply unprotects the worksheet, uses
the bulit-in dialog box of xlDialogFormulaReplace, and
then protect the worksheet again. well, it works just
fine, except if you use it, it disables the normal Edit
menu Find/Replace capability for the whole workbook. and
i don't want to do that. it doesn't give you any errors,
just won't work when you select them from the menu.
nothing happens at all.
so, i thought i would make the button unlock the
worksheet, then use the Execute method of the popup
command that is Replace, from the Edit menu. and then
lock the worksheet back up.
well, this works great, i prefer the replace dialog you
get from the Edit menu anyway. but, it does the Execute,
and doesn't stop, it isn't modal. so it just unlocks,
shows the dialog box, and locks right back up before you
can use the dialog box!!
surely someone else has done this!! if so PLEASE let me
know how you got around it!
thanks much!!!

--

Dave Peterson



--

Dave Peterson