iT邦幫忙

DAY 13
2

Excel VBA 的眉眉角角系列 第 13

Excel VBA 的眉眉角角Day13: InputBox輸入密碼字串顯示*字串

  • 分享至 

  • xImage
  •  

在使用InputBox輸入資料時,最麻煩的就是要輸入機密性的資料,因為Excel VBA 的InputBox並沒有選項可以遮蔽目前輸入的字元,例如輸入密碼。還好這也可透過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






Public Function InputBoxDK(Prompt, Optional Title, Optional Default, Optional Xpos, _
                        Optional Ypos, Optional HelpFile, Optional Context) 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, Default, Xpos, Ypos, HelpFile, Context)
    UnhookWindowsHookEx hHook
 
End Function  'Hope someone can use it!

此函數是由Daniel Klann於2003年撰寫,主要是呼叫了Windows系統的一些核心元件,以提供所需的功能。主要函數為InputBoxDK()。

以下程式碼用來展示如何套用InputBoxDK()這函數:

Sub InputBoxDK_TEST()
    Dim strAdminPWord As String
    strAdminPWord = InputBoxDK("請輸入密碼:", "注意!")
    If strAdminPWord = "password" Then
        MsgBox "密碼正確! ", vbOKOnly, "恭喜!"
    Else
        MsgBox "密碼錯誤!", , "殘念~"
        Exit Sub '離開程式
    End If
    '密碼輸入正確後
    '要執行的程式放在這裡
End Sub

我們可以看到,這與原本的InputBox用法完全一樣,但讓妳輸入機密資料時,免去被偷窺的困擾,希望對各位有幫助!

參考來源:http://www.tek-tips.com/faqs.cfm?fid=4617


上一篇
Excel VBA 的眉眉角角Day12:修改狀態列顯示資料
下一篇
Excel VBA 的眉眉角角Day14:判斷InputBox函數是按了確定還是取消
系列文
Excel VBA 的眉眉角角30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 則留言

0
jiusishuai
iT邦新手 5 級 ‧ 2017-02-09 00:08:06

請問我COPY第一組CODE(Code written by Daniel Klann on March 2003)
Private Declare Function 發生錯誤, 是什麼原因呢?

看更多先前的回應...收起先前的回應...
Andy Chiu iT邦研究生 2 級 ‧ 2017-02-11 09:06:13 檢舉

請問錯誤訊息為何?

Andy Chiu iT邦研究生 2 級 ‧ 2017-02-15 08:58:06 檢舉

剛剛又測試了一下,在Office 2003的環境使用正常,不知道您的環境是?

'API functions to be used 這註解下面的function都反紅
錯誤訊息:
"編譯錯誤: 不正確的內部程序"/images/emoticon/emoticon20.gif

請問需要先設定工具->引用項目嗎?

Andy Chiu iT邦研究生 2 級 ‧ 2017-04-18 11:31:50 檢舉

不用耶,把程式碼放到模組內就可以使用了,請您下載範本看一下,有問題再跟我說:
InputBoxDK.xls

jiusishuai安裝的Office可能是64位元版。

我要留言

立即登入留言