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

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
...
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

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" wrote in message
...
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
...
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