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.