Articles

Protec cells with data

October 3, 2010 at 16:52:24
Specs: Windows XP

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 secret

6 - 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 Sub

When 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



See More: Protec cells with data

Report •


#1
October 3, 2010 at 21:39:39

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


Report •

#2
October 4, 2010 at 15:00:51

Perfect is that I was looking for!

Thanks (at first) and for this code. It is very useful and practical in my work.


Report •

#3
October 6, 2010 at 17:27:56

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


Report •

Related Solutions

#4
October 6, 2010 at 17:57:20

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


Report •

#5
October 9, 2010 at 18:24:21

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 Sub

what's wrong


Report •

#6
October 10, 2010 at 07:21:06

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.


Report •

#7
October 10, 2010 at 16:34:43

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.


Report •

#8
October 10, 2010 at 20:01:05

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.


Report •


Ask Question