Find and Replace HELP!

[alfaista]

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]

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).

 

[Guest]

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

 

[Guest]

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

 

[Dave Peterson]

I'd call that an excellent fix!

Take credit for it and walk away fast.