Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto run macro when file is saved | Excel Programming | |||
Can't delete Excel file saved as web page | Excel Discussion (Misc queries) | |||
Conditional delete using saved macro possible ? | Excel Programming | |||
How can I delete a macro when the Delete button is not active? | Excel Worksheet Functions | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming |