Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select all the unlocked cells on a worksheet
Hi,
does anyone know how to select all the unlocked cells on a worksheet. All i need to do is show which cells are unlocked without changing the formatting of the cells. Thanks for any help, Steven |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select all the unlocked cells on a worksheet
You can use the following code
Dim rgeStart As Range Dim rgeEnd As Range Dim Z As Integer, Y As Integer Dim str1 As String, str2 As String Set rgeStart = Application.Range("A1") Set rgeEnd = Application.Cells.SpecialCells (xlCellTypeLastCell) For Z = 1 To rgeEnd.Row For Y = 1 To rgeEnd.Column If Not Application.Cells(Z, Y).Locked Then str1 = Mid(Application.Cells(Z, Y).Address, 2, 1) & Mid(Application.Cells(Z, Y).Address, 4, 1) & "," str2 = str2 & str1 End If Next Y Next Z str2 = Left$(str2, Len(str2) - 1) Application.Range(str2).Select -----Original Message----- Hi, does anyone know how to select all the unlocked cells on a worksheet. All i need to do is show which cells are unlocked without changing the formatting of the cells. Thanks for any help, Steven . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select all the unlocked cells on a worksheet
The obvious is to loop through all the cells in the sheet and check the
locked property of the cell, building a union. If the sheet is protected, you can use sendkeys to travel through them: Sub GetUnlocked() Dim rng As Range Dim cell As Range SendKeys "{tab}", True Set rng = ActiveCell Debug.Print rng.Address SendKeys "{tab}", True Set cell = ActiveCell Debug.Print cell.Address Do While Intersect(cell, rng) Is Nothing Set rng = Union(rng, cell) SendKeys "{tab}", True Set cell = ActiveCell Debug.Print rng.Address, cell.Address Loop rng.Select End Sub -- Regards, Tom Ogilvy Steven Revell wrote in message ... Hi, does anyone know how to select all the unlocked cells on a worksheet. All i need to do is show which cells are unlocked without changing the formatting of the cells. Thanks for any help, Steven |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locked Several Worksheets, allow format cells/select unlocked cell | Excel Discussion (Misc queries) | |||
Moving from unlocked cells to unlocked cells in an excel form | Excel Worksheet Functions | |||
Marking unlocked cells in active worksheet | Excel Discussion (Misc queries) | |||
how do you "select locked cells" w/o "select unlocked cells"? | Excel Discussion (Misc queries) | |||
How can I merge unlocked cells in a worksheet that has been protec | Excel Discussion (Misc queries) |