Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Find whilst protected (Excel 97 sr2)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find whilst protected (Excel 97 sr2)

    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.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find whilst protected (Excel 97 sr2)

    In addition to Hans' suggestions, you might consider downloading my flexfind.zip from the Excel MVP page stated below.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Find whilst protected (Excel 97 sr2)

    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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find whilst protected (Excel 97 sr2)

    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

  6. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Find whilst protected (Excel 97 sr2)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •