0

I am using the following code upon start-up to compile my workbook to prevent any Active-X related errors:

Private Sub ThisWorkbook_Open()
    Set compileMe = objVBECommandBar.FindControl(Type:=msoControlButton, ID:=578) 
    compileMe.Execute
End Sub

This code automates the command bar in VBE and works great in an unprotected workbook. When I lock the VBE (password protect) the debug->compile option becomes disabled and this results in the above code running into an error.

Is there any workaround this? For example, unprotect VBE then run the above code and protect it again all from the same workbook? I understand this wont be a basic task as AFAIK there is no API for this but is there any solution to this preferably (for stability purposes) a one which doesn't use send keys?

EDIT: Apologies for the confusion with question duplication. The question you have linked to be a duplicate explains how to unlock VBE in a different workbook in another instance of excel, this is no problem as it works fine however, requires an additional excel file to act as an "unlocker file" as opening the same file in another instance opens it as read only.

What I am trying to do is have the same workbook unlock itself, the problem with this is that as soon as the window opens asking for the password the code stops at that point. The reason for this is that it is opening the window as a Modal window therefore, requiring some form of user input to continue (hide or unload the window). So really, I was just wondering if there is any other way around this or if I am over complicating things.

Examorph
  • 143
  • 1
  • 1
  • 7
  • Just a tip for you to continue: you need to find (into the command bar, put a watcher and walk through all the elements) the form to input the password and unlock the code. you input the password, compile, then lock it again and continue the execution normally. – Matteo NNZ Jan 18 '15 at 00:25
  • Thanks for the guidance, the main problem I seem to have with the method you described is that I have to do all of this in another instance of excel as when I try to input the password, as soon as the password input window opens in the same instance, the code just stops at this point. Also, if I try and create an instance of the same file it opens are read only which is a problem in itself thats why I am wondering if this is even possible or if there is a simpler alternative to this. – Examorph Jan 18 '15 at 00:43
  • Have you already seen [this](http://stackoverflow.com/a/16176557/3111149)? – Matteo NNZ Jan 18 '15 at 01:40
  • Otherwise, the logic i would suggest is the same of the previous question: i might be wrong, but inside objVBECommandBars there should be as well the password input. Try to put a watcher and read through all the items. – Matteo NNZ Jan 18 '15 at 01:44

0 Answers0