Peter T
12/14/2006 8:47:00 AM
Hi Nick,
I should have said I was picking up from your suggestion earlier in this
thread with an attempt to address this bit (by limiting to a particular
sheet) -
"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."
I didn't know the OP had posted similar question twice!
Regards,
Peter T
"NickHK" <TungCheWah@Invalid.com> wrote in message
news:#ienXHzHHHA.4068@TK2MSFTNGP03.phx.gbl...
> Peter,
> That was the approach I suggested to the OP 2 days ago, but apparently the
> OP had to ask again.
>
> NickHK
>
> "Peter T" <peter_t@discussions> wrote in message
> news:uUPMTtwHHHA.4056@TK2MSFTNGP03.phx.gbl...
> > > 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
> >
> >
>
>