Solved: Password Protecting particular rows in excel

[tools]

Is there a way to password protect certain rows in an excel sheet.
hmm for eg when I open my excel sheet the user is displayed with few rows and I dont want the user to delete those rows.

[mikerickson]

You could hide those rows, unlock the visible cells, and password protect the worksheet.

[tools]

The rows should be visible to the user so cant hide them and locking the cells works to a certain extent but the user can still unprotect the sheet and work.

I want to allow the user to add new rows but dont want him to delete the existing rows.

[Simon Lloyd]

You could try adapting this kb entry

[Ken Puls]

I'm not quite sure I follow the issue here...

By default, all cells in the Excel sheet are locked, which takes effect when you protect the sheet. So why can't you select all the cells on the sheet, unlock them, then lock just the rows you want protected? At that point, when you protect the sheet, your critical rows are both locked and visible, and the rest of the sheet is editable.

In addition, depending on your version of Excel, you can deal with the protection in more granular levels. In Excel 2003 (at a minimum) you can allow the user to use autofilters and insert rows, for example. I'm not sure which version of Excel you are using, but you may not need to reach to VBA for this.

[tools]

The rows displayed in the excel sheet are obtained dynamically so vba is the only option available for locking the rows.

I am using excel 2003.

[Ken Puls]

Okay, fair enough.

If you are comfortable enough with using Excel's default worksheet level password, you could do what I suggested as follows:

[vba] Dim pw As String

pw = "Password"

With ActiveSheet
.Unprotect pw
.Cells.Locked = False
.Rows("1:6").Cells.Locked = True

.Protect Password:=pw, _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
End With[/vba]

You'd need to change the rows to work dynamically, and set a more complex password, of course. This example also gives you a look at all of the excel sheet protection features that can be set in 2003.

Cheers,

[tools]

Thanks ken

This worked fine but it asks me for a password when I try inserting some values in a new row.

[tools]

My mistake i wanted to lock hmm for eg cells B15 to B20

what i did was
[vba]
temp = 20
For t = 15 to temp

Dim pw As String
pw = "Password"
With ActiveSheet
.Unprotect pw
.Cells.Locked = False
.Range("B" & t).Cells.Locked = True
.Protect Password:=pw, _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True

End With

Next
[/vba]
This code locked the last cell i.e. B20

I tried something like this

[VBA]
.Range("B" & t & ":B" & temp).Cells.Locked = True
[/VBA]

but didnt work

[Ken Puls]

Hi,

To do cells B15:B20, you'd do something like the following:
[vba] Dim pw As String
Dim lRowFrom As Long
Dim lRowTo As Long

lRowFrom = 15
lRowTo = 20

pw = "Password"
With ActiveSheet
.Unprotect pw
.Cells.Locked = False
.Range("B" & lRowFrom & ":B" & lRowTo).Cells.Locked = True
.Protect Password:=pw, _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True

End With[/vba]

If you want to do the whole rows, change

[vba].Range("B" & lRowFrom & ":B" & lRowTo).Cells.Locked = True[/vba]
To this
[vba].Rows(lRowFrom & ":" & lRowTo).Cells.Locked = True[/vba]

HTH,

[tools]

Thanks Ken

worked fine

[Ken Puls]

Great, glad to hear it.