VBA Update Code

[alwaysXcel]

Hello,

I am trying to find a way to write a procedure that can programatically update my VBA code. I created about 500 worksheets and I need to update 1 single line of code in all those worksheets. The issue I am running into is that my VBA code in all those worksheets is protected for viewing. So how can I update the code in all those worksheets??

Thank you very mucH!!!!

[Richie(UK)]

Hi a,

Welcome to the board.

As far as using code to amend code goes, you may find Chip Pearson's page a useful starting point:
http://cpearson.com/excel/vbe.htm

As regards using code to unlock VBAProjects goes, you can't.

OK, strictly-speaking there is no in-built way of doing it - there is a Protection Property that will enable you to determine whether a project is locked but there is no way of passing a password argument to either lock or unlock the project. There is a workaround that involves the use of SendKeys. However, SendKeys is not a reliable method and most 'professional' coders would frown upon its use.

Why do you have 500 workbooks all containing the same piece of code? Would it not be preferable to have the code located in one workbook (preferably an add-in) that can act on the other workbooks? That way, any code updates only need to be done to one workbook. Just a thought.

[sixth sense]

This issue has been around in several forums too.
Maybe the best solution you can do is make a new workbook with the updated code. Then let a procedure run to copy all the relavent infomation in the old workbook to the new one. You can copy all the important detail in the old workbook including formating.
You can then rename the old file as back up or delete it and reneame the new workbook with the name of the old one.

[alwaysXcel]

This makes total sense. But how do I go about updating information in a closed password protected workbook. One good thing is my workbooks are password protected with user interface only.

[Jacob Hilderbrand]

You have to be able to open the workbook to modify it. If you know the password you can open the workbook and supply the password, then make your changes.

[alwaysXcel]

Thank you for your response. I do have the password, however, I am confused as to how to "syntax-it" to send the password.

[Richie(UK)]

Hi a,

You use the password argument ... like this:

Sub Test()
    Dim wbk As Workbook
Set wbk = Workbooks.Open(FileName:="TheWorkbookName", Password:="ThePassword")
With wbk
        '
        'do stuff with wbk
        '
        .Close
    End With
End Sub

HTH