LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27
Default Finding content of cell in visible "protected" sheets

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

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Splitting out Content in 1 cell (which is currently separated by"ALT-ENTER") into multiple cells Sunil Ahuja Excel Discussion (Misc queries) 1 March 24th 09 04:27 PM
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
protected sheets using "rowliner" Dino Excel Discussion (Misc queries) 1 April 21st 08 06:02 AM
VBA to address first visible cell in Column "D" after filtering EagleOne Excel Discussion (Misc queries) 2 December 11th 06 05:22 PM
Combining formulas, "and" & "or" to verify content of multiple cel Shu of AZ Excel Discussion (Misc queries) 15 October 15th 06 11:22 PM


All times are GMT +1. The time now is 01:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"