Hi all,

I have some buttons in my Excel files and assigned macro behind them. I have
add password protection to the VBA code so without password, the users won't
see the VBA code. But now my boss want even the buttons to be password
protected -- he doesn't want users to click on the button and run the macro
without permission.

How to add security also on the button?

Thanks a lot!

At the start of the macro, have an Input pop up and ask for the password.
Test inputted value against the actual password and branch within the code
as appropriate.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"cfman" wrote in message
...
Hi all,

I have some buttons in my Excel files and assigned macro behind them. I
have add password protection to the VBA code so without password, the
users won't see the VBA code. But now my boss want even the buttons to be
password protected -- he doesn't want users to click on the button and run
the macro without permission.

How to add security also on the button?

Thanks a lot!

"cfman" wrote...
I have some buttons in my Excel files and assigned macro behind them.
I have add password protection to the VBA code so without password,
the users won't see the VBA code. But now my boss want even the
buttons to be password protected -- he doesn't want users to click on
the button and run the macro without permission.
....

Your boss shouldn't be having you do this in Excel or any other
spreadsheet if he's so concerned about security.

The only way to password protect buttons is to add code at the
beginning of the macros called by the buttons that would prompt the
user to enter the password, check if it's correct, and if so proceed,
but if not issue a warning and terminate immediately.

Try something like this:

Put this in the 'ThisWorkBook' area:
Private Sub Workbook_Open()
g_mStrPW = "ChooseYourPassword"
MsgBox "Note: This workbook is password protected."
End Sub

Put this in the 'Sheet#' area, whichever is applicable:
Private Sub LockEM_Click()
Dim i As Long
Dim WS As Worksheet
g_mStrPW = InputBox("Password:")
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Protect (g_mStrPW)
If WS.Protection.AllowUsingPivotTables = False Then
WS.Protect Password:=g_mStrPW, AllowUsingPivotTables:=True,
AllowFiltering:=True, DrawingObjects:=False, Contents:=True,
Scenarios:= _
True
End If
Next
MsgBox i & " errors while protecting", vbInformation

Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

Private Sub UnLockEM_Click()
Dim i As Long
Dim PW_unlock As String
Dim WS As Worksheet
PW_unlock = InputBox("Password:")

On Error GoTo MyErr
If PW_unlock < g_mStrPW Then
MsgBox "Error: Failed to unprotect worksheets! Please check password
in ThisWorkBook and retry."
Exit Sub
Else
For Each WS In ActiveWorkbook.Worksheets
WS.Unprotect (PW_unlock)
Next
MsgBox i & " errors while unprotecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End If
End Sub

This code will lock or unlock the entire workbook - no changes to
cells can be saved, no buttons can be pressed.

I totally agree with Harlan. This is not secure in the sense of real
security. Taking this route is a preventative measure only. It will prevent
honest unauthorized users from access. It will only slow the dishonest
unauthorized users down.

If you want to proceed, I would suggest getting the logon username of the
user and use that username to run or not run the macro, rather than using
passwords. Using passwords would be a continuous maintenance area for you to
maintain. If you have multiple users, you're talking about a lot of multiple
passwords to maintain. There is probably 400 or so employees that use one of
my toolbars. I definitely do not want to maintain passwords for all those
people. I would set up a database of employee names and who has access to
what macro. Use Vlookup into a data table to sort out what runs and what
doesn't. The only maintenance you will have is adding and deleting employees
and their associations to what macros.

Regards,

Alan


"cfman" wrote in message
...
Hi all,

I have some buttons in my Excel files and assigned macro behind them. I
have add password protection to the VBA code so without password, the
users won't see the VBA code. But now my boss want even the buttons to be
password protected -- he doesn't want users to click on the button and run
the macro without permission.

How to add security also on the button?

Thanks a lot!