Password required to run Macro

[OX_2005]

Is there a way when someone clicks on the Macro button it will require a password to run the Macro?

 

[Logit]

Option Explicit
Option Private Module   'hides macro from users



Sub pass()

Dim cell As Range
Dim pwd As String
Dim Oops As Boolean

Application.EnableEvents = False
        
        pwd = Application.InputBox("Enter Password", Type:=2)
        
        If pwd = "" Then GoTo Oops
            
        If pwd = "abc" Then
        
            'your macro code here or call to macro
            
            MsgBox "Password Correct !", vbInformation, "Password Error"
            Exit Sub
        End If
        
Oops:
MsgBox "Bad password"
Exit Sub

Application.EnableEvents = True
End Sub

 

[OX_2005]

Option Explicit
Option Private Module   'hides macro from users



Sub pass()

Dim cell As Range
Dim pwd As String
Dim Oops As Boolean

Application.EnableEvents = False
       
        pwd = Application.InputBox("Enter Password", Type:=2)
       
        If pwd = "" Then GoTo Oops
           
        If pwd = "abc" Then
       
            'your macro code here or call to macro
           
            MsgBox "Password Correct !", vbInformation, "Password Error"
            Exit Sub
        End If
       
Oops:
MsgBox "Bad password"
Exit Sub

Application.EnableEvents = True
End Sub

I put the code in but it keeps highlighting "Option Private Module" red and wont show the Macro when I try to assign it to a button

 

[Logit]

You can delete that line of code from the macro Option Private Module 'hides macro from users

Then replace it with the word "Private" in front of "Sub Pass". This will slightly prevent users from seeing the macro and the password contained
therein :

Option Explicit

Private Sub pass()

Dim cell As Range
Dim pwd As String
Dim Oops As Boolean

 

[OX_2005]

Then replace it with the word "Private" in front of "Sub Pass". This will slightly prevent users from seeing the macro and the password contained
therein :

Option Explicit

Private Sub pass()

Dim cell As Range
Dim pwd As String
Dim Oops As Boolean

I have updated the code and I am not able to see the code to assign Macro

 

[OX_2005]

I have updated the code and I am not able to see the code to assign Macro

Just to make sure I am putting this code in the right place. I would be putting the code into a Module not the sheet correct?

 

[Edgar_]

Is there a way when someone clicks on the Macro button it will require a password to run the Macro?

Truth be told, the best you can do to prevent that is to use a .NET add-in.

Using VBA, your user can easily open the VBA IDE in a bunch of different ways. Then, they can ask the GPT where the code is that's stopping things from running, and just swap it out by copying and pasting. The add-in approach would allow you to set up a much tighter security layer. So even if you've got a sneaky user, they'll probably throw in the towel because the code will be unavailable and you can block it from running whatever you please with whatever rules you choose. Like, you could set up web validation or even add a licensing system and make money from it.

 

[Logit]

If you set up the macro first without using the word "Private" you'll be able to see the macro when you assign a Command Button to it (I am assuming this is what you
mean by not being able to 'see' the macro). After you have everything just the way you want it and you are ready to "go live" with your project, you can put the word "Private" back in to hide the macro from most users. It will still work as you desire.

 

[OX_2005]

One more question for the pop up that ask for the password can the char's beset to "*" instead of seeing what being typed?

 

[Logit]

There is quite a bit more code required to utilize the asterisks to hide your password :

Option Explicit

'////////////////////////////////////////////////////////////////////
 'Password masked inputbox
 'Allows you to hide characters entered in a VBA Inputbox.
 '
 'Code written by Daniel Klann
 'http://www.danielklann.com/
 'March 2003
 
 '// Kindly permitted to be amended
 '// Amended by Ivan F Moala
 '// http://www.xcelfiles.com
 '// April 2003
 '// Works for Xl2000+ due the AddressOf Operator
 '////////////////////////////////////////////////////////////////////
 
 '********************   CALL FROM FORM *********************************
 '    Dim pwd As String
 '
 '    pwd = InputBoxDK("Please Enter Password Below!", "Database Administration Security Form.")
 '
 '    'If no password was entered.
 '    If pwd = "" Then
 '        MsgBox "You didn't enter a password!  You must enter password to 'enter the Administration Screen!" _
 '        , vbInformation, "Security Warning"
 '    End If
 '**************************************
 
 
 
 'API functions to be used
Private Declare Function CallNextHookEx _
Lib "user32" ( _
ByVal hHook As Long, _
ByVal ncode As Long, _
ByVal wParam As Long, _
lParam As Any) _
As Long
 
Private Declare Function GetModuleHandle _
Lib "kernel32" _
Alias "GetModuleHandleA" ( _
ByVal lpModuleName As String) _
As Long
 
Private Declare Function SetWindowsHookEx _
Lib "user32" _
Alias "SetWindowsHookExA" ( _
ByVal idHook As Long, _
ByVal lpfn As Long, _
ByVal hmod As Long, _
ByVal dwThreadId As Long) _
As Long
 
Private Declare Function UnhookWindowsHookEx _
Lib "user32" ( _
ByVal hHook As Long) _
As Long
 
Private Declare Function SendDlgItemMessage _
Lib "user32" Alias "SendDlgItemMessageA" ( _
ByVal hDlg As Long, _
ByVal nIDDlgItem As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) _
As Long
 
Private Declare Function GetClassName _
Lib "user32" _
Alias "GetClassNameA" ( _
ByVal hWnd As Long, _
ByVal lpClassName As String, _
ByVal nMaxCount As Long) _
As Long
 
Private Declare Function GetCurrentThreadId _
Lib "kernel32" () _
As Long
 
 'Constants to be used in our API functions
Private Const EM_SETPASSWORDCHAR = &HCC
Private Const WH_CBT = 5
Private Const HCBT_ACTIVATE = 5
Private Const HC_ACTION = 0
 
Private hHook As Long
 
Public Function NewProc(ByVal lngCode As Long, _
    ByVal wParam As Long, _
    ByVal lParam As Long) As Long
    
    Dim RetVal
    Dim strClassName As String, lngBuffer As Long
    
    If lngCode < HC_ACTION Then
        NewProc = CallNextHookEx(hHook, lngCode, wParam, lParam)
        Exit Function
    End If
    
    strClassName = String$(256, " ")
    lngBuffer = 255
    
    If lngCode = HCBT_ACTIVATE Then 'A window has been activated
        RetVal = GetClassName(wParam, strClassName, lngBuffer)
        If Left$(strClassName, RetVal) = "#32770" Then 'Class name of the Inputbox
             'This changes the edit control so that it display the password character *.
             'You can change the Asc("*") as you please.
            SendDlgItemMessage wParam, &H1324, EM_SETPASSWORDCHAR, Asc("*"), &H0
        End If
    End If
    
     'This line will ensure that any other hooks that may be in place are
     'called correctly.
    CallNextHookEx hHook, lngCode, wParam, lParam
    
End Function
 
 '// Make it public = avail to ALL Modules
 '// Lets simulate the VBA Input Function
Public Function InputBoxDK(Prompt As String, Optional Title As String, _
    Optional Default As String, _
    Optional Xpos As Long, _
    Optional Ypos As Long, _
    Optional Helpfile As String, _
    Optional Context As Long) As String
    
    Dim lngModHwnd As Long, lngThreadID As Long
    
     '// Lets handle any Errors JIC! due to HookProc> App hang!
    On Error GoTo ExitProperly
    lngThreadID = GetCurrentThreadId
    lngModHwnd = GetModuleHandle(vbNullString)
    
    hHook = SetWindowsHookEx(WH_CBT, AddressOf NewProc, lngModHwnd, lngThreadID)
    If Xpos Then
        InputBoxDK = InputBox(Prompt, Title, Default, Xpos, Ypos, Helpfile, Context)
    Else
        InputBoxDK = InputBox(Prompt, Title, Default, , , Helpfile, Context)
    End If
    
ExitProperly:
    UnhookWindowsHookEx hHook
    
End Function
 
Sub TestDKInputBox()
    Dim x
    
    x = InputBoxDK("Type your password here.", "Password Required")
    If x = "" Then End
    If x <> "123" Then
        MsgBox "You didn't enter a correct password."
        End
    End If
    
    MsgBox "Welcome Creator!", vbExclamation
    
    'your macro code here or call to macro
    
End Sub