CODEWITHSUNDEEP
windowsexcelshelldigital-signaturevba
ggv
ggv

Reputation: 109

How to invoke Add a Digital Signature dialog in Excel VBA

I want to write a simple Excel macro, that invokes Add Digital Signature dialog for the user. I do not want to add the signature itself, just to show the Add Digital Signature dialog so that user doesn't have to look for it him or herself. I was googling for solution and understand that this can not be done in native Excel VBA. One has to call Windows Shell directly. How do I do that?

Upvotes: 1

Views: 938

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

You don't state your Excel version but assuming you have a version with the ribbon UI. There are a couple of options - you can use the fluent UI control identifier and this code:

Option Explicit

Sub FindControlByFluentUIId()

    Dim objCtrl As CommandBarControl
    Dim lngId As Long

    On Error GoTo ErrHandler

    ' magic number of Add Digital Signature
    lngId = 13035
    ' find that control in the command bars collection
    ' this line throws an error for some workbooks !?
    Set obj = Application.CommandBars.FindControl(Office.MsoControlType.msoControlButton, lngId)
    ' execute
    If Not obj Is Nothing Then
        obj.Execute
    Else
        MsgBox "Not found"
    End If

    End Sub

ErrHandler:
    If Err.Number <> 0 Then
        Debug.Print Err.Description
    End If

End Sub

The full list of codes is here: https://www.microsoft.com/en-us/download/details.aspx?id=36798

If you didn't know the ID for some reason you can manually search each control collection of each command bar for a control with Caption this is like the one you are looking for. You are better off doing a wildcard search with the Like operator because you may not know the exact case of the control caption and position of the &s that facilitate keyboard short-cuts.

You can try something like this:

Option Explicit

Sub TestFindControl()

    Dim strCaptionWild As String
    Dim objCtrl As CommandBarControl

    ' use wildcards to help find the control
    strCaptionWild = "*add*a*digital*signature*"

    ' call the function to find by caption
    Set objCtrl = FindControl(strCaptionWild)

    ' execute on match
    If Not objCtrl Is Nothing Then
        Debug.Print "Command bar index: " & objCtrl.Parent.Index
        Debug.Print "Control index: " & objCtrl.Index
        Debug.Print "Real caption: " & objCtrl.Caption
        objCtrl.Execute
    Else
        MsgBox "Not found for caption: " & strCaptionWild
    End If

End Sub

Function FindControl(ByVal strCaption As String) As CommandBarControl

    Dim objCb As CommandBar
    Dim objCtrl As CommandBarControl
    Dim blnFound As Boolean

    On Error GoTo ErrHandler

    ' not found the control
    blnFound = False

    ' iterate command bars and their controls
    For Each objCb In Application.CommandBars
        For Each objCtrl In objCb.Controls
            ' use like operator check control caption vs input caption
            ' LIKE enables use of wildcard matching
            If LCase$(objCtrl.Caption) Like LCase$(strCaption) Then
                ' found it
                blnFound = True
                Exit For
            End If
        Next objCtrl
        If blnFound Then Exit For
    Next objCb

    Set FindControl = objCtrl

    Exit Function

ErrHandler:
    Debug.Print Err.Description
    Set FindControl = Nothing

End Function

Upvotes: 1

Related Questions