Auto lock cells after data entry when file saved...

[cjcass]

Hi, I have a table range A5:AA1000 in a data entry worksheet… the file will be sat on a shared drive and will be accessed by different people throughout the day. I want the data entry worksheet to be locked and protected so people can only access and enter data into cells J5:N1000 in the table. When they have entered data into chosen cells and then saved the file, I want the cells they have entered data into to be locked so the next person opening the file can’t access them and make any changes.</SPAN>

Is there some VB code I can input into the worksheet that will execute this locking out of the cells when the user saves the file? Am using Excel 2007.</SPAN>

Many thanks indeed for your help…</SPAN>

 

[Techman01]

How do I modify this code so that I can apply the same auto-locking feature to another set of cells in the same worksheet but different tab?

 

[Faisal Parekh]

Hi Jaafar

Can you please guide me as to after pressing Alt+f11, where I need to paste these two codes?

 

[Faisal Parekh]

Give the range A5:AA1000 the Name of InputRange and put the following code in the Thisworkbook module :

Option Explicit

Private bRangeEdited As Boolean
Private WithEvents ws As Worksheet


Private Sub Workbook_Open()
    Set ws = Range("InputRange").Parent
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim sMSG As String
    
    sMSG = "saving the workbook will lock the cells you have entered data into." & vbLf
    sMSG = sMSG & "Do you want to go ahead ?"
    If Not bRangeEdited Then GoTo Xit
    If Not Me.ReadOnly Then
        With Range("InputRange")
            If MsgBox(sMSG, vbExclamation + vbYesNo) = vbNo Then
                Cancel = True
                GoTo Xit
            End If
            .Parent.Unprotect "password"
            If .SpecialCells(xlCellTypeBlanks).Address <> .Address Then
                .SpecialCells(xlCellTypeConstants).Locked = True
                bRangeEdited = False
            End If
            .Parent.Protect "password"
        End With
    End If
Xit:
End Sub

Private Sub ws_Change(ByVal Target As Range)
    If Not Intersect(Range("InputRange"), Target) Is Nothing Then
        bRangeEdited = True
    End If
End Sub

Now save and close the workbook so it is ready and functional next time you open it.

I assumed the following :
1- Workbook is not shared.
2- The worksheet is protected with the password "password" - change as required.

Hi Jaafar

Can you please guide me as to after pressing Alt+f11, where I need to paste these two codes?

 

[Daniel Tou]

How to use this code without a specified range ?

I used the code :

Private Sub Workbook_Open()
Dim wsh As Worksheet
Dim rng As Range
Set wsh = Me.Worksheets("Sheet1")
On Error Resume Next
wsh.Unprotect Password:="Secret"
wsh.Cells.Locked = False
Set rng = wsh.UsedRange.SpecialCells(xlCellTypeConstants)
If Not rng Is Nothing Then
rng.Locked = True
End If
Set rng = wsh.UsedRange.SpecialCells(xlCellTypeFormulas)
If Not rng Is Nothing Then
rng.Locked = True
End If
wsh.Protect Password:="Secret"
End Sub

The error is

"You cannot use this command on a protected sheet" , and when press debug this line is highlited in yellow ; "RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count"
I have a file whit a user form for introducing data and i want that other users to add data via user form , but not edit the data entered.

Please save me whit the right code.