Locking Cells

[chobe]

I did review some examples your members have already provided however I have a slight twist.

I have multiple users adding to a spreadsheet from which data is later copied to a master sheet. I need to lock ONLY cells which contain data when user chooses to save spreadsheet or saves speadsheet on exit. If the cells do not contain data, they should remain unlocked. Thanks in advance for your help.

 

[jymoua]

You can use Activesheet.ScrollArea="Rangewithdata"

 

[DonkeyOte]

You could use the save event, this isn't thoroughly tested...
Note this would sit within ThisWorkbook module in VBE and would fire as and when file is saved iterating each worksheet in the file. Probably a more elegant way to do this though.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Dim pwd As String: pwd = "Trustno1"
Dim rng, rng1, rng2 As Range
For Each ws In ActiveWorkbook.Worksheets
    ws.Select
    ws.Unprotect Password:=pwd
    ws.Cells.Locked = False
    On Error Resume Next
    Set rng1 = ws.Cells.SpecialCells(xlCellTypeConstants)
    Set rng2 = ws.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    Select Case rng2 Is Nothing
        Case True
            If IsEmpty(rng1) = False Then Set rng = rng1
        Case False
            If IsEmpty(rng1) Then
                Set rng = rng2
            Else
                Set rng = Union(rng1, rng2)
            End If
    End Select
    If IsEmpty(rng) = False Then rng.Locked = True
    ws.Protect Password:=pwd
    Set rng1 = Nothing
    Set rng2 = Nothing
Next ws
End Sub

HTH

 

[DonkeyOte]

Just a note that the line

ws.Select

is not required and should be removed... a legacy from testing!

 

[chobe]

You could use the save event, this isn't thoroughly tested...
Note this would sit within ThisWorkbook module in VBE and would fire as and when file is saved iterating each worksheet in the file. Probably a more elegant way to do this though.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Dim pwd As String: pwd = "Trustno1"
Dim rng, rng1, rng2 As Range
For Each ws In ActiveWorkbook.Worksheets
    ws.Select
    ws.Unprotect Password:=pwd
    ws.Cells.Locked = False
    On Error Resume Next
    Set rng1 = ws.Cells.SpecialCells(xlCellTypeConstants)
    Set rng2 = ws.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    Select Case rng2 Is Nothing
        Case True
            If IsEmpty(rng1) = False Then Set rng = rng1
        Case False
            If IsEmpty(rng1) Then
                Set rng = rng2
            Else
                Set rng = Union(rng1, rng2)
            End If
    End Select
    If IsEmpty(rng) = False Then rng.Locked = True
    ws.Protect Password:=pwd
    Set rng1 = Nothing
    Set rng2 = Nothing
Next ws
End Sub

HTH

lasw10;

Worked perfectly....However, since my original layout may now need to restrict locking changed cells to specific columns or range only. Other columns, that do contain new data, need to remain unlocked so they can be updated from time to time. Can you again assist?

Thanks in advance....

 

[DonkeyOte]

Post locking of union range and pre-re-protecting the sheet I would then unlock those cells that should remain unlocked at all times, eg - to keep Col A unlocked at all times something like the below perhaps:

    ...
    If IsEmpty(rng) = False Then rng.Locked = True
    Columns(1).Locked = False 
    ws.Protect Password:=pwd
    ...

I suspect it will be quicker to lock first (as before) as it's easy to identify the range required using this method -- then unlock "special" cases thereafter before reprotecting the sheet.

 

[chobe]

Again, worked perfectly, however one final refinement and I can write this off as complete. How to I restrict action of this code to one specific sheet instead of entire workbook?

 

[DonkeyOte]

After

For Each ws in ActiveWorkbook.Worksheets

You could simply add a line

If UCase(ws.Name) <> "SHEET1" Then Exit For

Where SHEET1 is the sheet name you wish to run the code on (note it must be written in Upper Case)

There are other ways of doing this but the above is probably the quickest/simplest way to do it from a non-coders perspective

Hope that helps.

 

[chobe]

Rather than lock all cells on a given sheet that contain constants or formulas, I need to locks cells in columns D and E beginning in row 7 that contain data only. For example, col D allows user to provide a project name, col E a employee name both of which are selected from validation lists. Columns G thru AA are fields in which users enter projected hours employ will spend on selected project and will change frequently. I want to lock the project name and employee columns, but here's a new twist, only if there are any hours entered in columns G thru AA. Manager may select a project, assign a employee, but later decide to change one or the other. Once hours have been projected, changes should not be allowed unless all hours are deleted which more than likely will not occur because projections for past months cannot be changed.

 

[chobe]

Post locking of union range and pre-re-protecting the sheet I would then unlock those cells that should remain unlocked at all times, eg - to keep Col A unlocked at all times something like the below perhaps:

    ...
    If IsEmpty(rng) = False Then rng.Locked = True
    Columns(1).Locked = False 
    ws.Protect Password:=pwd
    ...

I suspect it will be quicker to lock first (as before) as it's easy to identify the range required using this method -- then unlock "special" cases thereafter before reprotecting the sheet.

DonkeyOte, rather than lock all cells on a given sheet that contain constants or formulas, or just a entire column, I need to locks cells in columns D and E beginning in row 7 that contain data only. For example, col D allows user to provide a project name, col E a employee name both of which are selected from validation lists. Columns G thru CC are fields in which users enter projected hours employees will spend on selected project. These fields need to be updated frequently.

I want to lock the project name and employee columns, but here's a new twist in addition to limiting code action to a range of cells: I want to lock these cells only if there are any hours entered in columns G thru CC. Reason; Manager may select a project, assign a employee, but later decide to change one or the other before hours are recorded. Once hours have been projected, changes should not be allowed unless all hours are deleted which more than likely will not occur because projections for past months cannot be changed.