Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for locked/unlocked cells
Hi All
I have a spreadsheet which has many cells locked, and they are spread all across my worksheet. I want a code which will help me select locked/unlocked cells (as required) in the selected range in one go. Something like 'Visible cells only' and 'Blanks' options available in the 'Go to Special' screen. A macro that asks me "Go to:" Locked Cells Unlocked cells Thanks a lot Karthik Bhat |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for locked/unlocked cells
Karthik,
Paste the sub below into your personal.xls and assign it to a custom toolbar button. HTH, Bernie MS Excel MVP Sub SelectedLockedUnlockedCells() Dim myCell As Range Dim myRange As Range Dim SelLocked As Boolean Dim myReply As Variant myReply = MsgBox("Select Locked = ""Yes""" & Chr(10) & _ "Select UnLocked = ""No""", vbYesNoCancel) If myReply = vbCancel Then Exit Sub For Each myCell In Selection If myReply = vbYes And myCell.Locked Then If myRange Is Nothing Then Set myRange = myCell Else Set myRange = Union(myRange, myCell) End If End If If myReply = vbNo And Not myCell.Locked Then If myRange Is Nothing Then Set myRange = myCell Else Set myRange = Union(myRange, myCell) End If End If Next myCell If myRange Is Nothing Then MsgBox "No " & IIf(myReply = vbYes, "Locked", "Unlocked") & _ " cells found in the current selection." Exit Sub End If myRange.Select End Sub "Karthik Bhat - Bangalore" wrote in message ups.com... Hi All I have a spreadsheet which has many cells locked, and they are spread all across my worksheet. I want a code which will help me select locked/unlocked cells (as required) in the selected range in one go. Something like 'Visible cells only' and 'Blanks' options available in the 'Go to Special' screen. A macro that asks me "Go to:" Locked Cells Unlocked cells Thanks a lot Karthik Bhat |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for locked/unlocked cells
Karthik,
Depending on the WS Protection and .EnableSelection setting this may fail. NickHK "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Karthik, Paste the sub below into your personal.xls and assign it to a custom toolbar button. HTH, Bernie MS Excel MVP Sub SelectedLockedUnlockedCells() Dim myCell As Range Dim myRange As Range Dim SelLocked As Boolean Dim myReply As Variant myReply = MsgBox("Select Locked = ""Yes""" & Chr(10) & _ "Select UnLocked = ""No""", vbYesNoCancel) If myReply = vbCancel Then Exit Sub For Each myCell In Selection If myReply = vbYes And myCell.Locked Then If myRange Is Nothing Then Set myRange = myCell Else Set myRange = Union(myRange, myCell) End If End If If myReply = vbNo And Not myCell.Locked Then If myRange Is Nothing Then Set myRange = myCell Else Set myRange = Union(myRange, myCell) End If End If Next myCell If myRange Is Nothing Then MsgBox "No " & IIf(myReply = vbYes, "Locked", "Unlocked") & _ " cells found in the current selection." Exit Sub End If myRange.Select End Sub "Karthik Bhat - Bangalore" wrote in message ups.com... Hi All I have a spreadsheet which has many cells locked, and they are spread all across my worksheet. I want a code which will help me select locked/unlocked cells (as required) in the selected range in one go. Something like 'Visible cells only' and 'Blanks' options available in the 'Go to Special' screen. A macro that asks me "Go to:" Locked Cells Unlocked cells Thanks a lot Karthik Bhat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Viewing of protected locked or unlocked cells | Excel Worksheet Functions | |||
Display locked or Unlocked cells | Excel Discussion (Misc queries) | |||
Scope priviledges for locked/unlocked cells | Excel Discussion (Misc queries) | |||
paste locked cells and unlocked cells in protected sheet | Excel Worksheet Functions | |||
Highlighting locked or unlocked rows, Searching cells | Excel Programming |