Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to know when a user has selected the find funct
You have the option to allow or disallow selection of locked and unlocked
cells in 2002. It is just when you do that (in 2002 anyways) it messes up the find function... HTH... Jim Thomlinson It's same in earlier versions. If useser's problem is 'EnableSelection', as you suggested, perhaps hook into the Find button's click event, unprotect then OnTime ReProtect '''''' class named "clsFindButton" Public WithEvents cbFind As Office.CommandBarButton Private Sub cbFind_Click(ByVal Ctrl As _ Office.CommandBarButton, CancelDefault As Boolean) DoProtection End Sub '''''' normal module Dim cFind As clsFindButton Public gWS As Worksheet Sub EndFind() Set cFind = Nothing Set gWS = Nothing ' Unprotect End Sub Sub SetFindEvents() Dim cb As CommandBarButton Set cb = Application.CommandBars.FindControl(ID:=1849) Set cFind = New clsFindButton Set cFind.cbFind = cb Set gWS = ActiveSheet Unprotect ActiveSheet.EnableSelection = xlUnlockedCells 'try and find "Find me" Range("x4:y4") = "Find me" Range("A1").Select ReProtect End Sub Sub DoProtection() 'could assign this with onkey to Ctrl-F If ActiveSheet Is gWS Then ShtProtect "abc", False Application.OnTime Now, "ReProtect" End If End Sub Sub Unprotect() ShtProtect "abc", False End Sub Run SetFindEvents then try and find "Find me". Would need a lot more before implementing. Regards, Peter T "Alok" wrote: Jim, Thanks for clarifying. I was referring to Excel 2003 where you have the option to enable selection of Locked and Unlocked cells when protecting a sheet. As you have probably correctly guessed, the OP is using an older Excel version. Alok "Jim Thomlinson" wrote: The issue revolves around (correct me if I am wrong Hippy) the sheet is protected and the protection allows only unlocked cells to be selected. When this is the case Find will not (in XL2002 at least) select the cells (locked or unlocked) which should be located in a find. Interestingly enough Find All will list the cells found but the cells will not be selected... Now that the problem is defined the answer is that it is not easily detectable when the user has initiated a find... While it is possible it is definitly not easy to do it right and well over the head of those who are not well versed in VBA and events. You would need to override the find functionallity in your current workbook while leaving the find functionallity in tact in the rest of the open workbooks, handling any errors that might come up. My suggestion would be to add a find button to the sheet which unprotects the sheet and then brings up the find dialog... something like this... Sheets("Sheet1").Unprotect Application.Dialogs(xlDialogFormulaFind).Show Sheets("Sheet1").Protect Now the only issue is that it can find cells that are locked (since there is no protection there is no restriction). When the protection is re-added the selected cell will move to the next available unlocked cell if a locked cell was chosen... -- HTH... Jim Thomlinson "Alok" wrote: Hi Hippy, The find functionality remains even if the worksheet is protected. Do you mean that through code you have disabled the Find functionality? If so just do not disable it. In my opinion It is not possible to find out what the user is doing - unless what he is doing raises events which can be captured. Alok "Hippy" wrote: I have a protected spreadsheet. The find functionality is disabled due to the protection. I would like to find a way to detect if the user is attempting to find something, Turn off the protection, perform the find and then protect the sheet again. Is there a way to detect that a user is executing a certain function? I have code that can unprotect and protect the worksheet. I just need to find out how I can fire it when the user selects the function. Hippy |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I sort on the column selected by the user? | Excel Programming | |||
How to sort on the row selected by user? | Excel Programming | |||
Identify User Selected Range | Excel Programming | |||
Checking to see if user selected an Option | Excel Programming | |||
How to find out what the user selected? | Excel Programming |