CODEWITHSUNDEEP
excelvba
Sonal
Sonal

Reputation: 137

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

I am looking for a way to open the Import XML option (highlighted below) through a macro command...

enter image description here

So far, I have tried using Application.SendKeys ("%lt") - it works, but only when one has enabled the Developer tab in the ribbon - and sadly, a lot of my users won't have the tab enabled. So I thought If it's possible to toggle this checkbox - (File >> Excel Options >> Show Developer Tab)

enter image description here

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! :)

Upvotes: 0

Views: 536

Answers (1)

Elio Fernandes
Elio Fernandes

Reputation: 1420

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

Upvotes: 0

Related Questions