2

I'm developing a code to update protected modules in Excel.

This is the schema:

  • 1: On opening the book (Thisworkbook module, open function), there is a comparison between workbook version and valid version of the book (connecting to a database). If it's the same, it ends. If not, goes to step 2.
  • 2: function UnprotectVBProj is called to unprotect VBA code.
  • 3: all modules (except the one that contains functions to unprotect, delete and load new modules) are deleted using function.
  • 4: modules are loaded again from server, using a function called 'import_modules'.
  • 5: new version is set in workbook and it's closed.

My code works if I execute it step by step.

When it runs automatically on opening the book, function "DeleteVBAModulesAndUserForms" returns an error saying that code is protected. If I debug, I can see the code is not protected, it's really unprotected. It's like vbproject variables hadn't been set to the new unprotected state.

Here is the code:

Sub Workbook_Open()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

'Variables
Dim herramienta As String
Dim password_hoja As String
Dim password_visual As String

password_visual = "1111111111"
herramienta = "222222222"
password_hoja = "333333333"

Dim miversion As String
Dim WS_Count as Integer, i As Integer

miversion = CStr(Sheets("Control").Range("AG106").Value)

'Here is the function to make the comparation with version stered in database
If ultima_version(herramienta) = miversion Then

Else`enter code here`
    MsgBox ("Old version, need to update.")
    'Unprotect the code
    Call UnprotectVBProj(ThisWorkbook, password_visual)
    'delete all modules
    Call DeleteVBAModulesAndUserForms
    'load new modules
    Call importa_modulos
    'change version in sheet
    ActiveSheet.Unprotect Password:=password_hoja
    Sheets("Control").Range("AG106").Value = ultima_version(herramienta)
    ActiveSheet.Protect Password:=password_hoja, DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:= _
        True, AllowFiltering:=True, AllowUsingPivotTables:=True
    'Save and close
    ActiveWorkbook.Close (1)
End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Sub UnprotectVBProj(ByRef WB As Workbook, ByVal Password As String)
  Dim vbProj As Object
  Set vbProj = WB.VBProject

  Application.ScreenUpdating = True

  If vbProj.Protection <> 1 Then Exit Sub
  Set Application.VBE.ActiveVBProject = vbProj

  SendKeys Password & "~"
  SendKeys "~"
  Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute

  Application.ScreenUpdating = False
End Sub

Sub DeleteVBAModulesAndUserForms()

        Dim vbProj2 As Object
        Dim VBComp As Object

        Set vbProj2 = ActiveWorkbook.VBProject

        'Next line is commented to display protection error
        'If vbProj2.Protection <> vbext_pp_none Then Exit Sub

        For Each VBComp In vbProj2.VBComponents
            Select Case VBComp.Type
            Case Is = vbext_ct_StdModule, vbext_ct_ClassModule, vbext_ct_MSForm
                If VBComp.Name = "A_Importa_Modulos" Or VBComp.Name = "ThisWorkbook" Then
                    'Thisworkbook or worksheet module
                    'We do nothing
                Else
                    vbProj2.VBComponents.Remove VBComp
                End If
            Case Is = vbext_ct_Document
                'With VBComp.CodeModule
                '.DeleteLines 1, .CountOfLines
                'End With
            End Select
        Next VBComp
End Sub
Community
  • 1
  • 1
Pablo Garcia
  • 21
  • 1
  • Not sure but may try with `VBComp.Activate` before trying to delete. May refer my answer in [SO post](https://stackoverflow.com/questions/43703642/after-import-of-a-userform-into-vbcomponents-properties-cannot-be-read/52881988#52881988) – Ahmed AU Mar 12 '19 at 11:02
  • 1
    If the VBA Editor is protected then how are you debugging it? Are you able to run SendKeys when the project is closed? Have you seen [THIS](https://stackoverflow.com/questions/16174469/unprotect-vbproject-from-vb-code) approach? – Siddharth Rout Mar 12 '19 at 11:04
  • Hi Ahmed, thanks for reply. I've tested to put "VBComp.Activate" it in deleteVBAModulesAndUserForms() function, inside "For Each VBComp in vbProj2.VBComponents", but it didn't work. – Pablo Garcia Mar 12 '19 at 11:09
  • Hi @SiddharthRout , thanks for your help. The way I'm debuggin it is putting a msgbox after unprotect module. When it appears, I send Ctrl+Pause and go into the Vba editor window. And when I do it, the code is unprotected. If I don't add this msgbox, error message appears when function is in delete module, and then when I try to debug it, I check that variable vbProj2.Protection is set to '0' value (While execution, it's set to '1'). – Pablo Garcia Mar 12 '19 at 11:23
  • @SiddharthRout, I've checked your approach, but I've not used it because I'm using Excel 365, not 2007, and unprotect module worked for me. Thanks again! – Pablo Garcia Mar 12 '19 at 11:24

0 Answers0