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, 2022 in Others by Kithuzzz
• 38,010 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, 2022 by narikkadan
• 63,700 points

Related Questions In Others

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, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

How can I store the data of an open excel workbook in a collection using BluePrism?

To do what you want is like ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

How can I perform a reverse string search in Excel without using VBA?

This one is tested and does work ...READ MORE

answered Jan 5, 2023 in Others by narikkadan
• 63,700 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,740 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, 2022 in Others by gaurav
• 23,260 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, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

How to multi level sort an array in excel, using formulas? I am aware about the way, using SORT button on DATA tab

Use SORTBY, e.g. =SORTBY(A2:B5,A2:A5,1,B2:B5,1) Or simply&nb ...READ MORE

answered Jan 22, 2023 in Others by narikkadan
• 63,700 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, 2022 in Others by narikkadan
• 63,700 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP