Assigning range passwords using a macro

[SlinkRN]

Hi! I want to assign a range password (using Excel XP) to each range to the right of a list of names. I want to do it with a macro so that each range's password is the actual name. The code to assign a range password is something like this:

dim strName as string
strName = activecell
Range("B10:J10").select
activesheet.protection.allowEditRanges.Add Title:="Range1", Range:= _
Range("B10:J10"), Password:=strName

Can I do this in some sort of loop that will go through the whole list and assign each name as the password for that range? I have a worksheet example but I can't figure out how to post it using Colo's HTML download. It works on my screen, but I can't figure out how to get it to show up in this post

Anyway, the list of names is in column A starting in row 10.
Can anyone help?

[Colo]

Hi SlinkRN,

Originally Posted by SlinkRN

I have a worksheet example but I can't figure out how to post it using Colo's HTML download. It works on my screen, but I can't figure out how to get it to show up in this post

Thank you for using HTML Maker! click [View Source] and please just paste the generated code in to Message body area.
or click [Please click this button to send the source to clipboard]. The generated code will be copied in the clipboard.
So just paste it into the Message body area.
It seems just code lines in the the Message body textbox but it will be table after submited.

Originally Posted by SlinkRN

Can I do this in some sort of loop that will go through the whole list and assign each name as the password for that range?

Yes you can do that. Please read Offset method in the VBA help.

Sub Test()
    Dim i As Long
    Dim strName As String
    For i = 0 To 5
        strName = ActiveCell.Offset(i).Value
        On Error Resume Next
        ActiveSheet.Protection.AllowEditRanges.Add Title:="Range" & i + 1, _
        Range:=Range("B10:J10").Offset(i), Password:=strName
    Next
End Sub

******** ******************** ************************************************************************>

Microsoft Excel - Book1.xls ___Running: 11.0 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
F13 =
	A	B	C	D	E	F	G	H	I	J
10	Bill	*	*	*	*	*	*	*	*	*
11	Juan	*	*	*	*	*	*	*	*	*
12	Colo	*	*	*	*	*	*	*	*	*
13	Ivan	*	*	*	*	*	*	*	*	*
14	Dennis	*	*	*	*	*	*	*	*	*
15	kristy	*	*	*	*	*	*	*	*	*
Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

[SlinkRN]

Thanks Colo!
The many lines of code that showed up scared me off - I didn't know that it shows up as a spreadsheet after you hit submit. Now I know - thanks

Thanks also for the code! I haven't gotten the range passwords to work yet using the macro, but once I figure that out, your code will help me to make it work for the whole list. I used the record macro option to see the basic code for assigning a range password, but when I try to run the recorded macro, I get an error message "object required"
This is the basic code that won't work even though it was recorded:
Protection.AllowEditRanges.Add Title:="Range1", Range:=Range( _
"B10:J10"), Password:="Brooks, Robert"
Any ideas?

[SlinkRN]

Hi again Colo, I just tried your code and it works great! Very cool! However, I won't ever know how many names there will be - so how would you adjust the code to work for however many names there are until it hits a blank? Thanks so much! Slink

[Colo]

Assume the names are located in column A1 to A100, you can get the last row of the column A something like this.

myRow=range("A65536").end(xlup).row

It stands for same meaning to press [Ctrl] + [Up arrow] keys same time from A65536 cell.

[SlinkRN]

Works like a charm! Thanks Colo!

[SlinkRN]

OK. Now when I insert a new row in order to add a new name, my code doesn't work anymore. For some reason the new row gets lumped together with the row before it for the password. Here's the code:

Sub Test()

Dim myRow As Variant
myRow = Range("A65536").End(xlUp).Row


Dim i As Long
Dim strName As String
For i = 0 To myRow - 10
strName = ActiveCell.Offset(i).Value
On Error Resume Next
ActiveSheet.Protection.AllowEditRanges.Add Title:="Range" & i + 1, _
Range:=Range("B10:J10").Offset(i), Password:=strName
Next
End Sub

And here's the worksheet:

******** ******************** ************************************************************************>

Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A10 =
	A	B	C	D	E	F	G	H	I	J
10	Brooks,*Robert	*	*	*	*	*	*	*	*	*
11	Caper,*Connie	*	*	*	*	*	*	*	*	*
12	Hawthorne,*Harry	*	*	*	*	*	*	*	*	*
13	Ippema,*Irena	*	*	*	*	*	*	*	*	*
14	Jones,*John	*	*	*	*	*	*	*	*	*
15	Korn,*Kelly	*	*	*	*	*	*	*	*	*
16	Robertson,*James	*	*	*	*	*	*	*	*	*
17	Smith,*Bob	*	*	*	*	*	*	*	*	*
18	Stob,*Paul	*	*	*	*	*	*	*	*	*
19	*	*	*	*	*	*	*	*	*	*
20	Thompson,*Tom	*	*	*	*	*	*	*	*	*
Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

[SlinkRN]

Do I need to delete the passwords before I can run the macro again? What happens with my sheet is that (very infrequently) a new row is inserted with a new name (new employee). I'm assuming that the passwords will all be off then since it won't allow anything but absolute ref. when I'm choosing the range for the password. So, I figured that I could just reassign each password to the whole list again. The password is just a way to avoid the user from entering information in the wrong row - obviously everyone will know everyone else's password. Although my next project will be to see if I can allow them to change their password to something secret. The boss' password still works for all rows. Hope this makes sense to someone out there Thanks, Slink