|
|
|
|
Junior Member
|
|
Apr 12, 2009, 03:00 PM
|
|
Lock cell that has data, unlock others
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"
|
|
|
Software Expert
|
|
Apr 12, 2009, 11:01 PM
|
|
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.
|
|
|
Junior Member
|
|
Apr 12, 2009, 11:33 PM
|
|
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
|
|
|
Software Expert
|
|
Apr 13, 2009, 12:04 AM
|
|
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.
|
|
|
Junior Member
|
|
Apr 13, 2009, 12:27 AM
|
|
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
|
|
|
Software Expert
|
|
Apr 13, 2009, 02:09 AM
|
|
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.
|
|
|
Junior Member
|
|
Apr 14, 2009, 01:09 AM
|
|
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
|
|
|
Junior Member
|
|
Apr 14, 2009, 01:17 AM
|
|
FOR JBeaucaire
One more thing
Don't vaste Your time with cells color.
|
|
|
Software Expert
|
|
Apr 14, 2009, 03:30 AM
|
|
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.
|
|
|
Junior Member
|
|
Apr 14, 2009, 10:14 AM
|
|
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
|
|
|
Software Expert
|
|
Apr 14, 2009, 11:53 PM
|
|
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.
|
|
|
Junior Member
|
|
Apr 15, 2009, 02:11 AM
|
|
Fantastic!
That's exactly what I needed.
Thanks very much, JBeaucaire, you'r the greatest.
:D:D:D:D:
|
|
|
New Member
|
|
Aug 31, 2009, 11:47 AM
|
|
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.
|
|
|
Software Expert
|
|
Aug 31, 2009, 04:12 PM
|
|
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 module ThisWorkbook
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
|
|
|
New Member
|
|
Sep 1, 2009, 12:02 AM
|
|
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.
|
|
|
Software Expert
|
|
Sep 1, 2009, 04:29 AM
|
|
Change ws. to ActiveSheet.
|
|
|
New Member
|
|
Sep 1, 2009, 08:34 AM
|
|
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.
|
|
|
Software Expert
|
|
Sep 2, 2009, 02:41 PM
|
|
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?
|
|
|
New Member
|
|
Sep 2, 2009, 09:33 PM
|
|
With Range("A2:J" & LR).SpecialCells(xlCellTypeConstants, 23)
.Locked = True
JB this looks to be the line.
|
|
|
Software Expert
|
|
Sep 2, 2009, 11:29 PM
|
|
So if you don't want them locked... try removing the one line referring to locking.
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
Thinkpad - NBRs Lock /Fn key - won't unlock
[ 6 Answers ]
I've tried unlocking the numbers lock on my IBM thinkpad and still, when I type, I get numbers instead of letters. Per a suggestion on this site I pressed the Fn key and nbrs key and that didn't work. Any other ideas?
Loading different data into a cell
[ 2 Answers ]
Hello everyone,
I'm looking for a way to load different data into a cell, or part of the page on the click of a link.
So I've got a load of links across the top of the page, and when each is clicked, different data is displayed (ideally more HTML) in the rest of the page, without the main...
View more questions
Search
|