PDA

View Full Version : [SOLVED] Supply one password to unlock all code in workbook



vanhunk
01-16-2014, 08:24 AM
Supply one password to unlock all code in workbook: I have a workbook with a number of buttons (both types) to run macro code. I would like to have all of these buttons and code inactive by default. To use any of these I would like to be able to supply one password once to unlock all of the buttons and code. In other words I do not want to have to type in a password for every macro I want to run or for every button I press, until, say the file is saved again, or the protection is reactivated somehow (maybe with a button). Is this at all possible? Thank you. Regards, vanhunk

Paul_Hossler
01-16-2014, 10:09 AM
Not extremely secure or robust

WB Open to configure and a worksheet event




Option Explicit
Private Sub Workbook_Open()
gbLocked = True
With Worksheets("Sheet1")
.cbLockUnlock.Caption = "Click to Unlock"
.CommandButton1.Enabled = False
.CommandButton2.Enabled = False
.CommandButton3.Enabled = False
End With

gsPassword = "abcd"
End Sub






Option Explicit
Private Sub cbLockUnlock_Click()

Dim s As String

s = Application.InputBox("Enter Password", "Password", , , , , , 2)

If s <> gsPassword Then Exit Sub


gbLocked = Not gbLocked
If gbLocked Then
cbLockUnlock.Caption = "Click to Unlock"
Else
cbLockUnlock.Caption = "Click to Lock"
End If
CommandButton1.Enabled = Not gbLocked
CommandButton2.Enabled = Not gbLocked
CommandButton3.Enabled = Not gbLocked
End Sub




Paul

vanhunk
01-17-2014, 03:04 AM
Hi Paul thanks for the quick reply. If I can get it to work this I can work with. I opened your workbook but can not get it to work, I get a Run-time error '57121': Application-defined or Object-defined Error. Working in Excel 2003 maybe caused it, don't know. Hope you can help. Regards, vanhunk

GTO
01-17-2014, 03:47 AM
... I have a workbook with a number of buttons (both types) to run macro code. I would like to...


Greetings Van,

Might you whip up a small sample file?

I ran Paul's w/no problems.

Mark

snb
01-17-2014, 04:46 AM
Private Sub Workbook_Open()
if environ("computername")="snb" then
for each it in sheet1.oleobjects
it.enabled=true
next
end if
End Sub

Kenneth Hobs
01-17-2014, 06:49 AM
Paul's method will work for part of what you want.

When using snb's method to unlock all objects in the sheet1 object. You may want to also consider using the environment variable "username". I use that method on occasion. Computername is good for some sets of users where IT set a default prefix for the computer names.

If you mean to avoid sheet protection for every sheet modification by macro, put this in the object, ThisWorkbook.

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect "ken", UserInterfaceOnly:=True 'True allows code to change data.
Next ws
End Sub

vanhunk
01-17-2014, 07:33 AM
Private Sub Workbook_Open() if environ("computername")="snb" then for each it in sheet1.oleobjects it.enabled=true next end if End Sub Thanks snb, I found this very useful. Regards, vanhunk

vanhunk
01-17-2014, 07:39 AM
Paul's method will work for part of what you want. When using snb's method to unlock all objects in the sheet1 object. You may want to also consider using the environment variable "username". I use that method on occasion. Computername is good for some sets of users where IT set a default prefix for the computer names. If you mean to avoid sheet protection for every sheet modification by macro, put this in the object, ThisWorkbook.
Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In Worksheets ws.Protect "ken", UserInterfaceOnly:=True 'True allows code to change data. Next ws End Sub Thanks Kenneth, this is just as helpful. Regards, vanhunk

vanhunk
01-17-2014, 07:48 AM
Greetings Van, Might you whip up a small sample file? I ran Paul's w/no problems. Mark @GTO: For some or other reason I can not attach the file. I saved Paul's file as an Excel 2003 file and when I opened it up I got the error message and the code did not run. I would really like to get this code running, would appreciate it if you can help. It must be the 2003 thing I guess! Regards, vanhunk

vanhunk
01-20-2014, 04:38 AM
@GTO: I got the 2003 workbook to work by building it manually, i.e. not converting the newer format. Thanks.