Need some macro

[Ron99]

Hi,

I have a worksheet, where I have setup password for each and individual tabs. when an employee wants to makechanges to his tab he needs to enter the password, so I have created password for each tabs. I have pasted the code below as to how it works.

Private Sub Worksheet_Activate()
Dim strPassword As String, _
entPassword As String, _
attempts As Long
attempts = 3
strPassword = "NICO1"
Do
entPassword = InputBox("Please enter the password")
If strPassword = entPassword And InStr(strPassword, entPassword) <> 0 Then
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.Unprotect
Else
attempts = attempts - 1
MsgBox "Incorrect password, please try again." & vbLf & attempts & " attempts remaining."
End If
Loop Until (entPassword = strPassword And InStr(strPassword, entPassword) <> 0) Or attempts = 0
If attempts = 0 Then
MsgBox "Please contact Vikas Quadros for your password"
Sheets(1).Activate 'Adjust this line to be a "safe" sheet in your workbook
End If
End Sub


Help that I need is.

I would like to enter in all the tabs and make few changes on a daily basis, it is difficult for me to remember and enter each and everyones password.
the help i require is I want a code where when the password prompt comes up I need to enter a master password, which will disable all the password in this worksheet, if the master password is not correct then the code executes as above.

From the above code if someone can add an IF code, then I guess it will work.

can you please help me

 

[GTO]

I would like to enter in all the tabs and make few changes on a daily basis, it is difficult for me to remember and enter each and everyones password.
the help i require is I want a code where when the password prompt comes up I need to enter a master password, which will disable all the password in this worksheet, if the master password is not correct then the code executes as above.

From the above code if someone can add an IF code, then I guess it will work.

can you please help me

Hi there,

What do you mean "disable" the passwords? Are you just wanting a master password/inputbox to unlock all the sheets?

Reference:
<font face=Courier New><SPAN style="color:#00007F">If</SPAN> strPassword = entPassword And InStr(strPassword, entPassword) <> 0 <SPAN style="color:#00007F">Then</SPAN><br>    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True<br>    ActiveSheet.Unprotect<br><SPAN style="color:#00007F">Else</SPAN><br></FONT>
I am not understanding the InStr usage.

Mark

 

[Ron99]

Hi,

I got the code from one of the guys from mr excel.

I do not know VBA, I really cant understand the code.

I will explain to you in detail as to how the spreadsheet has setup and how the present code is working

I have 10 tabs each tab has a name of the employee.

example :-
tab 1 has the name Nico
tab 2 has the name Ron

and so on....

now when Nico enters his tab he gets a input box or pop up where he should enter his password else spreadsheet will not allow him to edit his tab, the same goes with Ron and others. the input box will prevent one person to enter and edit the others tab if the wrong password is used.

this is basically how the code works.

Now the changes that I need to make to this code is

I am the manager for this project, I would like to assign task to each of the employess in thier respective tabs, now I cannot remember the password and nor do I want to enter each one of the tabs password this will take time as 25 more employees are expected to join shortly.

So when I enter Nico's tab to do some editing, soon as I click on his tab I get a input box for password, here I want to enter a master password and not the Nico's password, when I enter a master password that should not ask me to enter the passwords when click on other tabs.

basically, master password should not prompt the inputbox, if wrong password the code should execute as its doing now.

another thought is there will be two password in Nico's tab one for himself to edit, and one for me, when I enter my password the code should not prompt me or unlock me to make changes in the other tabs.

Please let me know if any questions.

 

[Ron99]

Any luck on this one ?