Lock-UnLock VBAProjects Programmatically without SendKeys

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
9,797
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

I Have recently written this piece of code so I just thought I would post it here for future reference should anyone be looking for vba code to lock\unlock a vbaproject programmatically without needing to use the unreliable SendKeys method.

workbook example

This code requires that the excel macro security setting "Trust access to the vba project object model" be checked... .Also, it is worth mentioning that this code is language-specific as it reads the VBE dialog captions. So this code won't work in Non-English editions of excel but, should be easy to adapt with some small changes.

The code was written and tested in excel 2016-64bit.

Code goes in a Standard Module:
VBA Code:
Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As LongPtr, ByVal hmod As LongPtr, ByVal dwThreadId As Long) As LongPtr
    Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" (ByVal hhk As LongPtr) As Long
    Private Declare PtrSafe Function CallNextHookEx Lib "user32" (ByVal hHook As LongPtr, ByVal nCode As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" () As Long
    Private Declare PtrSafe Function GetDlgItem Lib "user32" (ByVal hDlg As LongPtr, ByVal nIDDlgItem As Long) As LongPtr
    Private Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
    Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As Long
    Private Declare PtrSafe Function GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As LongPtr, ByVal wFlag As Long) As LongPtr
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hwnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
    Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As String) As LongPtr
    Private Declare PtrSafe Function SetFocus Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Private Declare PtrSafe Function SetWindowPos Lib "user32" (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
    Private Declare PtrSafe Function SetActiveWindow Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    
    Private lHook As LongPtr
#Else
    
    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 hhk As Long) As Long
    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 GetCurrentThreadId Lib "kernel32" () As Long
    Private Declare Function GetDlgItem Lib "user32" (ByVal hDlg As Long, ByVal nIDDlgItem As Long) As Long
    Private Declare Function GetActiveWindow Lib "user32" () 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 GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
    Private Declare Function GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As Long, ByVal wFlag As Long) As Long
    Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hwnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As Long
    Private Declare Function SetFocus Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Private Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
    Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
    Private Declare Function SetActiveWindow Lib "user32" (ByVal hwnd As Long) As Long
    
    Private lHook As Long
#End If

Private sWinClassName As String, sWorkbookName As String, sPassword As String



Public Property Let LockVBProject(ByVal WorkbookName As String, ByVal Password As String, ByVal bLock As Boolean)

    #If VBA7 Then
        Dim hwnd As LongPtr
    #Else
        Dim hwnd As Long
    #End If
    
    Const WH_CBT = 5

    On Error GoTo errHandler

    hwnd = GetActiveWindow
    
    With Application.VBE
        Set .ActiveVBProject = Application.Workbooks(WorkbookName).VBProject
        If bLock Then
            If .ActiveVBProject.Protection = 0 Then
                sWinClassName = "VBAProject - Project Properties"
                sWorkbookName = WorkbookName
            Else
                MsgBox "VBProect already locked": Exit Property
            End If
        Else
            If .ActiveVBProject.Protection Then
                sWinClassName = "VBAProject Password"
            Else
                MsgBox "VBProect already unlocked": Exit Property
            End If
        End If
    End With
    
    sPassword = Password
    lHook = SetWindowsHookEx(WH_CBT, AddressOf Catch_DlgBox_Activation, 0, GetCurrentThreadId)
    Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
    
    If hwnd = Application.hwnd Then
        SetActiveWindow Application.hwnd
    End If
    
Exit Property

errHandler:
    Call UnHook
    MsgBox "Runtime Error : " & Err.Number & vbCr & vbCr & Err.Description, vbExclamation
    
End Property



#If VBA7 Then
    Private Function Catch_DlgBox_Activation(ByVal idHook As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
        Dim hwnd As LongPtr
#Else
    Private Function Catch_DlgBox_Activation(ByVal idHook As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
        Dim hwnd As Long
#End If

    Const HCBT_ACTIVATE = 5
    Const SWP_HIDEWINDOW = &H80

    Dim sBuff As String * 256, lRet As Long
    
    If idHook = HCBT_ACTIVATE Then
    lRet = GetClassName(wParam, sBuff, 256)
    If Left(sBuff, lRet) = "#32770" Then
        sBuff = ""
        lRet = GetWindowText(wParam, sBuff, 256)
        If Left(sBuff, lRet) = sWinClassName Then
            Call UnHook
            SetWindowPos wParam, 0, 0, 0, 0, 0, SWP_HIDEWINDOW
            Call SetTimer(Application.hwnd, wParam, 0, AddressOf Protect_UnProtect_Routine)
        End If
    End If
    End If
    
    Catch_DlgBox_Activation = CallNextHookEx(lHook, idHook, ByVal wParam, ByVal lParam)
 
End Function


Private Sub UnHook()
    UnhookWindowsHookEx lHook
End Sub


#If VBA7 Then
Private Sub Protect_UnProtect_Routine(ByVal hwnd As LongPtr, ByVal uMsg As Long, ByVal nIDEvent As LongPtr, ByVal dwTimer As Long)
    Dim hCurrentDlg As LongPtr, hwndSysTab As LongPtr
#Else
Private Sub Protect_UnProtect_Routine(ByVal hwnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
    Dim hCurrentDlg As Long, hwndSysTab As Long
#End If

    Const TCM_FIRST = &H1300
    Const TCM_SETCURSEL = (TCM_FIRST + 12)
    Const TCM_SETCURFOCUS = (TCM_FIRST + 48)
    Const EM_SETMODIFY = &HB9
    Const BM_SETCHECK = &HF1
    Const BST_CHECKED = &H1
    Const BM_GETCHECK = &HF0
    Const BM_CLICK = &HF5
    Const WM_SETTEXT = &HC
    Const WH_CBT = 5
    Const GW_CHILD = 5
    
    On Error GoTo errHandler
    
    Call KillTimer(Application.hwnd, nIDEvent)
    
    hCurrentDlg = nIDEvent
    
    If sWinClassName = "VBAProject - Project Properties" Then
    
        hwndSysTab = FindWindowEx(hCurrentDlg, 0, "SysTabControl32", vbNullString)
        Call SendMessage(hwndSysTab, TCM_SETCURFOCUS, 1, 0)
        Call SendMessage(hwndSysTab, TCM_SETCURSEL, 1, 0)
        
        If SendMessage(GetDlgItem(GetNextWindow(hCurrentDlg, GW_CHILD), &H1557), BM_GETCHECK, 0, 0) = 0 Then
            Call SendMessage(GetDlgItem(GetNextWindow(hCurrentDlg, GW_CHILD), &H1557), BM_SETCHECK, BST_CHECKED, 0)
            Call SendMessage(GetDlgItem(GetNextWindow(hCurrentDlg, GW_CHILD), &H1555), WM_SETTEXT, 0, sPassword)
            Call SendMessage(GetDlgItem(GetNextWindow(hCurrentDlg, GW_CHILD), &H1555), EM_SETMODIFY, True, 0)
            Call SendMessage(GetDlgItem(GetNextWindow(hCurrentDlg, GW_CHILD), &H1556), WM_SETTEXT, 0, sPassword)
            Call SendMessage(GetDlgItem(GetNextWindow(hCurrentDlg, GW_CHILD), &H1556), EM_SETMODIFY, True, 0)
        End If
        
        Call SendMessage(GetDlgItem(hCurrentDlg, &H1), BM_CLICK, 0, 0)
        Call Application.OnTime(Now, "SaveVBProjectChanges")
        
    ElseIf sWinClassName = "VBAProject Password" Then
    
        Call SendMessage(GetDlgItem(hCurrentDlg, &H155E), WM_SETTEXT, 0, sPassword)
        Call SendMessage(GetDlgItem(hCurrentDlg, &H155E), EM_SETMODIFY, True, 0)
        lHook = SetWindowsHookEx(WH_CBT, AddressOf Catch_DlgBox_Creation, 0, GetCurrentThreadId)
        Call SendMessage(GetDlgItem(hCurrentDlg, &H1), BM_CLICK, 0, 0)
        Call Application.OnTime(Now, "UnHook")
        
    End If
    
    Exit Sub
    
errHandler:
    Call UnHook
    MsgBox "Runtime Error : " & Err.Number & vbCr & vbCr & Err.Description, vbExclamation


End Sub


#If VBA7 Then
    Private Function Catch_DlgBox_Creation(ByVal idHook As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
#Else
    Private Function Catch_DlgBox_Creation(ByVal idHook As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
#End If

    Const HCBT_CREATEWND = 3
    Dim sBuff As String * 256, lRet As Long
    
    If idHook = HCBT_CREATEWND Then
        lRet = GetClassName(wParam, sBuff, 256)
        If Left(sBuff, lRet) = "#32770" Then
            Catch_DlgBox_Creation = -1
            Exit Function
        End If
    End If
    
    Catch_DlgBox_Creation = CallNextHookEx(lHook, idHook, ByVal wParam, ByVal lParam)

End Function

Private Sub SaveVBProjectChanges()
    On Error Resume Next
    Application.EnableEvents = False
        Workbooks(sWorkbookName).Save
    Application.EnableEvents = True
End Sub



Code usage examples :
VBA Code:
Sub Lock_Example()

    'To lock the vbproject. (change workbook name as required)
        LockVBProject(WorkbookName:="MyTestBook.xls", Password:="1234") = True

End Sub


VBA Code:
Sub UnLock_Example()

    'To un-lock the vbproject. (change workbook name as required)
        LockVBProject(WorkbookName:="MyTestBook.xls", Password:="1234") = False

End Sub
 
@pmajax

Yes. That awful beep sound is due to the fact that I am aborting the creation of the "#32770" window.
It is not easy to prevent that sound unless we temporarly mute the system sound\speakers as I did in this thread

I have incorporated some part of that CoreAudio code into this Lock\UnLock project so you should now not hear the annoying beep sound.

I have also improved the original lock\unlock code so it can now :
1- Optionally Notify the user (added third optional argument).
2- Lock\Unloc operations can be performed from the excel UI.
3- Changing the vbproject password is saved and preserved.

@lordy888
Try the new code in this post ... Also, remember that the vbproject will need to have at least one module. So add one module to the newly created workbook before saving and closing.


File Demo [Improved Version]
Lock_Unlock_VBAProject_API _IMPROVED_VERSION.xlsm


Here is the code :

1- In a Standard Module:
VBA Code:
Option Explicit

Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(0& To 7&) As Byte
End Type

#If VBA7 Then
    Private Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As LongPtr, ByVal hMod As LongPtr, ByVal dwThreadId As Long) As LongPtr
    Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" (ByVal hhk As LongPtr) As Long
    Private Declare PtrSafe Function CallNextHookEx Lib "user32" (ByVal hHook As LongPtr, ByVal ncode As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" () As Long
    Private Declare PtrSafe Function GetDlgItem Lib "user32" (ByVal hDlg As LongPtr, ByVal nIDDlgItem As Long) As LongPtr
    Private Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
    Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As Long
    Private Declare PtrSafe Function GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As LongPtr, ByVal wFlag As Long) As LongPtr
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hwnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
    Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As Long
    Private Declare PtrSafe Function SendMessageByString Lib "user32.dll" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As String) As Long
    Private Declare PtrSafe Function SetFocus Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Private Declare PtrSafe Function SetWindowPos Lib "user32" (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, ByVal x As Long, ByVal y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
    Private Declare PtrSafe Function SetActiveWindow Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Private Declare PtrSafe Sub SetLastError Lib "Kernel32.dll" (ByVal dwErrCode As Long)
    Private Declare PtrSafe Function DispCallFunc Lib "OleAut32.dll" (ByVal pvInstance As LongPtr, ByVal offsetinVft As LongPtr, ByVal CallConv As Long, ByVal retTYP As Integer, ByVal paCNT As Long, ByRef paTypes As Integer, ByRef paValues As LongPtr, ByRef retVAR As Variant) As Long
    Private Declare PtrSafe Function IIDFromString Lib "ole32.dll" (ByVal lpsz As LongPtr, ByRef lpiid As Any) As Long
    Private Declare PtrSafe Function CoCreateInstance Lib "ole32" (ByRef rclsid As GUID, ByVal pUnkOuter As LongPtr, ByVal dwClsContext As Long, ByRef riid As GUID, ByRef ppv As LongPtr) As Long
    Private Declare PtrSafe Function CLSIDFromString Lib "ole32" (ByVal OleStringCLSID As LongPtr, ByRef cGUID As Any) As Long
#Else
    Private Enum LongPtr
        [_]
    End Enum
    Private Declare Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As LongPtr, ByVal hMod As LongPtr, ByVal dwThreadId As Long) As LongPtr
    Private Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hhk As LongPtr) As Long
    Private Declare Function CallNextHookEx Lib "user32" (ByVal hHook As LongPtr, ByVal ncode As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long
    Private Declare Function GetDlgItem Lib "user32" (ByVal hDlg As LongPtr, ByVal nIDDlgItem As Long) As LongPtr
    Private Declare Function GetActiveWindow Lib "user32" () As LongPtr
    Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As Long
    Private Declare Function GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As LongPtr, ByVal wFlag As Long) As LongPtr
    Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hwnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As Long
    Private Declare Function SetFocus Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Private Declare Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Private Declare Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, ByVal x As Long, ByVal y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
    Private Declare Function SetActiveWindow Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Private Declare Sub SetLastError Lib "Kernel32.dll" (ByVal dwErrCode As Long)
    Private Declare Function DispCallFunc Lib "OleAut32.dll" (ByVal pvInstance As LongPtr, ByVal offsetinVft As LongPtr, ByVal CallConv As Long, ByVal retTYP As Integer, ByVal paCNT As Long, ByRef paTypes As Integer, ByRef paValues As LongPtr, ByRef retVAR As Variant) As Long
    Private Declare Function IIDFromString Lib "ole32.dll" (ByVal lpsz As LongPtr, ByRef lpiid As Any) As Long
    Private Declare Function CoCreateInstance Lib "ole32" (ByRef rclsid As GUID, ByVal pUnkOuter As LongPtr, ByVal dwClsContext As Long, ByRef riid As GUID, ByRef ppv As LongPtr) As Long
    Private Declare Function CLSIDFromString Lib "ole32" (ByVal OleStringCLSID As LongPtr, ByRef cGUID As Any) As Long
#End If

Private lHook As LongPtr, oProject As Object, sWinClassName As String, _
        sWorkbookName As String, sPassword As String, bNotify As Boolean, bHooked As Boolean


Public Property Let LockVBProject( _
    ByVal WorkbookName As String, _
    ByVal Password As String, _
    Optional ByVal NotifyUser As Boolean, _
    ByVal bLock As Boolean _
)
    Const WH_CBT = 5&
    Dim hwnd As LongPtr
 
    If bHooked Then Exit Property
 
    hwnd = GetActiveWindow: bNotify = NotifyUser:  bHooked = True
 
    On Error GoTo ErrHandler
    With Application.VBE
        Set .ActiveVBProject = Application.Workbooks(WorkbookName).VBProject
        Set oProject = .ActiveVBProject
        If bLock Then
            If .ActiveVBProject.Protection = 0 Then
                sWinClassName = "VBAProject - Project Properties"
                sWorkbookName = WorkbookName
            Else
                MsgBox "VBProect already locked.": Exit Property
            End If
        Else
            If .ActiveVBProject.Protection Then
                sWinClassName = "VBAProject Password"
            Else
                MsgBox "VBProect already unlocked.": Exit Property
            End If
        End If
    End With
    sPassword = Password
    lHook = SetWindowsHookEx(WH_CBT, AddressOf Catch_DlgBox_Activation, 0, GetCurrentThreadId)
    Application.VBE.CommandBars(1&).FindControl(ID:=2578, recursive:=True).Execute
    bHooked = False
Exit Property

ErrHandler:
    Call UnHook
    bHooked = False
    MuteSpeakers False
    If Err.Number = 9 Then
        MsgBox "Error : " & Err.Number & vbNewLine & vbNewLine & "Invalid Project Name.", vbExclamation
    Else
        MsgBox "Runtime Error : " & Err.Number & vbCr & vbCr & Err.Description, vbExclamation
    End If
End Property

Private Function Catch_DlgBox_Activation( _
    ByVal idHook As Long, _
    ByVal wParam As LongPtr, _
    ByVal lParam As LongPtr _
) As LongPtr
 
    Const HCBT_ACTIVATE = 5, SWP_HIDEWINDOW = &H80
    Dim sBuff As String * 256, lRet As Long
    Dim hwnd As LongPtr

    If idHook = HCBT_ACTIVATE Then
        lRet = GetClassName(wParam, sBuff, 256&)
        If Left(sBuff, lRet) = "#32770" Then
            sBuff = ""
            lRet = GetWindowText(wParam, sBuff, 256&)
            If Left(sBuff, lRet) = sWinClassName Then
                Call UnHook
                SetWindowPos wParam, 0, 0&, 0&, 0&, 0&, SWP_HIDEWINDOW
                Call SetTimer(Application.hwnd, wParam, 0, AddressOf Protect_UnProtect_Routine)
            End If
        End If
    End If
    Catch_DlgBox_Activation = CallNextHookEx(lHook, idHook, ByVal wParam, ByVal lParam)
 
End Function

Private Sub UnHook()
    Call UnhookWindowsHookEx(lHook)
End Sub

Private Sub UnHook2()
    Call UnHook
    MuteSpeakers False
    If bNotify Then
        If IsProjectUnLocked Then
            MsgBox "Project successfully unlocked!", vbInformation
        Else
            MsgBox "Failed to unlock the VBProject!" & vbNewLine & vbNewLine & _
                   "Passwords are Case-Sensitive ... Make sure the provided Password is correct.", vbExclamation
        End If
    End If
End Sub

Private Function IsProjectUnLocked() As Boolean
    IsProjectUnLocked = CBool(oProject.Protection = 0)
End Function

Private Sub Protect_UnProtect_Routine( _
    ByVal hwnd As LongPtr, _
    ByVal uMsg As Long, _
    ByVal nIDEvent As LongPtr, _
    ByVal dwTimer As Long _
)
    Const TCM_FIRST = &H1300
    Const TCM_SETCURSEL = (TCM_FIRST + 12&)
    Const TCM_SETCURFOCUS = (TCM_FIRST + 48&)
    Const EM_SETMODIFY = &HB9
    Const BM_SETCHECK = &HF1
    Const BST_CHECKED = &H1
    Const BM_GETCHECK = &HF0
    Const BM_CLICK = &HF5
    Const WM_SETTEXT = &HC
    Const WH_CBT = 5&
    Const GW_CHILD = 5&
    Dim hCurrentDlg As LongPtr, hwndSysTab As LongPtr
    Dim sBuff As String * 256, lRet As Long
 
    On Error GoTo ErrHandler
    Call KillTimer(Application.hwnd, nIDEvent)
    hCurrentDlg = nIDEvent
    If sWinClassName = "VBAProject - Project Properties" Then
        hwndSysTab = FindWindowEx(hCurrentDlg, 0, "SysTabControl32", vbNullString)
        Call SendMessage(hwndSysTab, TCM_SETCURFOCUS, 1, ByVal 0&)
        Call SendMessage(hwndSysTab, TCM_SETCURSEL, 1, ByVal 0&)
        Call SendMessage(GetDlgItem(GetNextWindow(hCurrentDlg, GW_CHILD), &H1557), BM_SETCHECK, BST_CHECKED, ByVal 0&)
        Call SendMessageByString(GetDlgItem(GetNextWindow(hCurrentDlg, GW_CHILD), &H1555), WM_SETTEXT, 0, sPassword)
        Call SendMessage(GetDlgItem(GetNextWindow(hCurrentDlg, GW_CHILD), &H1555), EM_SETMODIFY, True, ByVal 0&)
        Call SendMessageByString(GetDlgItem(GetNextWindow(hCurrentDlg, GW_CHILD), &H1556), WM_SETTEXT, 0, sPassword)
        Call SendMessage(GetDlgItem(GetNextWindow(hCurrentDlg, GW_CHILD), &H1556), EM_SETMODIFY, True, ByVal 0&)
        Call SendMessage(GetDlgItem(hCurrentDlg, &H1), BM_CLICK, 0, ByVal 0&)
        Call Application.OnTime(Now, "SaveVBProjectChanges")
    ElseIf sWinClassName = "VBAProject Password" Then
        MuteSpeakers True 'temporarly mute the system sounds\speakers to avoid awful sound when running Catch_DlgBox_Creation
        If SendMessageByString(GetDlgItem(hCurrentDlg, &H155E), WM_SETTEXT, 0, sPassword) Then
            Call SendMessage(GetDlgItem(hCurrentDlg, &H155E), EM_SETMODIFY, True, ByVal 0&)
            lHook = SetWindowsHookEx(WH_CBT, AddressOf Catch_DlgBox_Creation, 0, GetCurrentThreadId)
            lRet = GetWindowText(GetActiveWindow, sBuff, 256&)
            If Left(sBuff, lRet) = "VBAProject Password" Then
                Call SendMessage(GetDlgItem(hCurrentDlg, &H1), BM_CLICK, 0, ByVal 0&)
                Call SendMessage(GetDlgItem(GetActiveWindow, &H2), BM_CLICK, 0, ByVal 0&)
            Else
                Call SendMessage(GetDlgItem(hCurrentDlg, &H1), BM_CLICK, 0, ByVal 0&)
            End If
            Call Application.OnTime(Now + TimeSerial(0, 0, 3), "UnHook2")  'wait 3 secs before unmuting the speakers.
        End If
    End If
    Exit Sub
ErrHandler:
    Call UnHook2
    MsgBox "Runtime Error : " & Err.Number & vbCr & vbCr & Err.Description, vbExclamation
End Sub

Private Function Catch_DlgBox_Creation( _
    ByVal idHook As Long, _
    ByVal wParam As LongPtr, _
    ByVal lParam As LongPtr _
) As LongPtr

    Const HCBT_CREATEWND = 3&
    Dim sBuff As String * 256, lRet As Long
 
    If idHook = HCBT_CREATEWND Then
        lRet = GetClassName(wParam, sBuff, 256)
        If Left(sBuff, lRet) = "#32770" Then
            Catch_DlgBox_Creation = -1
            Exit Function
        End If
    End If
    Catch_DlgBox_Creation = CallNextHookEx(lHook, idHook, ByVal wParam, ByVal lParam)
End Function

Private Sub SaveVBProjectChanges()
    On Error Resume Next
    Application.EnableEvents = False
        Workbooks(sWorkbookName).Save
    Application.EnableEvents = True
End Sub

Private Sub MuteSpeakers(Optional ByVal bMute As Boolean = True)

    Const CLSID_MMDeviceEnumerator = "{BCDE0395-E52F-467C-8E3D-C4579291692E}"
    Const IID_IMMDeviceEnumerator = "{A95664D2-9614-4F35-A746-DE8DB63617E6}"
    Const IID_IAudioEndpointVolume = "{5CDF2C82-841E-4546-9722-0CF74078229A}"
    Const IID_NULL = "{00000000-0000-0000-0000-000000000000}"
    Const CLSCTX_INPROC_SERVER = 1&
    Const CC_STDCALL = 4&
 
    #If Win64 Then
        Const NULL_PTR = 0^
        Const PTR_SIZE = 8&
    #Else
        Const NULL_PTR = 0&
        Const PTR_SIZE = 4&
    #End If
 
    Dim tClsID As GUID, tIID As GUID
    Dim pDeviceEnumerator As LongPtr, pdefaultDevice As LongPtr, pIAudioEndpointVolume As LongPtr
    Dim eRender As Long, eMultimedia As Long
    Dim lRet As Long

    lRet = CLSIDFromString(StrPtr(CLSID_MMDeviceEnumerator), tClsID)
    lRet = IIDFromString(StrPtr(IID_IMMDeviceEnumerator), tIID)

    'Create an enumerator for the audio endpoint devices
    lRet = CoCreateInstance(tClsID, NULL_PTR, CLSCTX_INPROC_SERVER, tIID, pDeviceEnumerator)
    If lRet Then MsgBox "Failed to get IMMDeviceEnumerator.": Exit Sub
 
    eRender = 0&: eMultimedia = 1&
    'IMMDeviceEnumerator::GetDefaultAudioEndpoint Method.
    lRet = vtblCall(pDeviceEnumerator, 4& * PTR_SIZE, vbLong, CC_STDCALL, eRender, eMultimedia, VarPtr(pdefaultDevice))
    If lRet Then MsgBox "Failed to get IMMDevice.": Exit Sub
 
    lRet = IIDFromString(StrPtr(IID_IAudioEndpointVolume), tIID)
    'IMMDevice::Activate Method.
    lRet = vtblCall(pdefaultDevice, 3& * PTR_SIZE, vbLong, CC_STDCALL, VarPtr(tIID), CLSCTX_INPROC_SERVER, 0&, VarPtr(pIAudioEndpointVolume))
    If lRet Then MsgBox "Failed to get IAudioEndpointVolume.": Exit Sub

    lRet = IIDFromString(StrPtr(IID_NULL), tIID)
    lRet = vtblCall(pIAudioEndpointVolume, 14& * PTR_SIZE, vbLong, CC_STDCALL, CLng(Abs(bMute)), VarPtr(tIID))
 
    'Release Interfaces.
    lRet = vtblCall(pIAudioEndpointVolume, 2& * PTR_SIZE, vbLong, CC_STDCALL)
    lRet = vtblCall(pdefaultDevice, 2& * PTR_SIZE, vbLong, CC_STDCALL)
    lRet = vtblCall(pDeviceEnumerator, 2& * PTR_SIZE, vbLong, CC_STDCALL)

End Sub


Private Function vtblCall( _
    ByVal InterfacePointer As LongPtr, _
    ByVal VTableOffset As Long, _
    ByVal FunctionReturnType As Long, _
    ByVal CallConvention As Long, _
    ParamArray FunctionParameters() As Variant _
) As Variant

    Dim vParamPtr() As LongPtr

    If InterfacePointer = 0& Or VTableOffset < 0& Then Exit Function
    If Not (FunctionReturnType And &HFFFF0000) = 0& Then Exit Function

    Dim pIndex As Long, pCount As Long
    Dim vParamType() As Integer
    Dim vRtn As Variant, vParams() As Variant

    vParams() = FunctionParameters()
    pCount = Abs(UBound(vParams) - LBound(vParams) + 1&)
    If pCount = 0& Then
        ReDim vParamPtr(0& To 0&)
        ReDim vParamType(0& To 0&)
    Else
        ReDim vParamPtr(0& To pCount - 1&)
        ReDim vParamType(0& To pCount - 1&)
        For pIndex = 0& To pCount - 1&
            vParamPtr(pIndex) = VarPtr(vParams(pIndex))
            vParamType(pIndex) = VarType(vParams(pIndex))
        Next
    End If

    pIndex = DispCallFunc(InterfacePointer, VTableOffset, CallConvention, FunctionReturnType, pCount, _
    vParamType(0&), vParamPtr(0&), vRtn)
    If pIndex = 0& Then
        vtblCall = vRtn
    Else
        SetLastError pIndex
    End If
End Function


2- Usage Example:
VBA Code:
Option Explicit

Sub Lock_Example()

    ' To lock the vbproject. [change workbook name as required]
        LockVBProject(WorkbookName:="MyTestBook.xlsm", Password:="1234") = True

End Sub

Sub UnLock_Example()

    ' To un-lock the vbproject. [change workbook name as required]
    ' Operation may take about 3 seconds.
        LockVBProject(WorkbookName:="MyTestBook.xlsm", Password:="1234", NotifyUser:=True) = False
End Sub


Note: Due to the fact that the code waits for the beep sound(s) to finish , the unlocking may take about 3 seconds.
This is done in the following line:
Call Application.OnTime(Now + TimeSerial(0, 0, 3), "UnHook2") 'wait 3 secs before unmuting the speakers
You may need to play a bit with the timing and adjust it to your specific needs.

Also, just like before, this code is language-dependant as it reads the VBE dialog captions but it can be easily adapted from English to other languages.

Hi Jaafar :love:
First of all, thank you for your answer and my whole gratitude for the update of your solution...
For the lovers of Chopin’s nocturnes, I can humbly suggest the following code that does not tickle your speakers.
I turn off the beep just 1 second, but not the sound.
And it works too if you listen Snoop Dogg...
For the rest, dear members, try for yourselves IoI
With passion !
Pierre ;)
PS: I promise AI can't do that !
VBA Code:
'
'enable/disable specific windows sounds system
'

Option Explicit

'---constants and types for access to the register
Private Const HKEY_CURRENT_USER As Long = &H80000001
Private Const KEY_SET_VALUE As Long = &H20006
Private Const KEY_QUERY_VALUE As Long = &H1
Private Const REG_SZ As Long = 1
Private Const ERROR_SUCCESS As Long = 0

#If VBA7 Then
'---64 bits
Private Declare PtrSafe Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long
Private Declare PtrSafe Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, ByVal lpData As String, lpcbData As Long) As Long
Private Declare PtrSafe Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, ByVal lpData As String, ByVal cbData As Long) As Long
Private Declare PtrSafe Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
#Else
'---32 bits
Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long
Private Declare Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, ByVal lpData As String, lpcbData As Long) As Long
Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, ByVal lpData As String, ByVal cbData As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
#End If

Sub test_regkey_sound_enable()
'
'Silence is golden IoI
'

Const key_names_serial As String = ".Default;SystemHand;SystemExclamation"
    
    Call regkey_sound_disable(key_names_serial)
    '
    'here magical vba project unlock by Jaafar
    '
    sleep_s 1
    Call regkey_sound_enable(key_names_serial)
End Sub

Function regkey_sound_enable(key_names_serial As String) As Boolean
'
'refresh the current keys value by their default value
'

Dim hkey_def As Long
Dim hkey_cur As Long
Dim resu_tem As Long
Dim deft_val As String
Dim buff_siz As Long
Dim succ_sta As Boolean
'
Dim key_names() As String
Dim key_name As String
Dim i As Integer

'---split
    key_names = Split(key_names_serial, ";")
    
    For i = LBound(key_names) To UBound(key_names)
'---extract the keyname
    key_name = key_names(i)
 
    succ_sta = False
        
'---open the .Default key
    resu_tem = RegOpenKeyEx(HKEY_CURRENT_USER, "AppEvents\Schemes\Apps\.Default\" & key_name & "\.Default", 0, KEY_QUERY_VALUE, hkey_def)
    If resu_tem <> ERROR_SUCCESS Then
    Debug.Print "Error when opening the key .Default for " & key_name
    regkey_sound_enable = False
    Exit Function
    End If
        
'---read the .Default key
    buff_siz = 255
    deft_val = String(buff_siz, vbNullChar)
    resu_tem = RegQueryValueEx(hkey_def, "", 0, REG_SZ, deft_val, buff_siz)
    If resu_tem = ERROR_SUCCESS Then
    deft_val = Left(deft_val, InStr(deft_val, vbNullChar) - 1)
    Else
    Debug.Print "Error while reading the value of .Default for " & key_name
    RegCloseKey hkey_def
    regkey_sound_enable = False
    Exit Function
    End If
'---close the .Default key
    RegCloseKey (hkey_def)

'---open the .Current key
    resu_tem = RegOpenKeyEx(HKEY_CURRENT_USER, "AppEvents\Schemes\Apps\.Default\" & key_name & "\.Current", 0, KEY_SET_VALUE, hkey_cur)
    If resu_tem <> ERROR_SUCCESS Then
    Debug.Print "Error when opening the key .Current for " & key_name
    regkey_sound_enable = False
    Exit Function
    End If
    
'---write the .Current value by .Default value
    resu_tem = RegSetValueEx(hkey_cur, "", 0, REG_SZ, deft_val, Len(deft_val) + 1)
    If resu_tem = ERROR_SUCCESS Then
    succ_sta = True
    Else
    Debug.Print "Error while updating .Current with the value of . Default for " & key_name
    regkey_sound_enable = False
    End If
'---close the .Current key
    RegCloseKey hkey_cur
    Next i
    
'---confirm
    regkey_sound_enable = succ_sta
End Function

Function regkey_sound_disable(key_names_serial As String) As Boolean
'
'clear the current keys value
'

Dim hkey_def As Long
Dim hkey_cur As Long
Dim resu_tem As Long
Dim deft_val As String
Dim buff_siz As Long
Dim succ_sta As Boolean
'
Dim key_names() As String
Dim key_name As String
Dim i As Integer

'---split
    key_names = Split(key_names_serial, ";")
    
    For i = LBound(key_names) To UBound(key_names)
'---extract the keyname
    key_name = key_names(i)
 
    succ_sta = False

'---open the .Current key
    resu_tem = RegOpenKeyEx(HKEY_CURRENT_USER, "AppEvents\Schemes\Apps\.Default\" & key_name & "\.Current", 0, KEY_SET_VALUE, hkey_cur)
    If resu_tem <> ERROR_SUCCESS Then
    Debug.Print "Error when opening the key .Current for " & key_name
    regkey_sound_disable = False
    Exit Function
    End If

'---clear the .Current key value
    resu_tem = RegSetValueEx(hkey_cur, "", 0, REG_SZ, "", 1)
    If resu_tem = ERROR_SUCCESS Then
    succ_sta = True
    Else
    Debug.Print "Error when the empty value is assigned to the key .Current for " & key_name
    End If
'---close the .Current key
    RegCloseKey hkey_cur
    Next i
    
'---confirm
    regkey_sound_disable = succ_sta
End Function

Sub sleep_s(Secondes As Long)
'
'timer in seconds (no api)
'

Dim timer_st As Long
Dim timer_nd As Long
    
    timer_st = Timer
    timer_nd = timer_st + Secondes
    Do Until Timer >= timer_nd
    DoEvents
    Loop
End Sub

Sub test_vbExclamation_nosound()
'
'
'
Const key_names_serial As String = ".Default;SystemHand;SystemExclamation"
    
    Call regkey_sound_disable(key_names_serial)
    '
    MsgBox "Message box", vbExclamation
    '
'    sleep_s 1
    Call regkey_sound_enable(key_names_serial)
End Sub

Sub test_vbExclamation_sound()
'
'
'
    MsgBox "Message box", vbExclamation
End Sub
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi Jaafar :love:
First of all, thank you for your answer and my whole gratitude for the update of your solution...
For the lovers of Chopin’s nocturnes, I can humbly suggest the following code that does not tickle your speakers.
I turn off the beep just 1 second, but not the sound.
And it works too if you listen Snoop Dogg...
For the rest, dear members, try for yourselves IoI
With passion !
Pierre ;)
PS: I promise AI can't do that !
VBA Code:
'
'enable/disable specific windows sounds system
'

Option Explicit

'---constants and types for access to the register
Private Const HKEY_CURRENT_USER As Long = &H80000001
Private Const KEY_SET_VALUE As Long = &H20006
Private Const KEY_QUERY_VALUE As Long = &H1
Private Const REG_SZ As Long = 1
Private Const ERROR_SUCCESS As Long = 0

#If VBA7 Then
'---64 bits
Private Declare PtrSafe Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long
Private Declare PtrSafe Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, ByVal lpData As String, lpcbData As Long) As Long
Private Declare PtrSafe Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, ByVal lpData As String, ByVal cbData As Long) As Long
Private Declare PtrSafe Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
#Else
'---32 bits
Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long
Private Declare Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, ByVal lpData As String, lpcbData As Long) As Long
Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, ByVal lpData As String, ByVal cbData As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
#End If

Sub test_regkey_sound_enable()
'
'Silence is golden IoI
'

Const key_names_serial As String = ".Default;SystemHand;SystemExclamation"
   
    Call regkey_sound_disable(key_names_serial)
    '
    'here magical vba project unlock by Jaafar
    '
    sleep_s 1
    Call regkey_sound_enable(key_names_serial)
End Sub

Function regkey_sound_enable(key_names_serial As String) As Boolean
'
'refresh the current keys value by their default value
'

Dim hkey_def As Long
Dim hkey_cur As Long
Dim resu_tem As Long
Dim deft_val As String
Dim buff_siz As Long
Dim succ_sta As Boolean
'
Dim key_names() As String
Dim key_name As String
Dim i As Integer

'---split
    key_names = Split(key_names_serial, ";")
   
    For i = LBound(key_names) To UBound(key_names)
'---extract the keyname
    key_name = key_names(i)
 
    succ_sta = False
       
'---open the .Default key
    resu_tem = RegOpenKeyEx(HKEY_CURRENT_USER, "AppEvents\Schemes\Apps\.Default\" & key_name & "\.Default", 0, KEY_QUERY_VALUE, hkey_def)
    If resu_tem <> ERROR_SUCCESS Then
    Debug.Print "Error when opening the key .Default for " & key_name
    regkey_sound_enable = False
    Exit Function
    End If
       
'---read the .Default key
    buff_siz = 255
    deft_val = String(buff_siz, vbNullChar)
    resu_tem = RegQueryValueEx(hkey_def, "", 0, REG_SZ, deft_val, buff_siz)
    If resu_tem = ERROR_SUCCESS Then
    deft_val = Left(deft_val, InStr(deft_val, vbNullChar) - 1)
    Else
    Debug.Print "Error while reading the value of .Default for " & key_name
    RegCloseKey hkey_def
    regkey_sound_enable = False
    Exit Function
    End If
'---close the .Default key
    RegCloseKey (hkey_def)

'---open the .Current key
    resu_tem = RegOpenKeyEx(HKEY_CURRENT_USER, "AppEvents\Schemes\Apps\.Default\" & key_name & "\.Current", 0, KEY_SET_VALUE, hkey_cur)
    If resu_tem <> ERROR_SUCCESS Then
    Debug.Print "Error when opening the key .Current for " & key_name
    regkey_sound_enable = False
    Exit Function
    End If
   
'---write the .Current value by .Default value
    resu_tem = RegSetValueEx(hkey_cur, "", 0, REG_SZ, deft_val, Len(deft_val) + 1)
    If resu_tem = ERROR_SUCCESS Then
    succ_sta = True
    Else
    Debug.Print "Error while updating .Current with the value of . Default for " & key_name
    regkey_sound_enable = False
    End If
'---close the .Current key
    RegCloseKey hkey_cur
    Next i
   
'---confirm
    regkey_sound_enable = succ_sta
End Function

Function regkey_sound_disable(key_names_serial As String) As Boolean
'
'clear the current keys value
'

Dim hkey_def As Long
Dim hkey_cur As Long
Dim resu_tem As Long
Dim deft_val As String
Dim buff_siz As Long
Dim succ_sta As Boolean
'
Dim key_names() As String
Dim key_name As String
Dim i As Integer

'---split
    key_names = Split(key_names_serial, ";")
   
    For i = LBound(key_names) To UBound(key_names)
'---extract the keyname
    key_name = key_names(i)
 
    succ_sta = False

'---open the .Current key
    resu_tem = RegOpenKeyEx(HKEY_CURRENT_USER, "AppEvents\Schemes\Apps\.Default\" & key_name & "\.Current", 0, KEY_SET_VALUE, hkey_cur)
    If resu_tem <> ERROR_SUCCESS Then
    Debug.Print "Error when opening the key .Current for " & key_name
    regkey_sound_disable = False
    Exit Function
    End If

'---clear the .Current key value
    resu_tem = RegSetValueEx(hkey_cur, "", 0, REG_SZ, "", 1)
    If resu_tem = ERROR_SUCCESS Then
    succ_sta = True
    Else
    Debug.Print "Error when the empty value is assigned to the key .Current for " & key_name
    End If
'---close the .Current key
    RegCloseKey hkey_cur
    Next i
   
'---confirm
    regkey_sound_disable = succ_sta
End Function

Sub sleep_s(Secondes As Long)
'
'timer in seconds (no api)
'

Dim timer_st As Long
Dim timer_nd As Long
   
    timer_st = Timer
    timer_nd = timer_st + Secondes
    Do Until Timer >= timer_nd
    DoEvents
    Loop
End Sub

Sub test_vbExclamation_nosound()
'
'
'
Const key_names_serial As String = ".Default;SystemHand;SystemExclamation"
   
    Call regkey_sound_disable(key_names_serial)
    '
    MsgBox "Message box", vbExclamation
    '
'    sleep_s 1
    Call regkey_sound_enable(key_names_serial)
End Sub

Sub test_vbExclamation_sound()
'
'
'
    MsgBox "Message box", vbExclamation
End Sub
Agreed. AI won't do that
 
Upvote 0
Hi Jaafar :love:
First of all, thank you for your answer and my whole gratitude for the update of your solution...
For the lovers of Chopin’s nocturnes, I can humbly suggest the following code that does not tickle your speakers.
I turn off the beep just 1 second, but not the sound.
And it works too if you listen Snoop Dogg...
For the rest, dear members, try for yourselves IoI
With passion !
Pierre ;)
PS: I promise AI can't do that !
VBA Code:
'
'enable/disable specific windows sounds system
'

Option Explicit

'---constants and types for access to the register
Private Const HKEY_CURRENT_USER As Long = &H80000001
Private Const KEY_SET_VALUE As Long = &H20006
Private Const KEY_QUERY_VALUE As Long = &H1
Private Const REG_SZ As Long = 1
Private Const ERROR_SUCCESS As Long = 0

#If VBA7 Then
'---64 bits
Private Declare PtrSafe Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long
Private Declare PtrSafe Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, ByVal lpData As String, lpcbData As Long) As Long
Private Declare PtrSafe Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, ByVal lpData As String, ByVal cbData As Long) As Long
Private Declare PtrSafe Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
#Else
'---32 bits
Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long
Private Declare Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, ByVal lpData As String, lpcbData As Long) As Long
Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, ByVal lpData As String, ByVal cbData As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
#End If

Sub test_regkey_sound_enable()
'
'Silence is golden IoI
'

Const key_names_serial As String = ".Default;SystemHand;SystemExclamation"
   
    Call regkey_sound_disable(key_names_serial)
    '
    'here magical vba project unlock by Jaafar
    '
    sleep_s 1
    Call regkey_sound_enable(key_names_serial)
End Sub

Function regkey_sound_enable(key_names_serial As String) As Boolean
'
'refresh the current keys value by their default value
'

Dim hkey_def As Long
Dim hkey_cur As Long
Dim resu_tem As Long
Dim deft_val As String
Dim buff_siz As Long
Dim succ_sta As Boolean
'
Dim key_names() As String
Dim key_name As String
Dim i As Integer

'---split
    key_names = Split(key_names_serial, ";")
   
    For i = LBound(key_names) To UBound(key_names)
'---extract the keyname
    key_name = key_names(i)
 
    succ_sta = False
       
'---open the .Default key
    resu_tem = RegOpenKeyEx(HKEY_CURRENT_USER, "AppEvents\Schemes\Apps\.Default\" & key_name & "\.Default", 0, KEY_QUERY_VALUE, hkey_def)
    If resu_tem <> ERROR_SUCCESS Then
    Debug.Print "Error when opening the key .Default for " & key_name
    regkey_sound_enable = False
    Exit Function
    End If
       
'---read the .Default key
    buff_siz = 255
    deft_val = String(buff_siz, vbNullChar)
    resu_tem = RegQueryValueEx(hkey_def, "", 0, REG_SZ, deft_val, buff_siz)
    If resu_tem = ERROR_SUCCESS Then
    deft_val = Left(deft_val, InStr(deft_val, vbNullChar) - 1)
    Else
    Debug.Print "Error while reading the value of .Default for " & key_name
    RegCloseKey hkey_def
    regkey_sound_enable = False
    Exit Function
    End If
'---close the .Default key
    RegCloseKey (hkey_def)

'---open the .Current key
    resu_tem = RegOpenKeyEx(HKEY_CURRENT_USER, "AppEvents\Schemes\Apps\.Default\" & key_name & "\.Current", 0, KEY_SET_VALUE, hkey_cur)
    If resu_tem <> ERROR_SUCCESS Then
    Debug.Print "Error when opening the key .Current for " & key_name
    regkey_sound_enable = False
    Exit Function
    End If
   
'---write the .Current value by .Default value
    resu_tem = RegSetValueEx(hkey_cur, "", 0, REG_SZ, deft_val, Len(deft_val) + 1)
    If resu_tem = ERROR_SUCCESS Then
    succ_sta = True
    Else
    Debug.Print "Error while updating .Current with the value of . Default for " & key_name
    regkey_sound_enable = False
    End If
'---close the .Current key
    RegCloseKey hkey_cur
    Next i
   
'---confirm
    regkey_sound_enable = succ_sta
End Function

Function regkey_sound_disable(key_names_serial As String) As Boolean
'
'clear the current keys value
'

Dim hkey_def As Long
Dim hkey_cur As Long
Dim resu_tem As Long
Dim deft_val As String
Dim buff_siz As Long
Dim succ_sta As Boolean
'
Dim key_names() As String
Dim key_name As String
Dim i As Integer

'---split
    key_names = Split(key_names_serial, ";")
   
    For i = LBound(key_names) To UBound(key_names)
'---extract the keyname
    key_name = key_names(i)
 
    succ_sta = False

'---open the .Current key
    resu_tem = RegOpenKeyEx(HKEY_CURRENT_USER, "AppEvents\Schemes\Apps\.Default\" & key_name & "\.Current", 0, KEY_SET_VALUE, hkey_cur)
    If resu_tem <> ERROR_SUCCESS Then
    Debug.Print "Error when opening the key .Current for " & key_name
    regkey_sound_disable = False
    Exit Function
    End If

'---clear the .Current key value
    resu_tem = RegSetValueEx(hkey_cur, "", 0, REG_SZ, "", 1)
    If resu_tem = ERROR_SUCCESS Then
    succ_sta = True
    Else
    Debug.Print "Error when the empty value is assigned to the key .Current for " & key_name
    End If
'---close the .Current key
    RegCloseKey hkey_cur
    Next i
   
'---confirm
    regkey_sound_disable = succ_sta
End Function

Sub sleep_s(Secondes As Long)
'
'timer in seconds (no api)
'

Dim timer_st As Long
Dim timer_nd As Long
   
    timer_st = Timer
    timer_nd = timer_st + Secondes
    Do Until Timer >= timer_nd
    DoEvents
    Loop
End Sub

Sub test_vbExclamation_nosound()
'
'
'
Const key_names_serial As String = ".Default;SystemHand;SystemExclamation"
   
    Call regkey_sound_disable(key_names_serial)
    '
    MsgBox "Message box", vbExclamation
    '
'    sleep_s 1
    Call regkey_sound_enable(key_names_serial)
End Sub

Sub test_vbExclamation_sound()
'
'
'
    MsgBox "Message box", vbExclamation
End Sub

@pmajax
Thanks for the code.

It is nice to be able shut the system sound up without sacrificing the rest of the sound sessions.;) :)

I personally always stay away from meddling with the user's registry as there is always the risk of not restoring the defaults, furthemore, the relevant reg keys are not always guaranteed to be in the same path.

But I want to thank you because this has spurked me to further explore the issue of not being able to easily control the system sound selectively via code and I am glad I have come up with the following new solution which is entirely based on the core audio modern interfaces (no messing with user registry or settings) :

Take a look here :
 
Upvote 0
@pmajax
Thanks for the code.

It is nice to be able shut the system sound up without sacrificing the rest of the sound sessions.;) :)

I personally always stay away from meddling with the user's registry as there is always the risk of not restoring the defaults, furthemore, the relevant reg keys are not always guaranteed to be in the same path.

But I want to thank you because this has spurked me to further explore the issue of not being able to easily control the system sound selectively via code and I am glad I have come up with the following new solution which is entirely based on the core audio modern interfaces (no messing with user registry or settings) :

Take a look here :
Hi Jaafar :love:
It's a great pleasure to read you and a great honor to receive your thanks !
My approach is that of resourcefulness, yours is academic and what I like is when the two come together to give the best of the untiring effervescence of our brains !
Of course it's cleaner not to touch the registry keys.
A thousand thanks for your innovative solution.
I will come back to you, I'm closing the work of a life, this should also make many forums happy... and I hope that a single life will allow me to finish on time !
Respect, You're a king (y)
Pierre;)
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,174
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top