Ask Experts Questions for FREE Help !
Ask
    jmherbert2099's Avatar
    jmherbert2099 Posts: 1, Reputation: 1
    New Member
     
    #1

    Sep 3, 2009, 08:09 PM
    How can I have a cell lock automatically once a format is applied to it?
    I have an spreadsheet that I am working on. I have set up Macros buttons that when selected will changed the color(format) of the cell to correspond however once the cell is changed(formatted) I want to cell to lock automatically so that it can't be changed? Is this an available option for excel?
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    Sep 3, 2009, 09:20 PM

    To do this will require more VBA.

    1. First, unlock the cells you want editable
      • highlight all the cells
      • Press Ctrl-1 to open the format window
      • Click the Protection tab
      • Uncheck the [ ] Locked option
    2. Add a sheet-level macro to lock cells as they are valued
      • Right-click on the sheet tab and select VIEW CODE
      • Paste in this macro:
        Code:
        Option Explicit
        Const pw As String = "password"
        
        Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cell As Range
        Me.Unprotect pw
        
            For Each cell In Target
                If cell.Value <> "" Then cell.Locked = True
            Next cell
        
        Me.Protect pw
        End Sub
      • Press Alt-Q to close the editor
      • Save your sheet


    Of course, set your own protection password in that constant.

    There are many many ways to do this, this just being one.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Lock cell that has data, unlock others [ 21 Answers ]

How to lock only cell that has data, and unlock others while they no data. I need solution with: Data-Validation-Settings-Custom-Formula. Thanks! Sorry for my "english"

Solar cell & photo cell [ 6 Answers ]

What is the different between solar cell & photo cell ( photo electric effect exp... ) ?

Air lock or hammer lock [ 3 Answers ]

I was just wondering how I can rememdy hammer lock or airlock? I think that is what it is. I just bought this house that has been sitting for 3 months unoccuppied. I connected my washer and when the washer starts to cycle by pulling water to the unit, I hear this bang evertime it purges water. ...

ITIN applied for [ 1 Answers ]

Can I employ someone whose ITIN is under application... applied for?


View more questions Search