Delete VB code

[ChrisP]

I have a workbook that has password protected VB code (it must stay password
protected). Below is my code, my issue is that I can't delete the VB code
without entering the password but I have no clue how to do that. Any help is
appreciated!!!

Private Sub cmdMyButton4_Click()

Call BreakLinks

Application.Dialogs(xlDialogSaveAs).Show "TBD"

Call DeleteVBA

ActiveSheet.Shapes("cmdMyButton4").Delete

End Sub

Sub BreakLinks()
Dim Links As Variant
Dim i As Integer

With ActiveWorkbook
Links = .LinkSources(xlExcelLinks)
If Not IsEmpty(Links) Then
For i = 1 To UBound(Links)
.BreakLink Links(i), xlLinkTypeExcelLinks
Next i
End If
End With
End Sub

Sub DeleteVBA()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

Set VBProj = ActiveWorkbook.VBProject

For Each VBComp In VBProj.VBComponents
If VBComp.Type = vbext_ct_Document Then
Set CodeMod = VBComp.CodeModule
With CodeMod
.DeleteLines 1, .CountOfLines
End With
Else
VBProj.VBComponents.Remove VBComp
End If
Next VBComp
End Sub

 

[Peter T]

Simplest way is copy all the sheets to a new workbook. Easy enough to do
that manually or with code simply

Activeworkbook.Sheets.Copy

(you could paste that in the immediate window and hit enter)

If you had any code behind worksheet modules that'll get copied, but no
password in the new workbook so remove manually or with code.

Regards,
Peter T

 

[ChrisP]

That is an awesome idea! I would have never thought of that one... one
problem, one of my sheets is linked to another sheet which is linked to a
password protected workbook. When I copy the first sheet I get a pop up to
enter the password. How can I bypass this?

 

[Peter T]

If you think you need to open the password protected wb (not that you do)
you could open it programatically and supply the password(s). Otherwise just
disable/re-enable DisplayAlerts

Sub test()
Dim sFile As String
Dim wbOrig As Workbook

Set wbOrig = ActiveWorkbook ' the wb to copy
''' better still name it something like this
''' Set wb = workbooks("myBook.xls")

''' if necessary open the other wb
'sFile = "c:\temp\myPWbook.xls"
'
'Workbooks.Open Filename = sFile, _
' Password:="abc", _
' WriteResPassword:="abc"

Application.DisplayAlerts = False
wbOrig.Sheets.Copy
Application.DisplayAlerts = True

'' in case the orignal has a customiseed palette
ActiveWorkbook.Colors = wbOrig.Colors

'ActiveWorkbook.SaveAs a-unique-filename

End Sub


Regards,
Peter T

 

[ChrisP]

I tried to disable the display alerts but the password box keeps popping up.
What am I doing wrong? Here's my code:

Private Sub cmdMyButton4_Click()

Application.DisplayAlerts = False

ThisWorkbook.Sheets(Array("Summary", "2008", "Invoices")).Copy

Application.Dialogs(xlDialogSaveAs).Show "TBD"

Call BreakLinks

Call DeleteVBA

Call DeleteAllNames

Call DeleteButton

Application.DisplayAlerts = True

End Sub

Sub BreakLinks()
Dim Links As Variant
Dim i As Integer

With Workbooks("TBD.xls")
Links = .LinkSources(xlExcelLinks)
If Not IsEmpty(Links) Then
For i = 1 To UBound(Links)
.BreakLink Links(i), xlLinkTypeExcelLinks
Next i
End If
End With
End Sub

Sub DeleteVBA()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

Set VBProj = Workbooks("TBD.xls").VBProject

For Each VBComp In VBProj.VBComponents
If VBComp.Type = vbext_ct_Document Then
Set CodeMod = VBComp.CodeModule
With CodeMod
.DeleteLines 1, .CountOfLines
End With
Else
VBProj.VBComponents.Remove VBComp
End If
Next VBComp
End Sub
Sub DeleteButton()

Workbooks("TBD.xls").Sheets("Summary").Shapes("cmdMyButton4").Delete

End Sub

Sub DeleteAllNames()
Dim objName As Excel.Name
For Each objName In Workbooks("TBD.xls").Names
objName.Delete
Next objName
End Sub

 

[Peter T]

Don't know. I tested, for me disabling DisplayAlerts prevented the dialogs
to enter pw's for a closed, linked password prtected file. (I didn't test
with sheet protected)

You could try SendKeys Esc as a last resort (call that just before the
copy), or open the file as I suggested.

Looks like you only want to copy three sheets but not all sheets. No doubt
you have a reason but it seems odd that you save the copied file before
deleting the code and button, rather than after.

Regards,
Peter T


In passing, why the

 

[ChrisP]

I did it that way because I will have multiple workbooks open and I only want
the new workbook to have the code deleted. If I don't save it first then I
have no name to reference in the code. I'm very new to all this, I even
bought the VBA for dummies book but it doesn't help with this issue.

Thanks,
Chris

 

[Peter T]

After you do the sheets copy the new workbook will be the activeworkbook.
You could, in your particular scenario, simply work with the ActiveWorkbook
object while you are sure you have the correct wb.

so you could changeto
Set VBProj =ActiveWorkbook.VBProject

A more reliable (and more reusable codewise) way of doing it would be to
change
Sub DeleteVBA()
to
Sub DeleteVBA(wb as Workbook)
and
Set VBProj =wb.VBProject

You might call this procedure like this

DeleteVBA ActiveWorkbook

or even better, attach an reference to the ActiveWorkbook (ie the new wb
resulting from the copy) as soon as possible

Dim newWB as workbook

'code to copy etc

set newWB = ActiveWorkbook
DeleteVBA newWB

(and similarly for the other routines)

When you've made all the changes save it
NewWB.SaveAs etc, or if you want user to do it your dialogs method is fine

Regards,
Peter T