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

H

harikrishna

New Member
Joined
Aug 19, 2011
Messages
38
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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

****.

Code: 
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
 
Last edited:
Upvote 0
dickindeventer said:
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

****.

Code: 
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
Click to expand...

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

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 [TABLE="width: 192"]
<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: yellow"]Walsh[/TD]
[TD="class: xl65, width: 64, bgcolor: yellow, align: right"]111[/TD]
[TD="class: xl66, width: 64, bgcolor: #00b050"]Add Data[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Name[/TD]
[TD="class: xl67, bgcolor: transparent"]Number[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Hari[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Raj[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]99[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]


AFter Adding the Data
[TABLE="width: 192"]
<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: yellow"] [/TD]
[TD="class: xl65, width: 64, bgcolor: yellow"] [/TD]
[TD="class: xl66, width: 64, bgcolor: #00b050"]Add Data[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Name[/TD]
[TD="class: xl67, bgcolor: transparent"]Number[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Hari[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Raj[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]99[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Walsh[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]111[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]


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
 
Upvote 0
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.

Code: 
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 .
 
Upvote 0
Sorry still al little mistake in the code.
Corrected below

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA help - Locking & unlocking on certain times of day
2
Replies
14
Views
280
dmt32
D
FloatedWheat
  • Question Question
Protect Sheet with Different User Permissions
Replies
3
Views
497
iggydarsa
iggydarsa
GMC The Macro Man
  • Question Question
Sorting a table in a protected sheet when some cells are locked -MACRO needed
Replies
3
Views
379
RoryA
RoryA
A
  • Question Question
How do I lock a worksheet and stop data entry after a certain date and time?
Replies
1
Views
512
Jeffrey Mahoney
Jeffrey Mahoney
M
  • Question Question
Protecting Sheet from users but permitting Power Automate
Replies
1
Views
299
ExcelToDAX
E

Forum statistics

Threads
1,223,670
Messages
6,173,717
Members
452,528
Latest member
ThomasE

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top