Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   How can I have a cell lock automatically once a format is applied to it? (https://www.askmehelpdesk.com/showthread.php?t=393311)

  • Sep 3, 2009, 08:09 PM
    jmherbert2099
    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?
  • Sep 3, 2009, 09:20 PM
    JBeaucaire

    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.

  • All times are GMT -7. The time now is 11:02 PM.