Basically, how do I dissociate a workbook from Personal? Or how do I clear
ALL custom VBA from a workbook?
--
-- Ken Green
But as to removing macros from a workbook here is an Excel 97/2000 macro
that I wrote to remove everything associated with macros and programmability
from the active workbook. (Obviously this macro must be in another
workbook<g>):
''Needs a reference to the VB Extensibility library set (Tools, References
in VBE)
'Removes from active workbook all:
''Regular modules
''Class modules
''Userforms
''Code in sheet and workbook modules
''Non built-in references
''Excel 4 macro sheets
''Dialog sheets
Sub RemoveAllCode()
'XL2K:
'Dim VBComp As VBComponent, AllComp As VBComponents, ThisProj As
VBProject
'XL97 & XL2K:
Dim VBComp As Object, AllComp As Object, ThisProj As Object
Dim ThisRef As Reference, WS As Worksheet, DLG As DialogSheet
If ActiveWorkbook.Name <> ThisWorkbook.Name Then
Set ThisProj = ActiveWorkbook.VBProject
Set AllComp = ThisProj.VBComponents
For Each VBComp In AllComp
With VBComp
Select Case .Type
Case vbext_ct_StdModule, vbext_ct_ClassModule,
vbext_ct_MSForm
AllComp.Remove VBComp
Case vbext_ct_Document
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End Select
End With
Next
For Each ThisRef In ThisProj.References
If Not ThisRef.BuiltIn Then ThisProj.References.Remove ThisRef
Next
End If
Application.DisplayAlerts = False
For Each WS In Excel4MacroSheets
WS.Delete
Next
For Each DLG In DialogSheets
DLG.Delete
Next
End Sub
--
Jim Rech
Excel MVP
>But as to removing macros from a workbook here is an Excel 97/2000 macro
>that I wrote to remove everything associated with macros and programmability
>from the active workbook. (Obviously this macro must be in another
>workbook<g>):
Nice one, Jim!
There shouldn't be a macro warning either way, unless you have coded
the workbook.
Patrick
________
Deleted the empty Sub and all is well.
By the way, thanks for this macro.
-- Ken Green
Jim Rech <jar...@kpmg.com> wrote in message
news:#Gr#zFaC$GA....@cppssbbsa02.microsoft.com...
> I do not understand what you mean by "disassociate" a workbook from
> Personal. Personal.xls is ultimately just another workbook. It does not
> imbue any other workbook with any kind of association. Is it that you
saved
> Personal under another name? That's not the way to go; it's better to
start
> with a new workbook (File, New).
>
> But as to removing macros from a workbook here is an Excel 97/2000 macro
> that I wrote to remove everything associated with macros and
programmability
> from the active workbook. (Obviously this macro must be in another
> workbook<g>):
>