Load Excel 2007 with minimal user interface

Hi another Matt !

Thanks for your assistance, that worked a treat!

Out of curiosity, is there any way to lock users from running certain macros - or setting an execution password on them?

Thanks again,

Matt

Hi Matt,

Yes, and no. There is nothing built in to do this, but you can always make one yourself.  I do want to warn you that nothing in excel is truly secure. A knowledgeable user, or someone who is determined, will get in if they want to. But it keeps the rest of the people out.

The first thing to do would be to request a password from the user, and if it is not correct, simply put Exit Sub. An example, which you would put at the top of your subroutine:

 Dim vPassword As String
 vPassword = InputBox("This macro requires a password to be executed." & vbLf & vbLf & _
  "Please enter the password if you wish to proceed.", "Password required")
 If vPassword <> "your password here" Then
  MsgBox "Invalid password"
  Exit Sub
 End If

You can also get a little fancier, checking for blank entries and not notifying of invalid pw in that case:

 If vPassword <> "your password here" Then
  If Len(vPassword) > 0 Then MsgBox "Invalid password"
  Exit Sub
 End If

If you want to perform different things based on what password was entered (in place of the If block)

 Select Case vPassword
  Case "password1"
   Call OtherSubroutine1
  Case "password2"
   Call OtherSubroutine2
  Case ""
   Exit Sub
  Case Else
   MsgBox "Invalid password entered."
   Exit Sub
 End Select

The next question you might be asking is, "is there a way to mask the password entered, like with * symbols?". That is also possible, though nothing built in for such a thing. You would have to create a userform with a textbox on it setting the textbox's "PasswordChar" property to "*" or whatever you wanted.


The downfall to any of these is that the user could simply go to the VBA editor, and see what password is hardcoded to. To overcome this, you would have to protect the vba project. In the vba editor, go to Tools, then VBAProject Properties. Go to the Protection tab, lock the project for viewing, and add a password.
I'll repeat what I said earlier in that this is not *truly* secure, and a determined person can break into this. But for most users, this wouldn't be an issue.

Matt

Hey Matt,

Thanks for your very quick response. I'll give that a go and post back.

Matt

Thanks Matt - that worked a treat.

Matt

It looks like the asker is unfamiliar with how to close questions, they chose their comment as the accepted answer (with zero points assigned), then mine as assisted (with full points). I don't know if this will make a difference or anything, I've been away from the site for a little while and this process is new to me :)