How to Lock or password protect a range of data in excel using excel vba code

[harikrishna]

Hello All,

I am working Office 2010 environment, I need to get a macro that Locks or protects a range of data in a spread sheet. Initially I have data srarting from Range "A2" to "A10". Immedietly when i open the sheet the data in range A2 to A10 must be locked ( not allow me to do any changes) and now i will add data from row"A11 to "A15" and closes teh sheet after saving teh recently added data from A11 to A15.

Next time when i open teh same sheet, it should not allow m to do changes in the range From "A2 to A15" (Menas Including teh new data i have added"). Similarly everytime when adds new data and closes teh sheet , and next time when i open the sheet it sholud not allow me to do modifications from Range "A2 to the last row that contains the data".

Regards
Harikrishna Reddy

 

[dickindeventer]

Hello,

I made something to tinker with.
Click the link and try (Google Drive Example)

https://drive.google.com/file/d/0B5MMn040ahKkc2c3WE9mQjdEcms/view?usp=sharing

Greatings,

D i c k.

 

[dickindeventer]

Sorry, changed something
Link HERE
D I C K .

 

[harikrishna]

Sorry, changed something
Link HERE
D I C K .

Hello ****,

Thank you for your response, but i am unable to retrive the file you have mentioned using the above mentioned link, can you please send me the macro code?

 

[dickindeventer]

This code should be located in the codepart of sheet1
NOT in a module

put anything in te cells a1 and a2

when you change to sheet2 and then back, see what happens.

To reset click Alt-F8 and execute RESET

****.

Sub LockColumns()
Application.ScreenUpdating = True
With Application.Sheets(1)
    .Unprotect
    .Activate
    .Cells(1, 1).Select
    Do While Selection.Value <> ""                         'If selection is not empty
            Selection.EntireColumn.Interior.Color = 65535
            Selection.EntireColumn.Locked = True
            Selection.Offset(0, 1).Select
    Loop
    .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
End Sub




Sub Reset()
    Sheets("Blad1").Select
    Sheets("Blad1").Unprotect
    Cells.Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Locked = False
    Selection.FormulaHidden = False
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Sheets("Blad1").Range("A1").Select
End Sub


Private Sub Worksheet_Activate()
    LockColumns
End Sub

 

[harikrishna]

This code should be located in the codepart of sheet1
NOT in a module

put anything in te cells a1 and a2

when you change to sheet2 and then back, see what happens.

To reset click Alt-F8 and execute RESET

****.

Sub LockColumns()
Application.ScreenUpdating = True
With Application.Sheets(1)
    .Unprotect
    .Activate
    .Cells(1, 1).Select
    Do While Selection.Value <> ""                         'If selection is not empty
            Selection.EntireColumn.Interior.Color = 65535
            Selection.EntireColumn.Locked = True
            Selection.Offset(0, 1).Select
    Loop
    .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
End Sub




Sub Reset()
    Sheets("Blad1").Select
    Sheets("Blad1").Unprotect
    Cells.Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Locked = False
    Selection.FormulaHidden = False
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Sheets("Blad1").Range("A1").Select
End Sub


Private Sub Worksheet_Activate()
    LockColumns
End Sub

_---------------------------------------------------------------------------------------------------

Hello ****,

I tried the above code, but it is coloring the whole sheet 1, but not the cells with data. And i want to present my problem more clearly....As below

1. As i opens this sheet every time the data from Row # 2 to the last row containg the data must locked. (In this case- From teh row 2 to row 4 has to be locked).

2.Row1 will always be empty, bcas i need to enter the data in that row and click on "Add data" command button. Then teh data will be added at the last empty available empty row in teh sheet. In teh below i have entered teh new data "Walsh, 111" and click on the Add data button teh data will be added in the last empty row.

Before adding the data

Walsh	111	Add Data
Name	Number
Hari	11
Raj	99

<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>
</TBODY>

AFter Adding the Data

		Add Data
Name	Number
Hari	11
Raj	99
Walsh	111

<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>
</TBODY>

3. Now the data from Row 2 to row that contains the data i.e. Row # 5 has to be locked. I mean only the cells with data has to be locked always, even every time when i opens the sheet. (I mean immedietly when i opens the work sheet/workbook the data from Row to last row containing data has to be locked)...

I have macro now for adding the data at the bottom , but i need to have a macro only for locking the cells that contains the data always and that macro need to lock all the available data from row #2)

And if I want to edit any changes in the data from row # 2 it should ask me for a password.


Regards
Harikrishna

 

[dickindeventer]

Hello Harikrishna,

I think it's more logical to protect the data that has been entered right away.
When your macro has done it's job to put the data at the bottom of the table you can use the code below to protect the first and second column of the rows that have been filled.
The First column changes to and has to be protected also.
The cells containing Name and Number have to be named accordingly. The code will determine the range by starting with the cell under the named range (Name) and extend it to the last continuously filled cells below and then protect it.

So Everything stays protected until your code, including mine, will change the sheet and the protection. The only cells affected by the code are the cells below Name and Number. The rest of your protected and unprotected cells will keep unchanged.

Sub LockRange()
    With Application.Sheets(1)
        .Unprotect Password:="Password"
        Range(Range("Name").Offset(0, 1), Selection.End(xlDown)).Locked = True
        Range(Range("Number").Offset(0, 1), Selection.End(xlDown)).Locked = True
        .Protect Password:="Password"
    End With
End Sub

The columns cannot have blanks because the xlenddown will not work, but your code prevents this from happening.

When you rightclick the tab to unprotect your sheet you will have to enter the same password that is in the code. This enables you to edit any cell in the sheet. Do not forget to protect the sheet before saving.

You will have to protect your code for opening by a password because everyone, able to type Alt-F11 will be able to read your password in de code that is used.

Well, it was interesting to figure this out. I hope it helps. And do not forget the most important feature in the VBA editor being F1, the mother of all your answers.

Hope to read your reaction to this all.

D I C K .

 

[dickindeventer]

Sorry still al little mistake in the code.
Corrected below

Sub LockRange()
    With Application.Sheets(1)
        .Unprotect Password:="Password"
        Range(Range("Name").Offset(1, 0), Range("Name").Offset(1, 0).End(xlDown)).Locked = True
        Range(Range("Number").Offset(1, 0), Range("Number").Offset(1, 0).End(xlDown)).Locked = True
        .Protect Password:="Password"
    End With
End Sub