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

0 votes

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

enter image description here

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.

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

Nov 15 in Others by Kithuzzz
• 20,660 points

1 answer to this question.

0 votes

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"
    Exit Sub
    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
    Err.Raise Err.Number, "Registry_KeyExists", Err.Description
End Function
answered Nov 15 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24 in Others by narikkadan
• 37,660 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
0 votes
1 answer

How can I find and replace text in Word using Excel VBA?

Try this code Option Explicit Const wdReplaceAll = 2 Sub ...READ MORE

answered Oct 15 in Others by narikkadan
• 37,660 points
0 votes
1 answer

How do I insert a WebP-image (".jpg") in Excel using VBA?

It's not currently on the list of ...READ MORE

answered Nov 15 in Others by narikkadan
• 37,660 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP