Excel VBA Input box

Question

I have following Input box for excel file. I don't want to show typing characters and need to show input box characters * , how to do this?

Private Sub Workbook_Open()

Dim UserName As String

UserName = InputBox("Please Enter Your USER NAME.")

Range("O1") = UCase(UserName)


End Sub

Thanks, Hewage

In other word, I need to show inputbox characters as Asterisks. — Hewage, Jan 28 '15 at 11:03

score 10 · Accepted Answer · answered Jan 28 '15 at 15:05

10

Use a UserForm Create a userform that has a Textbox, and Two Buttons In the textbox Properties, enter * in the PasswordChar Box

enter image description here

Use the code below in the userForm module.

Private Sub CommandButton1_Click()

If TextBox1 = "123456" Then
MsgBox "Correct"
Else
MsgBox "Incorrect"
End If
Unload Me

End Sub

Private Sub CommandButton2_Click()

'cancel button

Unload Me
End Sub

Private Sub UserForm_Initialize()

Me.Caption = "Enter Password"

End Sub

Your userform will look like this

enter image description here

answered Jan 28 '15 at 15:05

Davesexcel

6,896
2
27
42

Thanks Dave. Actually my codes are complicated than I mentioned in this question. I have excel file with few user names and passwords. Each user assigned worksheets as below; – Hewage Jan 30 '15 at 06:01
User*Name Password Sheet 1 Sheet 2 Sheet 3 Sheet 4 Sheet 5 david 10 x x x x x paul 11 x x x x martin 12 x kumar 13 x x x – Hewage Jan 30 '15 at 06:02
Please disregard above comment. First row of the sheet user name, password and sheets mentioned. Below rows assigned accessibility for each user. As follows; – Hewage Jan 30 '15 at 06:09
User*Name Password User List Admin Menu Carpet david 10 x x x x paul 11 x x martin 12 x kumar 13 x x – Hewage Jan 30 '15 at 06:17
Private Sub Workbook_Open() Dim Sh As Worksheet Dim UserName As String Dim Password As String Dim ThisCell As Range Dim c As Long For Each Sh In ThisWorkbook.Worksheets If Sh.Name <> "Menu" Then Sh.Visible = xlSheetVeryHidden End If Next Sh – Hewage Jan 30 '15 at 06:26
UserName = InputBox("Please Enter Your USER NAME.") Password = InputBox("Please Enter PASSWORD.") For Each ThisCell In Sheets("User List").Range("A2:A" & Sheets("User List").Range("A65536").End(xlUp).Row) If UCase(ThisCell.Value) = UCase(UserName) And UCase(ThisCell.Offset(, 1).Value) = UCase(Password) Then MsgBox "Access Granted" For c = 2 To 55 If ThisCell.Offset(, c).Value <> "" Then Sheets(Sheets("User List").Cells(1, c + 1).Value).Visible = xlSheetVisible End If Next c Exit Sub End If Next ThisCell MsgBox "Access Denied" ThisWorkbook.Close End Sub – Hewage Jan 30 '15 at 06:26
How to mask this password? Hewage – Hewage Jan 30 '15 at 06:31

score 4 · Answer 2 · answered Nov 13 '20 at 20:22

Thank you to Bhanu Pratap for providing Daniel Klann's implementation of changing the built-in InputBox on the fly to mask the characters for use in password prompts. Adding this module to the project and calling PasswordInputBox is way easier than adding a new form.

Thank you to Joseph316 for updating the code to 64-bit so it works in Office 365 Excel (version 16)!

I took the Daniel & Joseph code, renamed some variables and added some comments to make it easier to understand what the code is doing. There is no functional difference.

Option Explicit

'////////////////////////////////////////////////////////////////////
'Password masked inputbox
'Allows you to hide characters entered in a VBA Inputbox.
'
'Code written by Daniel Klann
'March 2003
'
'Code updated by Joseph for Excel 64-bit Environments
'February 2020
'PtrSafe added & Long to PtrLong conversion
'see https://social.msdn.microsoft.com/Forums/office/en-US/c414ef6d-fa9a-406c-9644-e479e7e72d0b/addressof-function-data-type-mismatch?forum=accessdev
'
' Code names and comments updated by Barbara Bazemore Kiszka for clarity
' November 2020
'////////////////////////////////////////////////////////////////////

'API functions to be used
Private Declare PtrSafe Function CallNextHookEx Lib "user32" (ByVal hHook As LongPtr, _
ByVal ncode As LongPtr, ByVal wParam As LongPtr, lParam As Any) As LongPtr

Private Declare PtrSafe Function GetModuleHandle Lib "kernel32" Alias "GetModuleHandleA" (ByVal lpModuleName As String) As LongPtr

Private Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" _
(ByVal idHook As LongPtr, ByVal lpfn As LongPtr, ByVal hmod As LongPtr, ByVal dwThreadId As LongPtr) As LongPtr

Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As LongPtr) As LongPtr

Private Declare PtrSafe Function SendDlgItemMessage Lib "user32" Alias "SendDlgItemMessageA" _
(ByVal hDlg As LongPtr, ByVal nIDDlgItem As LongPtr, ByVal wMsg As LongPtr, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr

Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As LongPtr, _
ByVal lpClassName As String, ByVal nMaxCount As LongPtr) As LongPtr

Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" () As LongPtr

' 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 hMaskedInputHook As LongPtr

' This is a helper function. You probably don't want to call this directly.
Public Function AddMaskToInputBox(ByVal lngCode As LongPtr, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
    Dim RetVal
    Dim strClassName As String, lngBuffer As LongPtr
    
    ' If we are not active, skip this step
    If lngCode < HC_ACTION Then
        AddMaskToInputBox = CallNextHookEx(hMaskedInputHook, lngCode, wParam, lParam)
        Exit Function
    End If
    
    ' Initialize the string where we are going to check for the InputBox class
    strClassName = String$(256, " ")
    lngBuffer = 255
    
    If lngCode = HCBT_ACTIVATE Then        'A window has been activated
        RetVal = GetClassName(wParam, strClassName, lngBuffer)
        
        ' Check to see if this is the InputBox window
        If Left$(strClassName, RetVal) = "#32770" Then
            ' This changes the edit control so that it displays 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 hMaskedInputHook, lngCode, wParam, lParam
End Function

' Call this function to display Password prompt dialog
Public Function PasswordInputBox(Prompt, Title) As String
    Dim lngModHwnd  As LongPtr, lngThreadID As LongPtr
    
    ' Set up a Windows hook so the the masked chars are set up the next time we display an InputBox
    lngThreadID = GetCurrentThreadId
    lngModHwnd = GetModuleHandle(vbNullString)
    
    hMaskedInputHook = SetWindowsHookEx(WH_CBT, AddressOf AddMaskToInputBox, lngModHwnd, lngThreadID)
    
    ' Prompt the user for the password, masking the input characters
    PasswordInputBox = InputBox(Prompt, Title)
    
    ' Go back to normal InputBox behavior
    UnhookWindowsHookEx hMaskedInputHook
End Function

score 3 · Answer 3 · answered Jun 14 '18 at 06:22

It is possible to mask InputBox text in vba.

    Option Explicit

'////////////////////////////////////////////////////////////////////
'Password masked inputbox
'Allows you to hide characters entered in a VBA Inputbox.
'
'Code written by Daniel Klann
'March 2003
'////////////////////////////////////////////////////////////////////


'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

Function InputBoxDK(Prompt, Title) As String
    Dim lngModHwnd As Long, lngThreadID As Long

    lngThreadID = GetCurrentThreadId
    lngModHwnd = GetModuleHandle(vbNullString)

    hHook = SetWindowsHookEx(WH_CBT, AddressOf NewProc, lngModHwnd, lngThreadID)

    InputBoxDK = InputBox(Prompt, Title)
    UnhookWindowsHookEx hHook

End Function

score 2 · Answer 4 · answered Feb 03 '20 at 12:53

I know this is an old question, but Bhanu Pratap's code from Daniel Khan required updating before I could use it in Office 365. That code was written for a 32-bit Excel environment, but the modern Excel uses a 64-bit environment and returns errors on his code. I almost thought I would not be able to salvage his code! Since I finally found out how to make it work, I wanted to post it here in-case anyone else also finds it useful.

Excel 64-bit Code:

Option Explicit

'////////////////////////////////////////////////////////////////////
'Password masked inputbox
'Allows you to hide characters entered in a VBA Inputbox.
'
'Code written by Daniel Klann
'March 2003
'
'Code updated by Joseph for Excel 64-bit Environments
'February 2020
'PtrSafe added & Long to PtrLong conversion
'see https://social.msdn.microsoft.com/Forums/office/en-US/c414ef6d-fa9a-406c-9644-e479e7e72d0b/addressof-function-data-type-mismatch?forum=accessdev
'////////////////////////////////////////////////////////////////////

'API functions to be used
Private Declare PtrSafe Function CallNextHookEx Lib "user32" (ByVal hHook As LongPtr, _
    ByVal ncode As LongPtr, ByVal wParam As LongPtr, lParam As Any) As LongPtr

Private Declare PtrSafe Function GetModuleHandle Lib "kernel32" Alias "GetModuleHandleA" (ByVal lpModuleName As String) As LongPtr

Private Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" _
    (ByVal idHook As LongPtr, ByVal lpfn As LongPtr, ByVal hmod As LongPtr, ByVal dwThreadId As LongPtr) As LongPtr

Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As LongPtr) As LongPtr

Private Declare PtrSafe Function SendDlgItemMessage Lib "user32" Alias "SendDlgItemMessageA" _
(ByVal hDlg As LongPtr, ByVal nIDDlgItem As LongPtr, ByVal wMsg As LongPtr, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr

Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As LongPtr, _
ByVal lpClassName As String, ByVal nMaxCount As LongPtr) As LongPtr

Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" () As LongPtr

'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 LongPtr


Public Function NewProc(ByVal lngCode As LongPtr, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
    Dim RetVal
    Dim strClassName As String, lngBuffer As LongPtr

    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

Public Function InputBoxDK(Prompt, Title) As String
    Dim lngModHwnd As LongPtr, lngThreadID As LongPtr

    lngThreadID = GetCurrentThreadId
    lngModHwnd = GetModuleHandle(vbNullString)

    hHook = SetWindowsHookEx(WH_CBT, AddressOf NewProc, lngModHwnd, lngThreadID)

    InputBoxDK = InputBox(Prompt, Title)
    UnhookWindowsHookEx hHook
End Function

This should be the accepted answer. Note it's a minor point but ``Asc("•")`` changed to ``Asc("?")`` in my computer. Using an ascii character might be better. — kamocyc, Jul 21 '20 at 03:20

score 0 · Answer 5 · answered Jan 28 '15 at 11:14

0

ImputBox does not support masking, so you can't hide characters in then. You need to use some like TextBox with white characters or something like this.

answered Jan 28 '15 at 11:14

Liniel

719
1
6
15

Excel VBA Input box

5 Answers5

Linked

Related