VBA Excel 97: Worksheet/book Protection with Password ?
No, _not_ another question about password cracking....
Do you know a way in VBA to determine whether the worksheet / workbook protection was done with or without (i.e. left blank) a password ?
If not: Do you know a way how to catch the return of the password prompting dialog which Excel pops up when you try to unprotect by VBA a worksheet without passing the required password ?
Background: The auditing arrows are very handy, but for some reason they do not work on a protected worksheet. I am making a work-around macro and I want it to re-apply the password at the end if one was present. A fixed dialog is too cumbersome, personally I never use a password (crackers costing ~20$), but some users do.
Do you know a way in VBA to determine whether the worksheet / workbook protection was done with or without (i.e. left blank) a password ?
If not: Do you know a way how to catch the return of the password prompting dialog which Excel pops up when you try to unprotect by VBA a worksheet without passing the required password ?
Background: The auditing arrows are very handy, but for some reason they do not work on a protected worksheet. I am making a work-around macro and I want it to re-apply the password at the end if one was present. A fixed dialog is too cumbersome, personally I never use a password (crackers costing ~20$), but some users do.
Cri and Bruintje,
How about this function?
Function HasPassword(ws As Worksheet) As Boolean
If ws.ProtectContents = True Then
'If it is protected, try to unprotect it
On Error Resume Next
ws.Unprotect
On Error GoTo 0
If ws.ProtectContents = True Then
'If still protected, it has a password
HasPassword = True
Else
'If not protected now, it had no password
HasPassword = False
'Protect sheet again
ws.Protect
End If
Else
'If it wasn't protected to start with, it has no password
HasPassword = False
End If
End Function
Ture Magnusson
Karlstad, Sweden
How about this function?
Function HasPassword(ws As Worksheet) As Boolean
If ws.ProtectContents = True Then
'If it is protected, try to unprotect it
On Error Resume Next
ws.Unprotect
On Error GoTo 0
If ws.ProtectContents = True Then
'If still protected, it has a password
HasPassword = True
Else
'If not protected now, it had no password
HasPassword = False
'Protect sheet again
ws.Protect
End If
Else
'If it wasn't protected to start with, it has no password
HasPassword = False
End If
End Function
Ture Magnusson
Karlstad, Sweden
dirty, really dirty ;)
:-D
ASKER
bruintje: But it works....
ture: 'This' comming from you makes me almost sure there is no neater way.
Nonetheless: Assuming I do not want to probe first: Is there a way to intercept/copy the return of the default password entry dialog which will pop up if not overruled by the 'Resume Next' ? Strictly speaking I do not need to know whether there was a password, I want to ensure that the password is automatically re-applied afterwards.
ture: 'This' comming from you makes me almost sure there is no neater way.
Nonetheless: Assuming I do not want to probe first: Is there a way to intercept/copy the return of the default password entry dialog which will pop up if not overruled by the 'Resume Next' ? Strictly speaking I do not need to know whether there was a password, I want to ensure that the password is automatically re-applied afterwards.
ASKER
ture, an update ?
Sorry, ri... Will look into this tomorrow.
/Ture
/Ture
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The main reason why I use a UserForm is because InputBox doesn't provide a way to hide password characters. The form also provides a way to determine if Cancel was clicked and makes it possible to gracefully get out if that happens.
Inputbox returns the same value if Cancel is clicked as if OK is clicked with an empty input value.
/Ture
Inputbox returns the same value if Cancel is clicked as if OK is clicked with an empty input value.
/Ture
ASKER
Ture, thank you for workbook. I have increased the points to 250.
Regarding the subquestion regarding making a copy of the _regular_ password dialog return value I will post an additional question as I want to keep my little macros as much stand-alone as possible, using too many auxiliary subs and functions make them too vulnerable to my tinkering.
Regarding the subquestion regarding making a copy of the _regular_ password dialog return value I will post an additional question as I want to keep my little macros as much stand-alone as possible, using too many auxiliary subs and functions make them too vulnerable to my tinkering.
this is for Word but i thought it might interest you anyway
http://groups.google.com/groups?hl=nl&selm=%232lWCHyhBHA.2372%40tkmsftngp04&rnum=11
:O)Bruintje