PDA

View Full Version : Solved: Autodetect password



JeffT
11-25-2006, 08:11 AM
Hi

In a protected sheet, I have to switch off the protection to run some code then I switch it back on again. It's really low level stuff but the QA chap wants to password protect it. The only way I found to make the code run once the password was added was :-

ActiveSheet.Unprotect ("Password")

Code

ActiveSheet.Protect ("Password")

My worry is that the QA manager may change the password sometime, so wondered if there was a way for the Sheet Password to be detected automatically so the actual PW in the code is kept up to date. I protect the VB code using tools / properties .... so nobody can read it directly, (though I know if they know what they're doing it can be bypassed, but it's only a sortable snagsheet.)

Thanks

Jeff

Simon Lloyd
11-25-2006, 08:26 AM
Jeff you could always remove the "Protecton" availability from the Tools menu.

Right click the menu bar, choose customize, now go back to the menu bar click on Tools and right click the word "Protection" you will now have the option to delete it.

You could then add a password change button to your menu bar and then pass the value of that to the VBA project.

Regards,
Simon

Norie
11-25-2006, 01:03 PM
Jeff

Are you sure the QA manager would want you to be using code to get a password, which seems to be what you're asking?

JeffT
11-25-2006, 02:40 PM
Thanks for that suggestion simon. I may have a go at that. I made a completemenu bar a couple of years ago for a project, I'll have to dig it out of my archive.

The QA manager is a friend of mine and won't mind how I achieve what he wants. He's told me his current password so I can put that in. I'm just concerned that as he doesn't know anything about VB he'll have to keep running to me to change the code unless I automate it if he does for any reason change his PW. He'll have to tell me the new password anyway to put it in!
I really don't think the forms I develop warrant password protecting. They are just things like holiday planners, petrol returns, snag sheets etc. I'm not that good, just a few buttons, dialog boxes, & conditional formatting & they think I'm a genius!!

J

Norie
11-25-2006, 02:54 PM
Jeff

So what is it you are actually asking?

It isn't really clear from your post.

JeffT
11-25-2006, 03:26 PM
Hi Norie

The ActiveSheet.Unprotect ("Password") etc will work provided the manager doesn't change the sheet protect password. If he changed the sheet protect password, the PW in the VBA code would be wrong so the code between won't work and they'll be an error message. (I assume).

I was wondering if there were some code which could read the Sheet protect password and store it, so I could use something like Password = NewPassword before it is used by the ActiveSheet.Unprotect ("Password") etc. Then even if the sheet protect password were changed the code would still run.

Sorry if this isn't clear. All I've learn't is by trial and error and from you guys, so I'm not up on all the technical terms etc. I'm not even sure if it's possible to "read" the input into a password field and/or store it1

Regards

Jeff

Norie
11-25-2006, 03:53 PM
Jeff

I'm a little confused by your request.

You say that the QA manager insists on using passwords, you then seem to be asking of some way of, perhaps not breaking, but bypassing these passwords.

What need is there for the passwords in the first place?

Theoretically (that might be spelled (that might be spelled wrong too) wrong :beerchug:) you could store the password somewhere and use code to access it, but I think that would defeat the purpose of the passwords in the first place.

If you were to create code that say read the password from another file, then as long as somebody had permission to access that file then it would work for them and not just the people it 'should' work for.

JeffT
11-25-2006, 04:39 PM
Hi Norie

Thanks for perservering. The problem is that for the code to run it has to unprotect then reprotect the sheet. This only works if the actual password is typed in the code. The form will be stored on the server for lots of people to download and the Manger doesn't want them altering the formatting or accidently deleting formula etc, so he want's to password protect.

I can make that work by typing the actual "Password" after the Unprotect & Protect statements as shown at the start of this thread, then hide the code in VB by password protecting the code using Tools / project properties / protection tab and putting a password in here. Then nobody will be able to read the password which is typed in the code.

However say I drop dead (I try to think of all eventuallities) and he changes the sheet protection password, the form will stop working, as the "password" in the code won't change so it won't be able to unprotect the sheet.

All I was trying to do was make the Sheet protection Password, which I've typed directly into the Activesheet.Unprotect("Password") statement (& Activesheet.Protect statement), change the word "Password" to "Newpassword" automatically should he ever change the password.

I guess from your comments about breaking passwords what I'm trying to do might be used that way, however at present I'm the one who has to type his password into the code so don't actually need to break it. To stop this perhaps there's a way that he could enter a new password in a dialog box & all the passwords, Sheet protection, VB protection and references in the code, could automatically be changed at the same time. Then I wouldn't know any of them! I think I'm back to Simons suggestion.

Thanks

Jeff

johnske
11-25-2006, 07:18 PM
I'd say that if this person 'out-ranks' you at work then they have the right to change the password and lock you out any time they want.

However as passwords are really only to protect things like cell formulas from being accidentally erased and are in no way provide any real 'security', the same goal can be achieved by just protecting the worksheet but leaving the password field empty (that's all I ever do)

JeffT
11-26-2006, 01:06 AM
johnske

The QA manager doesn't out rank me I possibly out rank him but we're in different parts of the management structure. I'm in production, he's admin, so I have no direct say in what he does. I only help him out in my spare time at home with the code as I enjoy it. (and yes I've told him not to bother to use a password as they're easily bypassed if you know what you're doing.) I'm quite happy for him to lock me out It's just that if he does and changes the password the form won't work anymore.

Still I've actually got to start on a word doc today and have never done any coding for word so I'll mark this as solved as I realise I'm not going to get an easy answer. I will try the toolbar method sometime, and for the time being just tell him either leave it without a password (In which case I'll have to change the code to omit the .Protect("Password")!!), or never change it.

Thanks for listening

Jeff

johnske
11-26-2006, 01:16 AM
...(In which case I'll have to change the code to omit the .Protect("Password")!!), or never change it.

Thanks for listening

JeffIf you have an empty password you only need to use something like... :)


Sub A()

ActiveSheet.UnProtect

'your code here

ActiveSheet.Protect

End Sub

JeffT
11-26-2006, 02:57 AM
Thanks Johnske.

I know that. This is a bit like "there's a hole in my bucket".

I use ActiveSheet.UnProtect when a password isn't protecting the sheet which is my default. But if he adds a password that doesn't work, you have to add ("Password") including the quotes and brackets, where Password is the actual password. Thus if in sheet protect he changes the password to NewPassword the code still has Password writen in it so will stop working. That is my problem!

Jeff

Gert Jan
11-26-2006, 09:46 AM
Hi there,
I'm not an expert by far, but could something like this not be usefull?
There must be a lot of persons who could do this much better, but maybe it's a start?


Sub Sheetprotection()
Dim strPassword As String
strPassword = "Password" ' this comes from sub ChangePassword
On Error GoTo Errhandle
ActiveSheet.Unprotect (strPassword)
'Your code
Exit Sub
ErrHandler:
MsgBox "The password has been manually changed " & _
"the program will not function." & Chr(13) & _
"Please change it here as well"
'Call ChangePassword
End Sub


Gert Jan

Dave
11-26-2006, 11:54 PM
You need to choose a blank cell. On ws activate, copy the password the code uses before anyone messes with it to the blank cell. Now use this cell for your code address reference for the password. On workbook close copy it back. He can change the password all he wants your code won't crash. I don't know if you still have to mess with the protection thing? HTH. Dave


I was wondering if there were some code which could read the Sheet protect password and store it, so I could use something like Password = NewPassword before it is used by the ActiveSheet.Unprotect ("Password") etc. Then even if the sheet protect password were changed the code would still run.

JeffT
11-27-2006, 08:38 AM
Thanks GJ & Dave

I'll try those options when I get a chance. He's already posted the form on the server so for now I'll tell him not to change the password without letting me know he's going to do it. Now working on a Word doc. Never written any code for Word but I'm game for a laugh.

Thanks to you all once again.

J

CBrine
11-27-2006, 09:21 AM
JeffT,
Here's another suggestion.

1. Leave the sheet unprotected, with a blank "Sheet1".
2. Add this code to the thisWorkbook level of the vba.
3. Change the UserName condition to match the QA guy's XP login name.
4. Save the workbook.

Now all working sheets will be VeryHidden(Can't be retreived without code), and access is based on the XP username. Since the sheet is really unprotected, your code will not need to unprotect, and if the user changes thier loggin password, it doesn't matter, since you are only looking at who is currently logged in, and they couldn't be logged in without the password. (Hope this makes sense!)


HTH
Cal


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
If ws.Name <> "Sheet1" Then
ws.Visible = xlSheetVeryHidden
End If
Next ws
End Sub
Private Sub Workbook_Open()
Dim ws As Worksheet
If Environ("UserName") = "XPLoginNameHere" Then
For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next ws
End If
End Sub