Find/Replace Event or Find/Replace for Protected Sheet ...

[Joe HM]

Hello -

I have a worksheet that is protected but I would like to allow the
user to do a find/replace in a defined range of that sheet. The cells
of that range are unlocked for editing already.

Is there any way to allow the Find/Replace to work on a protected
sheet?

Alternatively, I was wondering if there is a way to "intercept" the
Find/Replace, unprotect the sheet, have the Find/Replace dialog
executed and then protect the sheet again? Is there something like a
Application.Replace() available somewhere?

Thanks,
Joe

 

[Tom Ogilvy]

If you set the UserInterfaceOnly property of the Protect method, it allows
your code to operate on a protected sheet. You can get code for doing a
Find/replace by using the macro recorder or looking the VBA help for those
methods of the Range object.

Look in the vba help for tge Protect method of the worksheet. The
userinterfaceonly property must be set with code and is non persistent when
you close the workbook, so it would need to be set in the workbook_open event
or some other appropriate event.

 

[Joe HM]

Hello -

Thanks! I think the UserInterfaceOnly property will work for me.

I know that I can call the Cell.Replace function to do a find/replace
but I was wondering how I can make the application-level Find/Replace
dialog appear. The problem with the Cell.Replace() is that the user
will not have the option to enter anything unless I create my own
dialog. Is there a way to do the Ctrl-H which opens this Excel
dialog? Similarily, I am trying to do a spell check but I think that
I have figured out and the UserInterfaceOnly will help.

Thanks,
Joe