I am using the following code to find the content of a cell in another
visible worksheet. The problem is that the other worksheets are typically
protected. Accordingly, when it finds the content of the cell in another
worksheet that is protected it is unable to reposition the cursor to that
cell location (foundcell).

Debug: Application.Goto FoundCell
(note: code works perfectly if other worksheets are unprotected)

What is the additional code needed to unprotect the worksheet where cell
content is found, reposition cursor to the "FoundCell" and then reprotect
that worksheet?

Thank you!
Clive

Sub FindCellContent()

Dim Sh As Worksheet, foundCell As Range

'Search for CellContent in all Visible Worksheets
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = xlSheetVisible Then

'If you want to ignore the active sheet unmark the below line
'If ActiveSheet.Name < Sh.Name Then

Set foundCell = Sh.Cells.Find(What:=ActiveCell, _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If Not foundCell Is Nothing Then
If foundCell.Address(External:=True) < _
ActiveCell.Address(External:=True) Then _
Application.Goto foundCell: Exit For
End If

'/If you want to ignore the active sheet
'End If

End If
Next

If foundCell Is Nothing Then _
MsgBox ActiveCell & " not found in this workbook"

End Sub

Your code works fine for me...

Dan

"RiverGully" wrote:

I am using the following code to find the content of a cell in another
visible worksheet. The problem is that the other worksheets are typically
protected. Accordingly, when it finds the content of the cell in another
worksheet that is protected it is unable to reposition the cursor to that
cell location (foundcell).

Debug: Application.Goto FoundCell
(note: code works perfectly if other worksheets are unprotected)

What is the additional code needed to unprotect the worksheet where cell
content is found, reposition cursor to the "FoundCell" and then reprotect
that worksheet?

Thank you!
Clive

Sub FindCellContent()

Dim Sh As Worksheet, foundCell As Range

'Search for CellContent in all Visible Worksheets
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = xlSheetVisible Then

'If you want to ignore the active sheet unmark the below line
'If ActiveSheet.Name < Sh.Name Then

Set foundCell = Sh.Cells.Find(What:=ActiveCell, _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If Not foundCell Is Nothing Then
If foundCell.Address(External:=True) < _
ActiveCell.Address(External:=True) Then _
Application.Goto foundCell: Exit For
End If

'/If you want to ignore the active sheet
'End If

End If
Next

If foundCell Is Nothing Then _
MsgBox ActiveCell & " not found in this workbook"

End Sub

Hi Dan,

With regard to Protection in my workbook, the option 'Protect Locked Cells'
is unchecked. User can only click on unlocked cells within the protected
worksheet. I think this is why the code does not work in my workbook.

When the cell content is found on a worksheet that is protected with
'Protect Locked Cells' unchecked... can this be change to complete the serach
and then set back again?

Many thanks!

"Dan" wrote:

Your code works fine for me...

Dan

"RiverGully" wrote:

I am using the following code to find the content of a cell in another
visible worksheet. The problem is that the other worksheets are typically
protected. Accordingly, when it finds the content of the cell in another
worksheet that is protected it is unable to reposition the cursor to that
cell location (foundcell).

Debug: Application.Goto FoundCell
(note: code works perfectly if other worksheets are unprotected)

What is the additional code needed to unprotect the worksheet where cell
content is found, reposition cursor to the "FoundCell" and then reprotect
that worksheet?

Thank you!
Clive

Sub FindCellContent()

Dim Sh As Worksheet, foundCell As Range

'Search for CellContent in all Visible Worksheets
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = xlSheetVisible Then

'If you want to ignore the active sheet unmark the below line
'If ActiveSheet.Name < Sh.Name Then

Set foundCell = Sh.Cells.Find(What:=ActiveCell, _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If Not foundCell Is Nothing Then
If foundCell.Address(External:=True) < _
ActiveCell.Address(External:=True) Then _
Application.Goto foundCell: Exit For
End If

'/If you want to ignore the active sheet
'End If

End If
Next

If foundCell Is Nothing Then _
MsgBox ActiveCell & " not found in this workbook"

End Sub

Protect Sheet:
Allow all users of this worksheet to:
Select locked cells (this is unchecked in my protected worksheets)

If it is checked then the code works fine... however, we need it unchecked.

Hope this helps clarify my setup.

Clive

"RiverGully" wrote:

Hi Dan,

With regard to Protection in my workbook, the option 'Protect Locked Cells'
is unchecked. User can only click on unlocked cells within the protected
worksheet. I think this is why the code does not work in my workbook.

When the cell content is found on a worksheet that is protected with
'Protect Locked Cells' unchecked... can this be change to complete the serach
and then set back again?

Many thanks!

"Dan" wrote:

Your code works fine for me...

Dan

"RiverGully" wrote:

I am using the following code to find the content of a cell in another
visible worksheet. The problem is that the other worksheets are typically
protected. Accordingly, when it finds the content of the cell in another
worksheet that is protected it is unable to reposition the cursor to that
cell location (foundcell).

Debug: Application.Goto FoundCell
(note: code works perfectly if other worksheets are unprotected)

What is the additional code needed to unprotect the worksheet where cell
content is found, reposition cursor to the "FoundCell" and then reprotect
that worksheet?

Thank you!
Clive

Sub FindCellContent()

Dim Sh As Worksheet, foundCell As Range

'Search for CellContent in all Visible Worksheets
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = xlSheetVisible Then

'If you want to ignore the active sheet unmark the below line
'If ActiveSheet.Name < Sh.Name Then

Set foundCell = Sh.Cells.Find(What:=ActiveCell, _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If Not foundCell Is Nothing Then
If foundCell.Address(External:=True) < _
ActiveCell.Address(External:=True) Then _
Application.Goto foundCell: Exit For
End If

'/If you want to ignore the active sheet
'End If

End If
Next

If foundCell Is Nothing Then _
MsgBox ActiveCell & " not found in this workbook"

End Sub

Hi.. can you help with this one?

As 'Select locked cells' is unchecked when the worksheets are protected the
code returns that error message... if 'Select Locked Cells' is enabled when
protecting the worksheets the code would work.

Is there a way to run the code and have it enable Select locked cells before
searching for the cell contents on protected worksheets, then after it
finishes searching, disable select locked cells on those protected worksheets?

Many thanks! Clive

"Dan" wrote:

Your code works fine for me...

Dan

"RiverGully" wrote:

I am using the following code to find the content of a cell in another
visible worksheet. The problem is that the other worksheets are typically
protected. Accordingly, when it finds the content of the cell in another
worksheet that is protected it is unable to reposition the cursor to that
cell location (foundcell).

Debug: Application.Goto FoundCell
(note: code works perfectly if other worksheets are unprotected)

What is the additional code needed to unprotect the worksheet where cell
content is found, reposition cursor to the "FoundCell" and then reprotect
that worksheet?

Thank you!
Clive

Sub FindCellContent()

Dim Sh As Worksheet, foundCell As Range

'Search for CellContent in all Visible Worksheets
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = xlSheetVisible Then

'If you want to ignore the active sheet unmark the below line
'If ActiveSheet.Name < Sh.Name Then

Set foundCell = Sh.Cells.Find(What:=ActiveCell, _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If Not foundCell Is Nothing Then
If foundCell.Address(External:=True) < _
ActiveCell.Address(External:=True) Then _
Application.Goto foundCell: Exit For
End If

'/If you want to ignore the active sheet
'End If

End If
Next

If foundCell Is Nothing Then _
MsgBox ActiveCell & " not found in this workbook"

End Sub