Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Select Cells that are Locked (2003 sr2)

    Dear Reader,

    I am helping liz with some office work whilst she's away, she told me about the lounge & I think it's great. I will be registering as soon as my new email arrive, in the mentime I am borrowing her account and have this small problem:

    I want to have a macro to select all cells with their loccked property set (to "yes").

    I can do this sort of stuff in Word but am not sure if it works the same in Excel

    regards and thanks, margie

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Cells that are Locked (2003 sr2)

    Hi Margie

    Try this:

    Sub Set_Protection()
    Dim myWB As Worksheet
    Dim myCell As Range
    On Error GoTo errRange
    Set myWB = ActiveSheet
    For Each myCell In myWB.UsedRange
    If myCell.Locked = True Then

    myCell.Locked = False

    myCell.Value = "Yes"
    myCell.Locked = True
    Else
    myCell.Locked = False

    End If
    Next
    myWB.Protect
    Exit Sub
    errRange:
    MsgBox Error


    End Sub

    I have made it say Yes in the locked cell but I am a bit worried in case there is a formula in it so change it to this to highlight with colour:

    Sub Set_Protection()
    Dim myCell As Range
    On Error GoTo errRange
    Set myWB = ActiveSheet
    For Each myCell In myWB.UsedRange
    If myCell.Locked = True Then

    myCell.Locked = False

    myCell.Interior.ColorIndex = 6
    myCell.Locked = True
    Else
    myCell.Locked = False

    End If
    Next
    myWB.Protect
    Exit Sub
    errRange:
    MsgBox Error


    End Sub
    Jerry

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Select Cells that are Locked (2003 sr2)

    This will select all the locked cells in the used range.

    <pre>Option Explicit
    Sub SelectLocked()
    Dim rLocked As Range
    Dim rCell As Range
    For Each rCell In ActiveSheet.UsedRange
    If rCell.Locked Then
    If rLocked Is Nothing Then
    Set rLocked = rCell
    Else
    Set rLocked = Union(rLocked, rCell)
    End If
    End If
    Next
    rLocked.Select
    Set rLocked = Nothing
    Set rCell = Nothing
    End Sub</pre>


    If you truly want to select all the locked cells, including ones outside the used range, you could change the line to:
    <pre> For Each rCell In ActiveSheet.Cells</pre>


    But this will be very slow, since it must literally look at the properties of all 16,777,216 cells on the sheet.

    Steve

  4. #4
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Select Cells that are Locked (2003 sr2)

    Jerry,

    thank you for this - liz was rigt the service is great!

    Can i confirm what the macro you have given me does? It looks like it finds locked cells.. then I'm not sure what - it looks like it unlocks any found as locked. What I want to do is have them selected so that I can see all the locked ones to make sure I've unlocked all the right ones.

    thank you, margie

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Select Cells that are Locked (2003 sr2)

    Another way if you just want to confirm what is locked is to use conditional formatting (I assume you have no existing cond formatting used).

    Add this function to a vb module:

    <pre>Option Explicit
    Function CellLocked(rng As Range)
    CellLocked = rng.Cells(1).Locked
    End Function</pre>


    Then select all the cells in the sheet
    Select "Formula is"
    =celllocked(A1)
    <format...>
    Patterns(tab)
    Select the color of your choice to mark them
    <ok><ok>

    Now the locked cells will be colored. You can add the cond format to any range of cells. You can then just delete the cond formatting when done and you won't have affected any explicit formatting you already had added to the cells...

    Steve

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Cells that are Locked (2003 sr2)

    It basically runs through each cell in the range, if it is locked, it unlocks it, makes the change and then locks it again.

    If you want to reverse the changes copy and paste the code and call it Sub Unprotect() and change this line:

    Selection.Interior.ColorIndex = xlNone

    It has to be done this way as locked cells only work if the worksheet is protected. Therefore:

    Unprotect sheet
    Find locked cell
    Unlock cell
    lock cell
    Protect sheet
    Jerry

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Select Cells that are Locked (2003 sr2)

    The cell property can be locked/unlocked (or hidden/not hidden) whether the sheet is or is not protected.

    The locking prevents changes to the cell (and the hiding of formulas from the formula bar) only when the sheet is protected, but the property still exists in cells whose sheet is not protected.

    So any code to change the sheet just has to unprotect the sheet at the start and reprotect at the end. It could even be done manually before the code is run.

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Cells that are Locked (2003 sr2)

    Thanks Steve, I suppose that is the way (wrongly) I have always done it so it is a habit. Thanks for clarifying
    Jerry

  9. #9
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Select Cells that are Locked (2003 sr2)

    Steve,

    This would be perfect but the sheet has various conditional formats on it . Can I use it anyway and then reverse it?

    margie

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Cells that are Locked (2003 sr2)

    Hi margie,

    > Can I use it anyway and then reverse it?

    That would be rather complicated. Steve's macro in <post:=622,805>post 622,805</post:> higher up in this thread is probably the best solution - it doesn't modify the cells in any way, it just selects the locked cells.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Select Cells that are Locked (2003 sr2)

    You could use the original macro I posted to set an explicit formatting that is not being used explicitly (borders, font size, font color, etc) and then after changing the cells you could select all the cells and remove the explicit formatting you just added. This presumes that you have some formatting that could be changed and removed and would not affect any current formatting.

    Another way would be to "circle" the cells that are locked.
    <pre>Option Explicit
    Sub CircleLocked()
    Dim shp As Shape
    Dim rCell As Range
    For Each rCell In ActiveSheet.UsedRange
    If rCell.Locked Then
    With rCell
    Set shp = (ActiveSheet.Shapes.AddShape( _
    Type:=msoShapeOval, _
    Left:=.Left, _
    Top:=.Top, _
    Width:=.Width, _
    Height:=.Height))
    End With
    With shp
    .Fill.Visible = msoFalse
    .Line.ForeColor.RGB = vbRed
    End With
    End If
    Next
    Set shp = Nothing
    Set rCell = Nothing
    End Sub</pre>


    You can just use edit - goto-special - objects to select them all and then delete them (presuming you have no other objects on the sheet).
    or the line in a macro (or immediate window):

    <pre>ActiveSheet.DrawingObjects.Delete</pre>


    Steve

  12. #12
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Select Cells that are Locked (2003 sr2)

    Steve,

    This works a treat. Whilst liz is away I'd like to understand about macros in excel. Can you recommend and "idiot's guide to writing & understanding excel macros". either on line or a book. liz subscribes to Safari - the O'Reilly bookshelf service - so I should e able to find anything you suggest.

    many thanks, margie

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Cells that are Locked (2003 sr2)

    If you type excel macros in the search box on the Safari Books Online home site, you should find several books about programming in VBA.

    A good step to start learning macros is as follows:
    - Think of an easy action that could be automated (it doesn't need to be particularly useful the first time).
    - As an example, we'll take turning off the display of gridlines in a worksheet.
    - Start by creating a blank new workbook.
    - Select Tools | Macro | Record New Macro...
    - Provide a name, for example ToggleGridlines.
    - Leave the rest as is, for the moment, and click OK.
    - You'll see a small toolbar with two buttons, one of which looks like the "Stop" button on a VCR or DVD player.
    - Everything you do now in Excel will be recorded.
    - Select Tools | Options...
    - Activate the View tab if necessary.
    - Clear the check box labeled Gridlines in the lower left corner.
    - Click OK.
    - Click the "Stop Recording" button on the small toolbar.
    - Select Tools | Macro | Visual Basic Editor (or press Alt+F11).
    - Double click Module1 in the Explorer-like tree view on the left hand side.
    - You should see something like this:
    <code>
    Sub ToggleGridlines()
    '
    ' ToggleGridlines Macro
    ' Macro recorded on 12/31/06 by margie.
    '

    '
    ActiveWindow.DisplayGridlines = False
    End Sub
    </code>
    Anything after an apostrophe ' is a comment, the rest is code. Click in a word such as ActiveWindow or DisplayGridlines, then press F1 to get help on this word. This way you can learn something of the elements of the Visual Basic language for Excel. You can even copy code from the example(s) in the Help file and paste them back into your code module. For example: the code yoy now have will only turn off gridlines. The example in the online help for DisplayGridlines toggles them on and off. You can change the recorded line

    ActiveWindow.DisplayGridlines = False

    to
    ActiveWindow.DisplayGridlines = Not(ActiveWindow.DisplayGridlines)

    You can now test this by pressing F5 and switching back to Excel to see the result.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Select Cells that are Locked (2003 sr2)

    These are some of my recommendations
    Check example code generated on this site (and other sites) by others and ask questions as appropriate. I find the best way to learn is to do things.

    Get a book on excel/VB programming (John Walkenbachhas some good ones). I have not looked at all of them but can recommend his "Power Programming" book. It is available in XL2000, 2002, 2003. I have the XL2000 version and it is excellent.

    Here are some links (excel and Excel VBA), that I have especially found useful:

    Allen Wyatt posts online help obtaind from his weekly excel tips newsletter. There are links on that site to subscribe. there is a free version and a "premium" (you pay a nominal fee) for additional tips and a discount on some of his e-books and collections of tips. You can also contact him about joining his "daily excel tips" which is an email program where people get Q&A in their email and people discuss.

    Our own WMVP Jan Karel <!profile=Pieterse>Pieterse<!/profile> has an excellent series of Excel Articles

    Here is Chip Pearson Topic Index(if you go to the bottom, you can see the search). Good Excel "primers" on many subjects.

    John Walkenbach's spreadsheet page is Here

    Debra Dagliesh's "Contextures" has lots of tips and techniques, many code examples for using excel's built-in routines.

    Jon Peltier's Excel Charts has some impressive tricks for manipulating charts and making some very interesting ones. (not a lot of VB but still good tips and tricks)

    The Excel MVP Page is an excellent source of some Addins.

    VBA in particular
    Here is a link to some MS articles with links to How to use Visual Basic for Applications in Excel

    Some MS MVP articles:
    Creating a macro with no programming experience using the recorder [Using the recorder is an excellent way to learn about the VB object model. I often use the recorder to generate some example code and then generalize and simplify it. The coding is not the most efficient, usually (and there are some things like IFs and and LOOPing that it can not handle) but it can often get you 50-80% there.]

    Getting To Grips With VBA Basics In 15 Minutes

    Look at this List of documents by category under "spreadsheet", for plenty of good "articles".

    Hope this helps.

    Steve

  15. #15
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Select Cells that are Locked (2003 sr2)

    Tank you to all of you for the hints

    margie

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •