delete macro button once file is saved
 

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

delete macro button once file is saved
 

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

--
JBeaucair
-----------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=7
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=6285

delete macro button once file is saved
 

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


On Wed, 11 Feb 2009 17:41:01 -0800, AB wrote:

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

delete macro button once file is saved
 

hi
if you are using filesaveas 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

"AB" wrote:

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

delete macro button once file is saved
 

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

sorry about that
regards
FSt1

"FSt1" wrote:

hi
if you are using filesaveas 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

"AB" wrote:

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

delete macro button once file is saved
 

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


--
Stringer
------------------------------------------------------------------------
Stringer's Profile: http://www.thecodecage.com/forumz/member.php?userid=117
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62854

delete macro button once file is saved
 

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

"Stringer" wrote:


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


--
Stringer
------------------------------------------------------------------------
Stringer's Profile: http://www.thecodecage.com/forumz/member.php?userid=117
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62854