I need help! I have this code, but is incomplete for my work
His process will allow the user to enter data in Column A just once:
1 - Select Column A
2 - Click Format...Cells...Protection Tab
3 - Uncheck the Locked option
4 - Click Tools...Protection...Protect Sheet
5 - Set Password to the word secret6 - Click the Sheet tab for the sheet and paste this code into the window that opens:
Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was to Column A
If Target.Column = 1 Then
'If yes, Unprotect sheet
Sheets("Sheet1").Unprotect Password:="secret"
'Locked changed cell
Target.Locked = True
'Protect Sheet
Sheets("Sheet1").Protect Password:="secret"
End If
End SubWhen the user enters data in Column A, the code will Unprotect the sheet, Lock the cell that was changed and Protect the sheet again.
I need protect in column A to F , and protect only after giving the save.
Thanks
Boy, that code looks familiar...I wonder who wrote it. ;-) Let me make sure I understand your requirements:
Is this what you want to do?
1 - Columns A - F are available for data input.
2 - Users can enter data in any cells that do not contain data, and even go back and change that data, as long as they don't save the workbook.
3 - Once the workbook is saved, any cells that have data in them, and only cells that have data in them should be locked, preventing any changes from being made.
4 - Data cannot be entered into Columns G or beyond. In other words those cells are always locked.
Is that correct?
If so...
Start by creating a backup copy of your workbook to test this in.
1 - Select Columns A - F
2 - Unlock those cells.
3 - Protect the sheet, Password = Secret
4 - Open the VBA editor
5 - Double-Click the ThisWorkbook module
6 - Paste this code in the pane that opens.Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) With Columns("A:F") Set c = .Find("*") Sheets("Sheet1").Unprotect Password:="secret" firstAddress = c.Address Do c.Locked = True Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End With Sheets("Sheet1").Protect Password:="secret" End Sub
Perfect is that I was looking for! Thanks (at first) and for this code. It is very useful and practical in my work.
Hello again, Works perfectly in a small file
I tried to apply in a workbook with 7 sheets and something is wrong.
Ex: I want to apply to the sheet named "results", change "sheet1" for "results" and does not work.Can I apply the code to each sheet? (Each with variations in number of columns)
Thanks
If each sheet needs to have a different range protected, you'll need to put each of those ranges in the code. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Code for Results sheet, Columns A - F With Sheets("Results").Columns("A:F") Set c = .Find("*") Sheets("Results").Unprotect Password:="secret" firstAddress = c.Address Do c.Locked = True Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress Sheets("Results").Protect Password:="secret" End With 'Code for Input sheet, Columns Q - S With Sheets("Input").Columns("Q:S") Set c = .Find("*") Sheets("Input").Unprotect Password:="secret" firstAddress = c.Address Do c.Locked = True Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress Sheets("Input").Protect Password:="secret" End With 'Repeat as required End Sub
More help please! So, It's complicated. I put code on my sheet and It's not working.
I tried a simple sheet and also does not work.Run-time error ' 91'
Run-time error '1004'Code ex:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Code for Results sheet3, Columns A - F
With Sheets("Sheet3").Columns("A:F")
Set c = .Find("*")
Sheets("Sheet3").Unprotect Password:="secret"
firstAddress = c.Address
Do
c.Locked = True
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Sheets("Sheet3").Protect Password:="secret"
End With'Code for Results sheet1, Columns G - H
With Sheets("Sheet1").Columns("G:H")
Set c = .Find("*")
Sheets("Sheet1").Unprotect Password:="secret"
firstAddress = c.Address
Do
c.Locked = True
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Sheets("Sheet1").Protect Password:="secret"
End With'Code for Results sheet2, Columns I - J
With Sheets("Sheet2").Columns("I:J")
Set c = .Find("*")
Sheets("Sheet2").Unprotect Password:="secret"
firstAddress = c.Address
Do
c.Locked = True
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Sheets("Sheet2").Protect Password:="secret"
End With
End Subwhat's wrong
I am assuming that there was text associated with those error numbers. Since I can't see your spreadsheet from where I'm sitting, I obviously can't see the text either. Perhaps it would help if you posted the text also.
That said, I can produce an Run time error '91: Object variable or With block variable not set if there is no data in the ranges you are trying to protect. However, I'm not going to suggest a fix until I know the text of the error you are getting.
I am unable to produce a 1004 error. Error 1004 can be the result of a large number of issues. Again, unless I know the text of the error message, I can't offer any suggestions. Even then, since I can't product the error, I may not be able to help.
Oops sorry - here is the error message, Run time error '91: Object variable or With block variable not set
But..... in the range there are a lot of data.For 1004 error: Unable to set the locked property of the range class
I think this is for my mistake when put the range protected.Thanks for your help again.
Again, since I can't see you workbook from where I am sitting, it's really hard for me to diagnose the errors. I'm not having any trouble with the code in any workbook I set up, so there is something within your workbook that the code I offered doesn't like.
If you can send me the workbook - with all confidential data changed/removed - I can take a look at it. If that's possible, let me know and I'll send you an email address via Private Message. Do not post your email address in this open forum.
