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" <(E-Mail Removed)> wrote in message
news:7D5D11D8-A6D6-4381-B2D7-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:7D5D11D8-A6D6-4381-B2D7-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
news:AACB47A0-2B82-480F-ABA6-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:7D5D11D8-A6D6-4381-B2D7-(E-Mail Removed)...
>> >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("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" 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" <(E-Mail Removed)> wrote in message
> news:AACB47A0-2B82-480F-ABA6-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:7D5D11D8-A6D6-4381-B2D7-(E-Mail Removed)...
> >> >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" <(E-Mail Removed)> wrote in message
news:46A21B3F-7BD2-4FF4-96E6-(E-Mail Removed)...
>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" 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" <(E-Mail Removed)> wrote in message
>> news:AACB47A0-2B82-480F-ABA6-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:7D5D11D8-A6D6-4381-B2D7-(E-Mail Removed)...
>> >> >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" <(E-Mail Removed)> wrote in message
> news:46A21B3F-7BD2-4FF4-96E6-(E-Mail Removed)...
> >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" 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" <(E-Mail Removed)> wrote in message
> >> news:AACB47A0-2B82-480F-ABA6-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> news:7D5D11D8-A6D6-4381-B2D7-(E-Mail Removed)...
> >> >> >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" <(E-Mail Removed)> wrote in message
news:3927C4C8-7C1F-4B2C-B8EC-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
>> news:46A21B3F-7BD2-4FF4-96E6-(E-Mail Removed)...
>> >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" 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" <(E-Mail Removed)> wrote in message
>> >> news:AACB47A0-2B82-480F-ABA6-(E-Mail Removed)...
>> >> > 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" <(E-Mail Removed)> wrote in message
>> >> >> news:7D5D11D8-A6D6-4381-B2D7-(E-Mail Removed)...
>> >> >> >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
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>