Solved Sheet password instruction query

February 28, 2014 at 10:47:19
Specs: Windows 7
Hi Guys,
I am using the following to open up a sheet that is password protected
From 'DASHBOARD' to 'ADMIN'

Sub showadmin2()

    Sheets("ADMIN").Visible = True
    Sheets("ADMIN").Select
    
    ActiveSheet.Unprotect
End Sub

However if the User does not know the password, and hits cancel, How can I get the program to return to the 'DASHBOARD'
Alternatively, I have From Control Buttons that needed to be locked unless the password is correct.
Is either way possible, and if so, any ideas for me to be able to use


See More: Sheet password instruction query

Report •


✔ Best Answer
March 2, 2014 at 11:02:39
re: What have I missed or got wrong. ??

When you debug your code, do you use F8 to single step through the instructions and use Watches to help figure out what is going on? I assume that you don't because if you did you would understand why the code is taking you "straight back to the 'DASHBOARD' and hiding 'ADMIN'."

Set a Watch on Sheets("ADMIN").ProtectContents and watch what happens as you single step through the code.

If you are not familiar with Single Stepping or Watches, read this How To:

http://www.computing.net/howtos/sho...

message edited by DerbyDad03



#1
February 28, 2014 at 11:48:54
If Sheets("ADMIN").ProtectContents Then
  'Sheet still protected
Else
  'Sheet unprocted
End If

How To Ask Questions The Smart Way


Report •

#2
February 28, 2014 at 13:01:40
To expand on Razor2.3's suggestion, you may want to deal with incorrect passwords as well as the Cancel button. An incorrect password may cause the code to throw up an error before it enters the IF section since the code is trying to execute at the same time that Excel is trying to present the "wrong password" message.

This code captures the error and let's the code continue.

Sub showadmin2()
On Error GoTo PW_Bad
    Sheets("ADMIN").Visible = True
    Sheets("ADMIN").Select
    ActiveSheet.Unprotect
    
PW_Bad:
  If Err = 1004 Then _
     MsgBox "You Have Entered An Incorrect Password"
   If Sheets("ADMIN").ProtectContents Then
     Sheets("ADMIN").Visible = False
     Sheets("DASHBOARD").Activate
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
March 2, 2014 at 09:34:29
Sub showadmin3()

On Error GoTo PW_Bad
    Sheets("ADMIN").Visible = True
    Sheets("ADMIN").Select
' unprotects sheet with administrators password
    ActiveSheet.Unprotect
' protects sheet with program password
    Sheets("ADMIN").Protect Password:="aAAAaAaBBa", _
          DrawingObjects:=True, Contents:=True, Scenarios:=True
   
' if error or incorrect password returns to dashboard
PW_Bad:
  If Err = 1004 Then _
     MsgBox "You Have Entered An Incorrect Password"
   If Sheets("ADMIN").ProtectContents Then
     Sheets("ADMIN").Visible = False
     Sheets("DASHBOARD").Activate
   End If
End Sub

Hi DerbyDad03, super little bit of code but I have been trying to manipulate or take it a bit further. Notice these following lines of code in the above

' unprotects sheet with administrators password
ActiveSheet.Unprotect
' protects sheet with program password
Sheets("ADMIN").Protect Password:="aAAAaAaBBa", _
DrawingObjects:=True, Contents:=True, Scenarios:=True

The admin password allows access to the sheet. However I also need the sheet to then be protected by the program password so that only certain fields can have data entered into them.
With the above , it is taking me straight back to the 'DASHBOARD' and hiding 'ADMIN'

What have I missed or got wrong. ??

I have a form control button on the 'ADMIN' sheet to hide the sheet, where the coding would unprotect using the program password and then protect the sheet with the Administrators password defined by a cell value hidden in the 'DASHBOARD' sheet

Sub hideadmin()
'
' unprotects sheet with program password
    Sheets("ADMIN").Unprotect Password:="aAAAaAaBBa"
' protect sheet with administrator password
    Sheets("ADMIN").Protect Password:=Range("DASHBOARD!B33"), _
          DrawingObjects:=True, Contents:=True, Scenarios:=True
    
     ActiveWindow.SelectedSheets.Visible = False
      Sheets("DASHBOARD").Select
End Sub

edited by moderator: Fit to screen


Report •

Related Solutions

#4
March 2, 2014 at 10:36:23
tonygibb: The admin password allows access to the sheet. However I also need the sheet to then be protected by the program password so that only certain fields can have data entered into them.
With the above , it is taking me straight back to the 'DASHBOARD' and hiding 'ADMIN'

What have I missed or got wrong. ??
I have no idea what you're asking. You're trying to protect the sheet, but not the data on the sheet? Then just toggle off the Locked properties on the cells in question.

How To Ask Questions The Smart Way


Report •

#5
March 2, 2014 at 11:02:39
✔ Best Answer
re: What have I missed or got wrong. ??

When you debug your code, do you use F8 to single step through the instructions and use Watches to help figure out what is going on? I assume that you don't because if you did you would understand why the code is taking you "straight back to the 'DASHBOARD' and hiding 'ADMIN'."

Set a Watch on Sheets("ADMIN").ProtectContents and watch what happens as you single step through the code.

If you are not familiar with Single Stepping or Watches, read this How To:

http://www.computing.net/howtos/sho...

message edited by DerbyDad03


Report •

#6
March 2, 2014 at 11:03:03
Razor2.3
When a sheet is password protected, you can ensure that certain fields will accept data input. In this case out of the whole sheet, only 4 or five cells require data to be input. Other cells operate formulas that will give values calculated from the input data.
There will be one or two people that will be able to input the data, and several other people who will use the workbook but do not require access to the admin sheet.

Initially I need the unprotect password box to appear and, for the user to enter data into the sheet, the correct password entered. However by entering the correct password, the sheet becomes unprotected completely.

I need to ensure that once entry to the sheet has been gained , that only the cells to have data input are unlocked, not the entire sheet. If data were to be input into a cell with a formula, it could completely corrupt the workbook.

So I believe the process would be to protect the sheet again with a generic password


Report •

#7
March 2, 2014 at 11:50:58
Sub showadmin3()

On Error GoTo PW_Bad
    Sheets("ADMIN").Visible = True
    Sheets("ADMIN").Select
' unprotects sheet with administrators password
    Sheets("ADMIN").Unprotect

      
' if error or incorrect password returns to dashboard
PW_Bad:
    If Err = 1004 Then _
     MsgBox "You Have Entered An Incorrect Password"
   If Sheets("ADMIN").ProtectContents Then
     Sheets("ADMIN").Visible = False
     Sheets("DASHBOARD").Activate
   End If
   
    Sheets("ADMIN").Protect Password:="aABBbAbbAA", _ 
          DrawingObjects:=True, Contents:=True, Scenarios:=True
     
End Sub

Many thanks for the heads up, can see what was going on and have rectified the coding to the above. Stupidity is reckless, not clever when I have the function to help and do not use it. Thanks for pointing me in the right direction, look out for my next question

edited by moderator: Fit to page


Report •

#8
March 2, 2014 at 12:59:00
Sheets("ADMIN").Protect Password:="aABBbAbbAA", DrawingObjects:=True, Contents:=True, Scenarios:=True
Warning: This line will silently fail if the sheet is still protected. This will cause the Unprotect function to fail noisy.

I'm still not sure why you're using the sheet protection features, just to relock it with a new password. You really should stop and rethink your course of action here.

How To Ask Questions The Smart Way


Report •

#9
March 2, 2014 at 13:44:25
If you are going to put a password in your VBA code, I hope that you are protecting and hiding the VBA code itself. If not, any user can view the code and see the password. In most cases, you should be protecting and hiding all of your code so that users can't see it and/or alter it.

In addition, when you post code in this forum, please try to keep the lines short enough so that we don't have to scroll sideways to read the text. You will note that I edited both of your recent posts to split this line so that the entire post stays within the borders of the message frame.

Sheets("ADMIN").Protect Password:="aABBbAbbAA", _ 
         DrawingObjects:=True, Contents:=True, Scenarios:=True

Space Underscore Space is used in VBA code to split a long instruction line into multiple lines to make the code easier to read in both the VBA editor! and coincidentally, this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question