Thanks!
Chris wrote:
Thanks for the help.
16-Jul-08
Thanks for the help. Here's what my code looks like incase anyone can use it
Private Sub cmdMyButton_Click(
Call BreakLink
Application.Dialogs(xlDialogSaveAs).Show "Desktop
ActiveSheet.Shapes("cmdMyButton").Delet
Call DeleteAllVBACod
End Su
Sub BreakLinks(
Dim Links As Varian
Dim i As Intege
With ActiveWorkboo
Links = .LinkSources(xlExcelLinks
If Not IsEmpty(Links) The
For i = 1 To UBound(Links
.BreakLink Links(i), xlLinkTypeExcelLink
Next
End I
End Wit
End Su
Sub DeleteAllVBACode(
Dim VBProj As VBIDE.VBProjec
Dim VBComp As VBIDE.VBComponen
Dim CodeMod As VBIDE.CodeModul
Set VBProj = ActiveWorkbook.VBProjec
For Each VBComp In VBProj.VBComponent
If VBComp.Type = vbext_ct_Document The
Set CodeMod = VBComp.CodeModul
With CodeMo
.DeleteLines 1, .CountOfLine
End Wit
Els
VBProj.VBComponents.Remove VBCom
End I
Next VBCom
End Su
Private Sub CommandButton1_Click(
End Su
Private Sub Worksheet_SelectionChange(ByVal Target As Range
End Su
:
Previous Posts In This Thread:
Button to break links, delete all vba code & save as
I have a workbook that needs to be e-mailed out to many people. I would like
to create a button that the user can click which would break all the links in
the workbook, delete all the VBA code in the workbook & open a save as box so
the user can specify where to save the file.
I have the break links code
Sub BreakLinks(
Dim Links As Varian
Dim i As Intege
With ActiveWorkboo
Links = .LinkSources(xlExcelLinks
If Not IsEmpty(Links) The
For i = 1 To UBound(Links
.BreakLink Links(i), xlLinkTypeExcelLink
Next
End I
End Wit
End Su
but I have no idea how to do the rest. HELP!!!
Ok, I have the save as and the delete all vba code.
Ok, I have the save as and the delete all vba code. Now I would like to hav
my button deleted before saving. any ideas
:
Re: Button to break links, delete all vba code & save as
One possibility: put the button on a toolbar instead of in the workbook
and add code to exit sub if it isn't the right workbook
Another possibility: If the button is in the workbook, it is an object
of some type (I've used a couple different methods of creating
'buttons', not all actual buttons nor ActiveX controls). Use code to
find the object and delete it from the worksheet, similar to how you are
deleting the code modules
--
Cli
Still learning Access (and Excel) 200
--
Cli
Still learning Access 2003
Thanks for the help.
Thanks for the help. Here's what my code looks like incase anyone can use it
Private Sub cmdMyButton_Click(
Call BreakLink
Application.Dialogs(xlDialogSaveAs).Show "Desktop
ActiveSheet.Shapes("cmdMyButton").Delet
Call DeleteAllVBACod
End Su
Sub BreakLinks(
Dim Links As Varian
Dim i As Intege
With ActiveWorkboo
Links = .LinkSources(xlExcelLinks
If Not IsEmpty(Links) The
For i = 1 To UBound(Links
.BreakLink Links(i), xlLinkTypeExcelLink
Next
End I
End Wit
End Su
Sub DeleteAllVBACode(
Dim VBProj As VBIDE.VBProjec
Dim VBComp As VBIDE.VBComponen
Dim CodeMod As VBIDE.CodeModul
Set VBProj = ActiveWorkbook.VBProjec
For Each VBComp In VBProj.VBComponent
If VBComp.Type = vbext_ct_Document The
Set CodeMod = VBComp.CodeModul
With CodeMo
.DeleteLines 1, .CountOfLines
End With
Else
VBProj.VBComponents.Remove VBComp
End If
Next VBComp
End Sub
Private Sub CommandButton1_Click()
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
:
Re: Button to break links, delete all vba code & save as
You're welcome.
Ahh. Share, and share alike. Thoughtful of you.
--
Clif
Still learning Access 2003
Does someone know how to do the exact same thing in Microsoft Word 2007?
Thanks!
Submitted via EggHeadCafe - Software Developer Portal of Choice
Server Side Processing in ADO.NET/WCF Data Services
http://www.eggheadcafe.com/tutorial...f-4f6f92a76585/server-side-processing-in.aspx