|
Find and Replace HELP!
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!!! |
Find and Replace HELP!
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 |
Find and Replace HELP!
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 |
Find and Replace HELP!
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 |
| All times are GMT +1. The time now is 09:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com