Find whilst protected (Excel 97 sr2)

Topic

New Reply

Briefly:
Excel worksheet, header cells are locked, remainder are unlocked.
Worksheet is protected.
Users can add details but not change headers.
Ctrl+F opens Find dialog but the search does not return any found cells.
I have looked for the xlDialog for Find but cannot find it (sic), I had in mind to have a command button to unprotect the sheet then display the find dialog and then re-protect the sheet after closing the find dialog.
Alternatives would be to use a form but that seems a bit heavyweight when Excels find dialog looks fine for our use.

Any suggestions would be welcome.
TIA
Alan
Cheshire
UK

Reply | Quote

Replies

Firstly, check carefully if Find really doesn’t work – I have no problem searching for values in a protected worksheet (I’m also using Excel 97 SR2). Sometimes the dialog settings prevent you from finding what you want (look in formulas or values, case sensitive or not, entire cell or not).

In VBA, Application.Dialogs(xlDialogFormulaFind) is the Find dialog, and Application.Dialogs(xlDialogFormulaReplace) is the Replace dialog. If you want to initialize dialog settings, you can find the list of arguments for built-in dialogs in the online Help.

Reply | Quote

In addition to Hans’ suggestions, you might consider downloading my flexfind.zip from the Excel MVP page stated below.

Reply | Quote

Thanks guys. Good pointers to try.
Strange things are afoot.
Hans’ suggestion that the find was in formula/values solved part of the problem and I did get results from ensuring Lookin was “Values”.
I believe I now have sufficient to drop a sub in there to cope with the generic find requirements of the team.
Ctrl+F works most of the time but for some strange reason ( and I cannot reliably replicate it every time) the search comes back with not found dialog.
Still, from your suggestions with the xlDialogFormulaFind I have the solution.
Thanks again.
Alan
Cheshire
UK

Reply | Quote

Another point is:
What is the selection when find is run?
If the selection is only one cell, find looks everywhere in the sheet
If the selection is more than 1 cell, find ONLY looks in the selection.

I have been frustrated by this at times, not realizing that on a particular sheet a section is selected, not just one cell and then get confused when the find doesn’t give me the expected results.

Steve

Reply | Quote

Thanks Steve.
Yes, the cell selection that I have is one only. I too have been caught out with that.
In the intervening moments I now have a working system that has a search button which prompts with an inputbox for user input.
I have taken this route for the simple reason that I can now stipulate exactly how the search is started, i.e. one cell selected,values only etcetera.

One of the most frustrating problems which I’m sure a lot of people have is that whilst the end user (125 miles away oop north as we say) insists that he is not looking in formula and “of course I only have one cell selected” I sometimes wonder whether the cell he is referring to is enclosed in his cranial cavity.

I shall be producing a list of do’s and don’t’s for the end user and the “single cell selection” will be on the list.

Thanks to all for the responses but I guess we can now chalk this upon the success chart.

Alan
Cheshire
UK

Reply | Quote