password protect the button in Excel?
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! |
password protect the button in Excel?
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! |
password protect the button in Excel?
"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. |
password protect the button in Excel?
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. |
password protect the button in Excel?
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! |
All times are GMT +1. The time now is 03:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com