Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Splitting out Content in 1 cell (which is currently separated by"ALT-ENTER") into multiple cells | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
protected sheets using "rowliner" | Excel Discussion (Misc queries) | |||
VBA to address first visible cell in Column "D" after filtering | Excel Discussion (Misc queries) | |||
Combining formulas, "and" & "or" to verify content of multiple cel | Excel Discussion (Misc queries) |