Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select all unlocked cells on a sheet?
I need a sub procedure that selects all unlocked cells on a worsheet.
Go to special doesn't offer this as an option. What I want to do is akin to something like Selection.SpecialCells(xlCellTypeBlanks).Select, but instead of blanks I want to select cells where Locked = False. I've tried a few things with Application.FindFormat.Locked = False, but I can't figure out to select ALL the FindFormat.Locked = False cells. Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select all unlocked cells on a sheet?
Try using this function to get there.
Option Explicit Function GetUnlocked(myWS As Excel.Worksheet) As Excel.Range Dim r As Excel.Range Set GetUnlocked = Nothing For Each r In myWS.UsedRange If Not r.Locked Then If GetUnlocked = Nothing Then Set GetUnlocked = r Else Set GetUnlocked = Union(GetUnlocked, r) End If End If Next r End Function "Ted M H" wrote: I need a sub procedure that selects all unlocked cells on a worsheet. Go to special doesn't offer this as an option. What I want to do is akin to something like Selection.SpecialCells(xlCellTypeBlanks).Select, but instead of blanks I want to select cells where Locked = False. I've tried a few things with Application.FindFormat.Locked = False, but I can't figure out to select ALL the FindFormat.Locked = False cells. Any suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select all unlocked cells on a sheet?
Give the following macro a try. By default, it works on the ActiveSheet, but
you can change this to a specific worksheet by changing the worksheet reference in the With statement on the third code line. Sub ClearUnlockedCells() Dim C As Range, FoundCells As Range, FirstAddress As String Application.FindFormat.Locked = False With ActiveSheet.UsedRange Set C = .Find("", SearchFormat:=True) If Not C Is Nothing Then FirstAddress = C.Address Do If FoundCells Is Nothing Then Set FoundCells = C Else Set FoundCells = Union(FoundCells, C) End If Set C = .Find("", after:=C, SearchFormat:=True) Loop While Not C Is Nothing And C.Address < FirstAddress End If If Not FoundCells Is Nothing Then FoundCells.Select End With Application.FindFormat.Clear End Sub -- Rick (MVP - Excel) "Ted M H" wrote in message ... I need a sub procedure that selects all unlocked cells on a worsheet. Go to special doesn't offer this as an option. What I want to do is akin to something like Selection.SpecialCells(xlCellTypeBlanks).Select, but instead of blanks I want to select cells where Locked = False. I've tried a few things with Application.FindFormat.Locked = False, but I can't figure out to select ALL the FindFormat.Locked = False cells. Any suggestions? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select all unlocked cells on a sheet?
You can either loop through each cell and create a range.
Or you can use the .findformat. It would be nice to use the .findnext method, but that doesn't work with formatting (at least not in xl2003). Option Explicit Sub testme() Dim wks As Worksheet Dim myUnlockedRng As Range Dim FoundCell As Range Dim FirstAddress As String Set wks = ActiveSheet With wks 'clear any existing formatting that was used Application.FindFormat.Clear 'just the unlocked cells Application.FindFormat.Locked = False Set FoundCell = .Cells.Find(what:="", _ after:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ lookat:=xlPart, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False, _ searchformat:=True) If FoundCell Is Nothing Then 'not one unlocked cell! Else 'keep track of where the first one was, so we can quit 'when we find this again. FirstAddress = FoundCell.Address 'start building the range of unlocked cells Set myUnlockedRng = FoundCell Do Set FoundCell = .Cells.Find(what:="", _ after:=FoundCell, _ LookIn:=xlFormulas, _ lookat:=xlPart, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False, _ searchformat:=True) If FoundCell Is Nothing Then Exit Do End If If FoundCell.Address = FirstAddress Then Exit Do End If 'add to the growing range Set myUnlockedRng = Union(myUnlockedRng, FoundCell) Loop End If End With If myUnlockedRng Is Nothing Then MsgBox "None found!" Else Application.Goto myUnlockedRng ', scroll:=True End If End Sub Ted M H wrote: I need a sub procedure that selects all unlocked cells on a worsheet. Go to special doesn't offer this as an option. What I want to do is akin to something like Selection.SpecialCells(xlCellTypeBlanks).Select, but instead of blanks I want to select cells where Locked = False. I've tried a few things with Application.FindFormat.Locked = False, but I can't figure out to select ALL the FindFormat.Locked = False cells. Any suggestions? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select all unlocked cells on a sheet?
I meant to replace this portion of the text:
"Or you can use the .findformat." with the "it would be nice to ..." portion, but messed up. Dave Peterson wrote: You can either loop through each cell and create a range. Or you can use the .findformat. It would be nice to use the .findnext method, but that doesn't work with formatting (at least not in xl2003). Option Explicit Sub testme() Dim wks As Worksheet Dim myUnlockedRng As Range Dim FoundCell As Range Dim FirstAddress As String Set wks = ActiveSheet With wks 'clear any existing formatting that was used Application.FindFormat.Clear 'just the unlocked cells Application.FindFormat.Locked = False Set FoundCell = .Cells.Find(what:="", _ after:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ lookat:=xlPart, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False, _ searchformat:=True) If FoundCell Is Nothing Then 'not one unlocked cell! Else 'keep track of where the first one was, so we can quit 'when we find this again. FirstAddress = FoundCell.Address 'start building the range of unlocked cells Set myUnlockedRng = FoundCell Do Set FoundCell = .Cells.Find(what:="", _ after:=FoundCell, _ LookIn:=xlFormulas, _ lookat:=xlPart, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False, _ searchformat:=True) If FoundCell Is Nothing Then Exit Do End If If FoundCell.Address = FirstAddress Then Exit Do End If 'add to the growing range Set myUnlockedRng = Union(myUnlockedRng, FoundCell) Loop End If End With If myUnlockedRng Is Nothing Then MsgBox "None found!" Else Application.Goto myUnlockedRng ', scroll:=True End If End Sub Ted M H wrote: I need a sub procedure that selects all unlocked cells on a worsheet. Go to special doesn't offer this as an option. What I want to do is akin to something like Selection.SpecialCells(xlCellTypeBlanks).Select, but instead of blanks I want to select cells where Locked = False. I've tried a few things with Application.FindFormat.Locked = False, but I can't figure out to select ALL the FindFormat.Locked = False cells. Any suggestions? -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select all unlocked cells on a sheet?
Ignore this "correction". I originally wrote what I wanted.
(I reread it as using the .findnext, not .findformat and that won't work.) (Sigh) Dave Peterson wrote: I meant to replace this portion of the text: "Or you can use the .findformat." with the "it would be nice to ..." portion, but messed up. Dave Peterson wrote: You can either loop through each cell and create a range. Or you can use the .findformat. It would be nice to use the .findnext method, but that doesn't work with formatting (at least not in xl2003). Option Explicit Sub testme() Dim wks As Worksheet Dim myUnlockedRng As Range Dim FoundCell As Range Dim FirstAddress As String Set wks = ActiveSheet With wks 'clear any existing formatting that was used Application.FindFormat.Clear 'just the unlocked cells Application.FindFormat.Locked = False Set FoundCell = .Cells.Find(what:="", _ after:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ lookat:=xlPart, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False, _ searchformat:=True) If FoundCell Is Nothing Then 'not one unlocked cell! Else 'keep track of where the first one was, so we can quit 'when we find this again. FirstAddress = FoundCell.Address 'start building the range of unlocked cells Set myUnlockedRng = FoundCell Do Set FoundCell = .Cells.Find(what:="", _ after:=FoundCell, _ LookIn:=xlFormulas, _ lookat:=xlPart, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False, _ searchformat:=True) If FoundCell Is Nothing Then Exit Do End If If FoundCell.Address = FirstAddress Then Exit Do End If 'add to the growing range Set myUnlockedRng = Union(myUnlockedRng, FoundCell) Loop End If End With If myUnlockedRng Is Nothing Then MsgBox "None found!" Else Application.Goto myUnlockedRng ', scroll:=True End If End Sub Ted M H wrote: I need a sub procedure that selects all unlocked cells on a worsheet. Go to special doesn't offer this as an option. What I want to do is akin to something like Selection.SpecialCells(xlCellTypeBlanks).Select, but instead of blanks I want to select cells where Locked = False. I've tried a few things with Application.FindFormat.Locked = False, but I can't figure out to select ALL the FindFormat.Locked = False cells. Any suggestions? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select all unlocked cells on a sheet?
Your second If..Then test should be this...
If GetUnlocked Is Nothing Then where I have used the Is keyword in place of your equal sign. I guess you put your code in the form of a function rather than a macro so that the user has the flexibility to do more than simply select the unlocked cells. Given that, the OP should be made aware that he will need to test the return value from your function for not being Nothing, otherwise the OP's code will error out if he runs your function against a worksheet that has no unlocked cells on it. -- Rick (MVP - Excel) "Barb Reinhardt" wrote in message ... Try using this function to get there. Option Explicit Function GetUnlocked(myWS As Excel.Worksheet) As Excel.Range Dim r As Excel.Range Set GetUnlocked = Nothing For Each r In myWS.UsedRange If Not r.Locked Then If GetUnlocked = Nothing Then Set GetUnlocked = r Else Set GetUnlocked = Union(GetUnlocked, r) End If End If Next r End Function "Ted M H" wrote: I need a sub procedure that selects all unlocked cells on a worsheet. Go to special doesn't offer this as an option. What I want to do is akin to something like Selection.SpecialCells(xlCellTypeBlanks).Select, but instead of blanks I want to select cells where Locked = False. I've tried a few things with Application.FindFormat.Locked = False, but I can't figure out to select ALL the FindFormat.Locked = False cells. Any suggestions? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select all unlocked cells on a sheet?
How about: Sub GetUnlocked() Dim r As Range, rUnlocked As Range Set rUnlocked = Nothing For Each r In ActiveSheet.UsedRange If r.Locked = False Then If rUnlocked Is Nothing Then Set rUnlocked = r Else Set rUnlocked = Union(r, rUnlocked) End If End If Next If rUnlocked Is Nothing Then Else rUnlocked.Select End If End Sub This will select all the unlocked cells in the used ange. -- Gary''s Student - gsnu200908 "Ted M H" wrote: I need a sub procedure that selects all unlocked cells on a worsheet. Go to special doesn't offer this as an option. What I want to do is akin to something like Selection.SpecialCells(xlCellTypeBlanks).Select, but instead of blanks I want to select cells where Locked = False. I've tried a few things with Application.FindFormat.Locked = False, but I can't figure out to select ALL the FindFormat.Locked = False cells. Any suggestions? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select all unlocked cells on a sheet?
Yup, you're right. I didn't test it. :(
"Rick Rothstein" wrote: Your second If..Then test should be this... If GetUnlocked Is Nothing Then where I have used the Is keyword in place of your equal sign. I guess you put your code in the form of a function rather than a macro so that the user has the flexibility to do more than simply select the unlocked cells. Given that, the OP should be made aware that he will need to test the return value from your function for not being Nothing, otherwise the OP's code will error out if he runs your function against a worksheet that has no unlocked cells on it. -- Rick (MVP - Excel) "Barb Reinhardt" wrote in message ... Try using this function to get there. Option Explicit Function GetUnlocked(myWS As Excel.Worksheet) As Excel.Range Dim r As Excel.Range Set GetUnlocked = Nothing For Each r In myWS.UsedRange If Not r.Locked Then If GetUnlocked = Nothing Then Set GetUnlocked = r Else Set GetUnlocked = Union(GetUnlocked, r) End If End If Next r End Function "Ted M H" wrote: I need a sub procedure that selects all unlocked cells on a worsheet. Go to special doesn't offer this as an option. What I want to do is akin to something like Selection.SpecialCells(xlCellTypeBlanks).Select, but instead of blanks I want to select cells where Locked = False. I've tried a few things with Application.FindFormat.Locked = False, but I can't figure out to select ALL the FindFormat.Locked = False cells. Any suggestions? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select locked/unlocked cells | Excel Programming | |||
how do you "select locked cells" w/o "select unlocked cells"? | Excel Discussion (Misc queries) | |||
How select unlocked cells only | Excel Programming | |||
Password Protection - Select Unlocked Cells Only | Excel Programming | |||
Select all the unlocked cells on a worksheet | Excel Programming |