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

Question

I have multiple Excel files to change automatically one of their module's code. As each VBA project is protected, I need to unprotect each one.

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

In the unlock function, I get

"Runtime error 5"

when running the VBE.CommandBars instruction.

I tried different versions of the unlock function.

The unlock function: https://www.mrexcel.com/board/threads/copy-code-from-a-protected-vba-project.1185364/

My current code (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