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

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" wrote in message
...
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

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" wrote:

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" wrote in message
...
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

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

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("cmd MyButton4").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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

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" wrote in message
...
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("cmd MyButton4").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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

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" wrote:

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" wrote in message
...
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("cmd MyButton4").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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

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 change
Set VBProj = Workbooks("TBD.xls").VBProject
to
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

"ChrisP" wrote in message
...
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" wrote:

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" wrote in message
...
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("cmd MyButton4").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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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