How should I show the Developer tab in Excel using Macros Excel VBA

Question

I'm trying to figure out how to use a macro command to open the Import XML option, which is shown below.

I've tried using the Application.SendKeys ("%lt") so far; it works, but only if the Developer tab in the ribbon is active, which is unfortunate because many of my users won't have it. Because of this, I wondered if it was possible to toggle the checkbox next to Show Developer Tab in the File >> Excel Options menu.

I'll just make the Developer tab visible in my user's Excel and then use Sendkeys. Or, if this isn't possible, Is there any way I could invoke the Import XML option by any other means in Macro? Invoking the Import XML option is the only reason I am doing all this. Kindly guide... Thanks! :)

Answer 1

You can activate (mode=1) or deactivate (mode=0) the developer tab by changing the DeveloperTools option in the registry.

Sub Test_DeveloperTab()
    Call setDeveloperTab(1)
End Sub

Sub setDeveloperTab(ByVal mode As Integer)
    Dim regKey As String
    regKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application.Version & "\Excel\options\DeveloperTools"
    
    On Error GoTo errHandler
    
    ' If value is equal to existing or different from 0 or 1 then exit
    Select Case Registry_KeyExists(regKey)
        Case 0: If mode = 0 Then Exit Sub
        Case 1: If mode = 1 Then Exit Sub
        Case Else: Exit Sub
    End Select
    
    ' Late Binding
    Dim oShell As Object: Set oShell = CreateObject("Wscript.Shell")
        
    If (mode <> 0 And mode <> 1) Then Exit Sub
    
    ' Developer Tab: Activate \\ Deactivate
    oShell.RegWrite regKey, mode, "REG_DWORD"
    
exitRoutine:
    Exit Sub
    
errHandler:
    Debug.Print Now() & "; " & Err.Number & "; " & Err.Source & "; " & Err.Description
    Resume exitRoutine
End Sub

Function Registry_KeyExists(ByVal regKey$) As Variant
    ' Check if registry key exists
    
    On Error GoTo errHandler
    
    Dim wsh As Object: Set wsh = CreateObject("WScript.Shell")
    Registry_KeyExists = wsh.RegRead(regKey)
    
    Exit Function
    
errHandler:
    Err.Raise Err.Number, "Registry_KeyExists", Err.Description
End Function