The plan is to allow a user to change worksheet, workbook
and some textbox passwords via a button on the sheet and
then a form. This is so I can code the thing but if they
want to change the passwords they don't have to keep
coming back to me to change the code.


Thanks to John for setting me on the right track.
I've now got the passwords in a veryhidden sheet.


However when the user clicks the button on the sheet to
change the password, I have to unprotect the sheet with
the old password, protect it with the new one and save the
sheet. The trouble is I've getting run time errors on the
unprotect and protect bits

sheet1.unprotect passwor:= sheet3.range("a65536").end
(xlup).value

The errors normally method unprotect of object failed.

Any ideas?

Libby,

sheet1.unprotect passwor:= sheet3.range("a65536").end
(xlup).value
Is the above a typo????
Should be sheet1.unprotect password
Also, are you sure that the value you're getting is the one that
you want???
Try
MSgBox sheet3.range("a65536").end(xlup).value
just before that line of code to see what's there.

Also...would be better to use
Worksheets("Sheet3") as opposed to just Sheet3

Thanks to John for setting me on the right track.
I've now got the passwords in a veryhidden sheet.
You're welcome

John


"Libby" wrote in message
...
The plan is to allow a user to change worksheet, workbook
and some textbox passwords via a button on the sheet and
then a form. This is so I can code the thing but if they
want to change the passwords they don't have to keep
coming back to me to change the code.


Thanks to John for setting me on the right track.
I've now got the passwords in a veryhidden sheet.


However when the user clicks the button on the sheet to
change the password, I have to unprotect the sheet with
the old password, protect it with the new one and save the
sheet. The trouble is I've getting run time errors on the
unprotect and protect bits

sheet1.unprotect passwor:= sheet3.range("a65536").end
(xlup).value

The errors normally method unprotect of object failed.

Any ideas?

You can pass parameters to .unprotect by position--like you suggested:

sheet1.unprotect password

Or you can pass it by keyword--like Libby did (with a typo, though):

sheet1.unprotect password:=sheet3.range("a65536").end(xlup).value

And the sheet1 and sheet3 don't have to be the same thing as the user sees in
the worksheet tab in excel.

When you're in the VBE and you can see the project explorer and the names of the
worksheets, you'll see something like:

Sheet1 (My Sheet Name on the Tab)

That name in parentheses is the worksheet name that you see in Excel.

The name in front of that name is called the CodeName of the worksheet.

If you use the codename in your code, there's a better chance that things won't
break.

If the user renames the worksheet, you'll get an error if you're looking for the
previous name.

Changing the Codename is more difficult for the average user.


John Wilson wrote:

Libby,

sheet1.unprotect passwor:= sheet3.range("a65536").end
(xlup).value
Is the above a typo????
Should be sheet1.unprotect password
Also, are you sure that the value you're getting is the one that
you want???
Try
MSgBox sheet3.range("a65536").end(xlup).value
just before that line of code to see what's there.

Also...would be better to use
Worksheets("Sheet3") as opposed to just Sheet3

Thanks to John for setting me on the right track.
I've now got the passwords in a veryhidden sheet.
You're welcome

John

"Libby" wrote in message
...
The plan is to allow a user to change worksheet, workbook
and some textbox passwords via a button on the sheet and
then a form. This is so I can code the thing but if they
want to change the passwords they don't have to keep
coming back to me to change the code.


Thanks to John for setting me on the right track.
I've now got the passwords in a veryhidden sheet.


However when the user clicks the button on the sheet to
change the password, I have to unprotect the sheet with
the old password, protect it with the new one and save the
sheet. The trouble is I've getting run time errors on the
unprotect and protect bits

sheet1.unprotect passwor:= sheet3.range("a65536").end
(xlup).value

The errors normally method unprotect of object failed.

Any ideas?

--

Dave Peterson

And I'd bet you're correct on the passwords not being the same as expected.

John Wilson wrote:

Libby,

sheet1.unprotect passwor:= sheet3.range("a65536").end
(xlup).value
Is the above a typo????
Should be sheet1.unprotect password
Also, are you sure that the value you're getting is the one that
you want???
Try
MSgBox sheet3.range("a65536").end(xlup).value
just before that line of code to see what's there.

Also...would be better to use
Worksheets("Sheet3") as opposed to just Sheet3

Thanks to John for setting me on the right track.
I've now got the passwords in a veryhidden sheet.
You're welcome

John

"Libby" wrote in message
...
The plan is to allow a user to change worksheet, workbook
and some textbox passwords via a button on the sheet and
then a form. This is so I can code the thing but if they
want to change the passwords they don't have to keep
coming back to me to change the code.


Thanks to John for setting me on the right track.
I've now got the passwords in a veryhidden sheet.


However when the user clicks the button on the sheet to
change the password, I have to unprotect the sheet with
the old password, protect it with the new one and save the
sheet. The trouble is I've getting run time errors on the
unprotect and protect bits

sheet1.unprotect passwor:= sheet3.range("a65536").end
(xlup).value

The errors normally method unprotect of object failed.

Any ideas?

--

Dave Peterson