PDA

View Full Version : Solved: Password Protecting particular rows in excel



tools
05-19-2008, 10:36 PM
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
05-19-2008, 10:47 PM
You could hide those rows, unlock the visible cells, and password protect the worksheet.

tools
05-19-2008, 10:53 PM
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
05-20-2008, 06:44 AM
You could try adapting this kb entry (http://vbaexpress.com/kb/getarticle.php?kb_id=931)

Ken Puls
05-20-2008, 08:29 PM
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
05-20-2008, 08:37 PM
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
05-20-2008, 08:54 PM
Okay, fair enough.

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

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

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
05-20-2008, 10:19 PM
Thanks ken

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

tools
05-20-2008, 10:34 PM
My mistake i wanted to lock hmm for eg cells B15 to B20

what i did was

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

This code locked the last cell i.e. B20

I tried something like this


.Range("B" & t & ":B" & temp).Cells.Locked = True


but didnt work

Ken Puls
05-20-2008, 11:25 PM
Hi,

To do cells B15:B20, you'd do something like the following:
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

If you want to do the whole rows, change

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

HTH,

tools
05-21-2008, 05:17 AM
Thanks Ken

worked fine :)

Ken Puls
05-21-2008, 08:25 PM
Great, glad to hear it. :)