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.

 

[chobe]

Please forgive multiple post I am having real problems with our internet security interferring with my posts.....

 

[DonkeyOte]

Regards extra post, not a problem.

Regards your actual question... personally my own preference would be to use a helper cell in each row which would hold a flag to determine as to whether or not a row was deemed "set" or not.

For ex - if we assume that by this:

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.

you effectively mean:

if any hours are entered in G:CC (as opposed to every cell in G:CC has an entry) AND BOTH D (project name) and E (resource name) have also been entered then we deem that row as "set" -- ie neither D nor E can be changed from that point forth

then in that case I personally would add a formula to help me identify this "set" rows quickly in the VBA.... let's say column CD is free... in each row I would have the following formula:

CD2: =IF(AND(COUNTA($D2:$E2)=2,COUNT($G2:$CC2)>0),1,"x")

This will thus return a 1 if both D & E are filled in and some hours have been added in columns G:CC -- if not an "x" is returned... it is important that the value for set is numeric (1) and for unset is text ("x")

Once the above is setup you can adjust your code such that it looks in column CD for all numeric values and then offset the resulting range such that it looks at D:E and then lock those cells, eg:

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
'iterate worksheets in file
For Each ws In ActiveWorkbook.Worksheets
    Select Case UCase(ws.Name)
        Case "SHEET1"
            'activate sheet
            ws.Select
            'unprotect
            ws.Unprotect Password:=pwd
            'unlock
            ws.Cells.Locked = False
            'define rows which need to be locked by looking in column CD for numerics
            On Error Resume Next
            Set rng = ws.Range(Cells(2, 82), Cells(Rows.Count, 82)).SpecialCells(xlCellTypeFormulas, xlNumbers)
            With rng
                .Offset(0, -78).Locked = True
                .Offset(0, -77).Locked = True
            End With
            On Error GoTo 0
            'protect sheet
            ws.Protect Password:=pwd
            'clear ranges
            Set rng = Nothing
        Case Else
            'do nothing
    End Select
Next ws
End Sub