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"

Tell me a little more about your process. Are you asking how to have a blank cell ready for manual input of data, and once data is inserted manually, the cell becomes locked and cannot be changed?

This would require a macro to be running on your sheet basically locking cells as data is inserted. You could have this occurring on only certain rows/columns or even ranges of cells. Is this acceptable?

If so, mock up a sheet showing what you have in mind. Be sure to clearly define which cells should receive this treatment, the name of the sheet or sheets that this should occur on, or clarify that on a sample workbook uploaded here.

Click on GO ADVANCED and use the paperclip icon to attach a workbook.

Quote:

---

Originally Posted by jbeaucaire

tell me a little more about your process. Are you asking how to have a blank cell ready for manual input of data, and once data is inserted manually, ONLY THOSE CELLS becomes locked and cannot be changed?

this would require a macro to be running on your sheet basically locking cells as data is inserted. You could have this occurring on only certain rows/columns or even ranges of cells. Is this acceptable?

If so, go ahead and mock up a sheet showing what you have in mind. Be sure to clearly define which cells should receive this treatment, the name of the sheet or sheets that this should occur on, or clarify that on a sample workbook uploaded here.

Click on go advanced and use the paperclip icon to attach a workbook.

---

Try with my workbook

Thanks again

Please explain the process for entering data in a row. At what moment does the row become locked? What should trigger that moment? Can they edit the data anywhere in the row they are currently working on? Please think this through and decide when/how you want this "lock" to trigger.

Quote:

---

Originally Posted by JBeaucaire

Please explain the process for entering data in a row. At what moment does the row become locked? What should trigger that moment? Can they edit the data anywhere in the row they are currently working on? Please think this through and decide when/how you want this "lock" to trigger.

---

Sorry for isufficiently data.
So, What should trigger that moment? - CLICK TO SAVE SPREADSHEET!
Can they edit the data anywhere in the row they are currently working on?
- YES, COLUMNS N, O, P, Q, R, S

Much torture with me, You're very patient man.

Thanks

All right, I added a couple of macros in the ThisWorkbook module. Whenever you try to save this sheet, it will warn you that doing so will make your current changes permanent and give a chance to abort.

If you say "yes", it will scroll through the sheets 1-20 and color the finished rows blue. I added a "count" column out to the right in column J to accomplish that test.

If you look in the macro here, you will see the line that protects the sheets. There is another version of that line commented out directly below... it shows you how to add a PASSWORD to the protection so the users cannot simply unprotect the sheet.

Code:

---

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel As Boolean)
    a = MsgBox("Saving this workbook will lock the entries completed so far and you will no longer be able to edit them. Do you really want to save the workbook now?", vbYesNo)
    If a = vbNo Then
        Cancel = True
    Else
        Call UpdateAndSaveSheet
    End If
End Sub

Private Sub UpdateAndSaveSheet()
Dim lastrow As Long, i As Integer, ws As Worksheet
Application.ScreenUpdating = False
Application.EnableEvents = False

For Each ws In Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", _
                            "9", "10", "11", "12", "13", "14", "15", _
                            "16", "17", "18", "19", "20"))  'extend this array to include all your sheets by name
    ws.Activate
    ws.Protect UserInterfaceOnly:=True
'    ws.Protect Password:="1234", UserInterfaceOnly:=True
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
   
    For i = 9 To lastrow
        If Cells(i, 10).Value = 7 Then
            Rows(i).Locked = True
            Range(Cells(i, 2), Cells(i, 8)).Interior.ColorIndex = 37
        End If
    Next i

    If lastrow < 23 Then GoTo FinishLine
Next ws

FinishLine:
    ThisWorkbook.Save
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

---

Oh, and I had to remove all those merged cells, those wreak havoc on macros, and they were mostly unnecessary for your layout.

Thanks for Your time, but I must ask You one more thing.


So, currently, with Your macro, when I enter data into first row, save workbook,exit, and reopen, ALL cells are locked and no data entry are allowed.

What I need?

When I enter data into first row, save workbook,exit, and reopen, all cells EXCEPT first row must be unlocked, when I enter data into second row, save workbook,exit, and reopen, all cells EXCEPT first and second rows must be unlocked... etc.


Thanks

FOR JBeaucaire

One more thing

Don't vaste Your time with cells color.

No, that's not what's happening. The sheets are locked after you save the first time, but all the yellow cells are still unlocked. Only the blue cells are truly locked and uneditable.

The different color is sort of critical for seeing clearly which cells can be edited and which cannot.

I'm sorry, in the sheet I posted earlier I did notice I had accidentally left some of the yellow cells locked from earlier playing with the macro. That's fixed here on this version.

The sheets 1-20 are protected, but all yellow cells are still editable until after a full row is entered and saved, then it becomes uneditable. All other yellow cells are still accessible without unprotecting.

Protecting a workbook is the only way to keep users from mucking with cells... even things like fancy conditional formatting tricks and validation restrictions can be easily broken or circumvented, much more easily than a protected worksheet.

Quote:

---

Originally Posted by JBeaucaire

No, that's not what's happening. The sheets are locked after you save the first time, but all the yellow cells are still unlocked. Only the blue cells are truly locked and uneditable.

The different color is sort of critical for seeing clearly which cells can be edited and which cannot.

I'm sorry, in the sheet I posted earlier I did notice I had accidentally left some of the yellow cells locked from earlier playing with the macro. That's fixed here on this version.

The sheets 1-20 are protected, but all yellow cells are still editable until after a full row is entered and saved, then it becomes uneditable. All other yellow cells are still accessible without unprotecting.

Protecting a workbook is the only way to keep users from mucking with cells...even things like fancy conditional formatting tricks and validation restrictions can be easily broken or circumvented, much more easily than a protected worksheet.

---

Work excellent, BUT... (there's always some BUT)

"until after a FULL row is entered and saved"

I need one more thing

Until after a FULL OR NON-FULL row is entered and saved.


Thanks

OK, I was sure you had said previously that users should be able to edit incomplete rows, but I guess not.

All right, I removed the part that was checking to make sure all 7 cells were filled.

This version will lock individual cells with data in them leaving empty cells still editable.

Code:

---

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel As Boolean)
    a = MsgBox("Saving this workbook will lock the entries completed so far and you will no longer be able to edit them. Do you really want to save the workbook now?", vbYesNo)
    If a = vbNo Then
        Cancel = True
    Else
        Call UpdateAndSaveSheet
    End If
End Sub

Private Sub UpdateAndSaveSheet()
Dim lastrow As Long, i As Integer, ws As Worksheet
Application.ScreenUpdating = False
Application.EnableEvents = False

For Each ws In Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", _
                            "9", "10", "11", "12", "13", "14", "15", _
                            "16", "17", "18", "19", "20"))  'extend this array to include all your sheets by name
    ws.Activate
    ws.Protect UserInterfaceOnly:=True
'    ws.Protect Password:="1234", UserInterfaceOnly:=True
    On Error Resume Next
    With Range("B9:H23").SpecialCells(xlCellTypeConstants, 23)
        .Locked = True
        .Interior.ColorIndex = 37
    End With
Next ws

    ThisWorkbook.Save
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

---

BTW, these sheets are protected, but currently not password protected, so any user can simply choose TOOLS > PROTECTION > UNPROTECT SHEET to unlock the sheet. In my opinion that makes this attempt at security pointless.

To make it truly secure, you should activate the RED line in the code above by taking out the apostrophe and deactivate/remove the line above. Then put in your password. This will truly protect your sheets from users fiddling.

And since some users are VBA savvy, you should hide the code as well, probably with the same password. Right-Click on the VBAProject in the left panel of the VBEditor and select VBAProject Properties... use the Protection tab to add a password. Next time you open the sheet all the code will be hidden and you'll need the password to view/edit it.

Fantastic!
That's exactly what I needed.
Thanks very much, JBeaucaire, you'r the greatest.
:D:D:D:D:

JBeaucaire,

You truly are the greatest. I have the same problem as Pajoooo. I have an excel sheet (only one in the workbook) with 10 columns. A1 - J1 are the headers and from A2 we start to input data. Like Pajoo, I want once data is input\, and you hit save for the cell to be locked, and for the rest to be editable. Please can you modify the code you wrote for Pajoooo. As well I already have data up to row 213 in my worksheet. I need to lock all these.

Thanks in advance.

How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. In the VBAProject, doubleclick on the moduleThisWorkbook
4. Copy and Paste in your code (given below)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. When you attempt to save your sheet, it will ask if you want to make your current date permanent, then it will lock all the cells with values in them on Sheet1.

Code:

---

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If MsgBox("Saving this workbook will lock the entries completed so far and you will no longer be able to edit them. Do you really want to save the workbook now?", vbYesNo) = vbNo Then
        Cancel = True
    Else
        Call UpdateAndSaveSheet
    End If
End Sub

Private Sub UpdateAndSaveSheet()
Dim LR As Long
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False

Sheets("Sheet1").Activate
LR = Range("A1").SpecialCells(xlCellTypeLastCell).Row

ws.Protect UserInterfaceOnly:=True
'ws.Protect Password:="1234", UserInterfaceOnly:=True
   
    With Range("A2:J" & LR).SpecialCells(xlCellTypeConstants, 23)
        .Locked = True
        .Interior.ColorIndex = 37
    End With

    ThisWorkbook.Save
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

---

Thanks JB, I truly appreciate it.

However when I tried to run its giving me an error.It says was not defined and when I try to define was as Worksheet on the same line as LR as Long. I get a syntax error on the line Private Sub UpdateAndSaveSheet().

Please help.

Change ws. to ActiveSheet.

JB thanks again. This seems to work, all the filled cells turn blue and unfilled yellow, but the yellow ones are also uneditable, I have to unprotect sheet to edit them.

I need the unfilled cells to be editable without having to unprotect the sheet.

TEACHER'S HAT = ON:

When you look at the macro, does any particular line of the code jump out to you as the line that is locking the cells?

With Range("A2:J" & LR).SpecialCells(xlCellTypeConstants, 23)
.Locked = True

JB this looks to be the line.

So if you don't want them locked... try removing the one line referring to locking.