Deleting password protected code from Excel 2003 & 7 VBA

[phudgens]

I am using the following code to delete code modules from my Excel 2003 workbook:

    Set ThisVBProject = ActiveWorkbook.VBProject.VBComponents
    
    For Each VBCom In ThisVBProject
        If LCase(VBCom.Name) = "fracfill" Then ThisVBProject.Remove ThisVBProject(VBCom.Name)
    Next VBCom
    For Each VBCom In ThisVBProject
        If LCase(VBCom.Name) = "pd_plot" Then ThisVBProject.Remove ThisVBProject(VBCom.Name)
    Next VBCom
    For Each VBCom In ThisVBProject
        If LCase(VBCom.Name) = "fracspacing" Then ThisVBProject.Remove ThisVBProject(VBCom.Name)
    Next VBCom

This works great unless the code is password protected, and the code modules have not been opened with the correct password. In that case, an error message is posted that the code cannot be excuted since the code modules are protected. Is there a way to delete password-protected code regardless of whether the code modules have been opened or not?

Thanks,
Paul Hudgens
Denver

 

[combo]

If you need a workbook without all the modules and userforms, you can do it directly in excel:
1. right-click any tab and select all sheets,
2. right-click any tab again and copy selected to new workbook.

combo

 

[phudgens]

I'm looking for a way to do this programmatically in my VBA code. Thanks anyway.

Paul H.

 

[combo]

You can do a copy by code:
[tt]Application.Workbooks("WorkbookName.xls").Sheets.Copy[/tt]

As for diract access to protected vba project - no way except of breaking the project password (not a topic for this site).

combo

 

[phudgens]

Thanks. We don't want users to have access to the code, but obviously the code has to be in the Excel workbook in order for them to run the various programs. I had thought that maybe since they didn't have access to the code anyway, deleting it from the final output product was not really a big deal. Thanks anyway.

 

[macropod]

Hi Paul,

The following code can be used to Protect/Unprotect a vba project, provided you know the password (which the calling sub supplies).

Sub UnprotectVBProj(ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection <> 1 Then Exit Sub ' already unprotected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys Pwd & "~~"
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
End Sub

As the above shows, it is not necessary to break the password, contrary to combo's:

no way except of breaking the project password

Cheers
[MS MVP - Word]

 

[phudgens]

Apologies for my tardiness in replying to your post. This worked - Many Thanks!

Paul Hudgens

 

[phudgens]

It turns out that this code did not work after all - I had my code unlocked when I tested it. With the code protected, I get the message: "Runtime error 50289: Can't perform the operation since the project is protected."

I have also tried the following variation with the same results:

Private Sub UnprotectVBProj()
Dim vbProj As VBIDE.VBProject
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection <> 1 Then Exit Sub  Application.VBE.ActiveVBProject = vbProj
SendKeys "phudgens" & "~~", True
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
SendKeys "{ESC}"
End Sub

Thanks for any help.
Paul H.