Personal macro workbook

[Ken Green]

I have some custom macros that I made for the Personal.xls, which are then
available any time I start Excel. But what if I create a workbook that
doesn't use the macros and I don't want the VBA associated with it? (I
don't want users to have the Macro/VBA virus warning every time they open).

Basically, how do I dissociate a workbook from Personal? Or how do I clear
ALL custom VBA from a workbook?

--

-- Ken Green

[Jim Rech]

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>):

''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

[Myrna Larson]

On Tue, 28 Sep 1999 07:06:13 -0400, "Jim Rech" <jar...@kpmg.com> wrote:

>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!

[Patrick Molloy]

If your workbook doesn't use any of the macros in Personal.xls, then
there will be no association.

There shouldn't be a macro warning either way, unless you have coded
the workbook.

Patrick
________

[Ken Green]

OK, I promise I'm not as dumb as I look. I was overlooking the obvious.
Turns out that the workbook in question DID have some code. It was an empty
subprocedure. I must have looked at it 30 times and since there wasn't any
code between Sub and End Sub, hey, there's no code, right?

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>):
>