Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / March 2007

Tip: Looking for answers? Try searching our database.

password protect the button in Excel?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
cfman - 02 Mar 2007 20:48 GMT
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!
Bernard Liengme - 02 Mar 2007 20:57 GMT
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
Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Hi all,
>
[quoted text clipped - 7 lines]
>
> Thanks a lot!
S Davis - 02 Mar 2007 21:06 GMT
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.
Harlan Grove - 02 Mar 2007 21:02 GMT
"cfman" <comtech....@gmail.com> 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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.