delete macro button once file is saved

[AB]

Hi,

I've created a macro that saves a template 'Master' into 'Slave' .xls file
that contains only values; assigned this to a macro button that sits in the
'master' file.

my problem is this - the macro button is saved with the 'slave' .xls file,
however i don't want users of the slave file to be able to have it and muck
with it.

Thus i want to have, as part of the programing, for the button to 'self
destruct' in the .xls file

I'm using MS Office Excel 2003 and the slave files are to be distributed on
a LAN and also for emailing.

Many thanks in advance

 

[JBeaucaire]

Place this code into your macro:
========
Dim vbCom As Object
Set vbCom = Application.VBE.ActiveVBProject.VBComponents

vbCom.Remove VBComponent:= _
vbCom.Item("Module1")
========

You will have to go to Tools>Macro>Security - Trusted Publishers an
check "Trust access to Visual Basic Editor" before running the adde
code.

Change "Module1" at the bottom to the module holding the macro(s) yo
want to delete, the whole module is going away.

Source: 'Excel VBA: Delete Module After Running VBA Code. Deletin
Modules via VBA Code' (http://www.ozgrid.com/VBA/delete-module.htm

 

[Gord Dibben]

I would think you also want to remove the code behind the button.

See Chip Pearson's site for code and module removal.

http://www.cpearson.com/excel/vbe.aspx

As far as getting rid of the button just select it by name and delete it
before saving.

All objects have a name that can be accessed.


Gord Dibben MS Excel MVP

 

[FSt1]

hi
if you are using file>saveas then you are saveing the button, macro, data
and everthing else into your slave file. a better solution might be to just
save the range of your template.
Sub mac1SaveRange()
Dim r As Range
set r = range(A1:N50") "change to fit your template
r.Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
Application.Dialogs(xlDialogSaveAs).Show
End Sub

this way only the date will end up in your slave file.

Regards
FSt1

 

[FSt1]

hi
corrections.
this way only the data will end up in your slave file

sorry about that
regards
FSt1

 

[Stringer]

Holy cow, How many times are you going to ask this question

 

[FSt1]

hi
i'm not asking a question. i'm answering one. as to the double post, i made
a mistake in my previous post to the op and this post corrects it.

regards
FSt1