How to change a module's code from multiple VBA protected projects

Question

I have multiple Excels that I need to process, which means changing automatically one of their module's code. As each VBA project is protected, I need to unprotect each one to be able to change the module's code.

Further information is detailed here: https://www.mrexcel.com/board/threads/how-to-automatically-change-a-module-code-in-multiple-protected-visual-basic-projects.1242413/

The problem I have is in the unlock function, I get a "Runtime error 5" when running the "VBE.CommandBars" instruction. I tried differents versions of the unlock function but none of them are working. The unlock function that I'm currently using is from here: https://www.mrexcel.com/board/threads/copy-code-from-a-protected-vba-project.1185364/

I hope someone could give me any clue on what I'm doing wrong.

Thank you.

I show you my current code (hope you can understand even though the comments are in Spanish):

Option Explicit

Sub UnLockAndViewVBAProject()
     With Application
        '//we execute the "VBAProject Properties..." control\\
        '//just to invoke the password dialog, the password\\
        '//must be given before the dialog can be shown :) \\
        .SendKeys "hola3"
         .SendKeys "{ENTER}"
        '//now reset the project password and relock it so\\
        '//that it's locked again when the workbook's closed\\
        .VBE.CommandBars("Menu Bar").Controls("Tools") _
            .Controls("VBAProject Properties...").Execute
        .SendKeys "^{TAB}"
        .SendKeys "{TAB}" & "{TAB}" & "{TAB}" & "hola3"
        .SendKeys "{TAB}" & "hola3"
        .SendKeys "{TAB}"
        .SendKeys "{ENTER}"
     End With
End Sub

Sub ACTUALIZAR_MODULO()
'Declaramos variables
Dim nArchivos, CodigoCopiar, CodigoPegar
Dim destino As String, NombreLibro As String
Dim FSO As Variant, i As Long, lineas As Long
Dim WB As Workbook
Dim unpassVB As String

'Desactivamos actualización de pantalla
Application.ScreenUpdating = False

'Seleccionamos uno o varios archivos
nArchivos = Application.GetOpenFilename(filefilter:="Excel (*.xls*),*.xls", _
Title:="SELECCIONAR ARCHIVO", MultiSelect:=True)

'Nombre de la hoja o del módulo (en este caso), cuyo código se quiere modificar
destino = "D_Utilidades"

'si no seleccionamos nada, salimos del proceso
If Not IsArray(nArchivos) Then
    Exit Sub
Else

    If destino = Empty Then
        Exit Sub
    Else
        
        'Recorremos mediante un array los archivos seleccionados
        For i = LBound(nArchivos) To UBound(nArchivos)
        
            'Abrimos cada archivo
            Set WB = Workbooks.Open(Filename:=(nArchivos(i)))
                    
            With ActiveWorkbook
            
                'Desprotegemos el proyecto de Visual Basic para poder comprobar si existe el módulo para tal archivo
                UnLockAndViewVBAProject
                
                'Borramos el código que queremos actualizar en los archivos seleccionados
                .VBProject.VBComponents(destino).CodeModule.DeleteLines 1, .VBProject.VBComponents(destino).CodeModule.CountOfLines
                
                'seleccionamos y copiamos el código de nuestro libro y que está en el módulo CODIGO A COPIAR
                Set CodigoCopiar = ThisWorkbook.VBProject.VBComponents("CODIGO_A_COPIAR").CodeModule
                'Pegamos en cada archivo y módulo seleccionado el código que hemos copiado
                Set CodigoPegar = .VBProject.VBComponents(destino).CodeModule
                lineas = CodigoCopiar.CountOfLines
                CodigoPegar.AddFromString CodigoCopiar.Lines(1, lineas)
                    
                'cerramos cada libro que hemos seleccionado y abierto
                WB.Close SaveChanges:=True
                
            End With
    
        Next i
        
    End If
    
End If

End Sub

Answer 1

What's your excel version? I know in 2016 excel there was/is a bug where .vbe.Commandbars returns a runtime error 5. I think unfortunately there might be no other option but to manually unlock each wb if the bug persists.

Answer 2

There's two possible problems here.

1. If you have your Office Display Language settings (ie in the Excel UI, File > Options > Language > Office Display Language) set to use the Spanish language, is that the VBE menus and controls use names localised in that language (crazy ... but true). You're using English language names in your code and so the relevant menus and controls are not found (for example, in Spanish, the main menu is "Barra de menús" not "Menu Bar"). The safer solution, rather than using any name, is to use the index number ... which is 1.

2. You will also find that the name of the "VBAProject Properties..." button changes for the name of the specific project ie it is only "VBAProject Properties..." if your project name is "VBAProject" ... if you rename your project to "Foo" then the button becomes "Foo Properties...". The safer solution, rather than using any name, is to use the index number ... which is 2578.

The relevant line of code (used in the With Application block) is, then:

.VBE.CommandBars(1).FindControl(ID:=2578, Recursive:=True).Execute