Copying macro and associated button to new file/sheet "in flight"

[Asterix]

Dear all,

macro1 and macro2 are held on .xls1.

Macro 1 creates a new file (.xls2), collects data from a given directory, manipulates and formats it.

Macro2 extracts individual specific items from .xls2 and emails that extract to an individual.

Problem is, macro2 starts off existing in .xls1, attached to a button.

.xls1 isn't available to the person that .xls2 is created for.

At the end of macro1, how do I copy macro2 and it's associated button into the top left hand corner of .xls2?

This and other forums like it have been invaluable for getting me to this point. All the other questions I've had, I've searched for answers and found them. I've searched for this one too, just can't find an answer.

Thanks for looking.

[Bob Phillips]

Use a control toolbox button with the code in the worksheet code module, and just copy the sheet to the new book.

[Asterix]

Thanks:friends: , that would work and I'll start by going down that route but [aaarghhh!...scope creep:devil2: ], it would be more cosmetically pleasing if the button just appeared in the newly created sheet on the newly created file.

[Bob Phillips]

Just delete the others, then it does!

[Asterix]

I think we're closer but it's me not being clear enough and my lack of knowledge around adding/ identifying/ copying buttons...here's the original post amended to clarify...

macro1 and macro2 are held on .xls1. Button1 and Button2 are associated with macro1 and macro2 respectively on sheet1 of .xls1

Macro 1 creates sheet2 on .xls1, collects data from a given directory, manipulates and formats it. Then macro1 takes sheet2 and moves it to a newly created date/time-stamped-in-the-filename .xls2

Macro2 extracts individual specific items from sheet2 on .xls2 and emails that extract to an individual.

Problem is, macro2 starts off existing in .xls1, attached to button2.

.xls1 isn't available to the person that .xls2 is created for.

Does that make sense?

If I did what you suggest (which sounds completely valid), I'd still have to delete button1 (and preferably macro1) from sheet2...and I don't know how to identify and delete buttons.:doh:

[Bob Phillips]

post your workbook and we'll see what we can do.

[Norie]

Do you really need 2 seperate buttons?

[Asterix]

post your workbook and we'll see what we can do.
I'd love to, but that would contravene our security policy (financial industry).

[Asterix]

Do you really need 2 seperate buttons?
Yep, they do 2 separate things - wouldn't be desirable to combine them, even with a user confirm breakpoint.

[Bob Phillips]

I'd love to, but that would contravene our security policy (financial industry).

Can you not even post an obfuscated workbook?

[Asterix]

Can you not even post an obfuscated workbook?

I could, but you may not hear from me again while I'm mid-obfustication. Will try and do this tomorrow. Thanks again for persevering.

[Asterix]

Have obfusticated completely.

Hope I've attached it right. Sheet opens with 2 buttons, "Setup" and "Email". "Email" is effectively a dummy for illustration purposes. Select "Setup" and a new sheet is created, and then moved to a newly created file with specific date/time title stamp.

I want to be able to get the "Email" button and associated macro into the new sheet (preferably before for simplicity but after the move will do). I do not want the "Setup" button and macro in the new sheet.

N.b. I had to "Save" the attachment. "Open" it caused a bug for me.

Thanks.

[Asterix]

[said in a smily tone, appreciative of all the comments so far]
Based on the speed of response to the original question, am I to assume we're having a "hmmm", stroky beard moment, then?

[Asterix]

Ok, have worked out how to move all macro's but not 1 macro of several, nor how to setup a button on a new sheet...yet

[Asterix]

http://www.vbaexpress.com/kb/getarticle.php?kb_id=275

[Asterix]

Except I'm having problems assigning the existing macro that I've moved (i.e. is already present in the new file) to the button that this code creates.

[Bob Phillips]

Now I want you to sit down, I have some extremely disconcerting newqs for you.

We are NOT all sitting here waiting for you to post another question that we can rush off and solve for you.

[Asterix]

Now I want you to sit down, I have some extremely disconcerting newqs for you.

We are NOT all sitting here waiting for you to post another question that we can rush off and solve for you.

Haha...I knew that, but thanks for pointing out the bleedin' obvious anyway:rotlaugh: (note to self, must use more smiley's next time)

Anyway, courtesy of you, other VBAexpress threads, Wilmot forums and drexcel, it's done (well enough for me anyway), see attachment. Now, if only I could get rid of that pesky "Click for action" button which I created whilst testing one of the threads that didn't work (for me).

Thanks all.

[Asterix]

d'uh...the school of basic emailing applies

[Bob Phillips]

Public Sub DeleteDamnButton()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
obj.Delete
Next obj
End Sub

[Asterix]

cheers...most appreciated

[Norie]

drexcel?:eek:

Can you post the URL, or has MrExcel been awarded a doctorate?

[CaptRon]

Open the Control Toolbox Menu (View, Toolbars, Control Box). Then click on the Design Mode icon. Right click on the commandbutton you are wanting to remove and when the option box appears, select Cut.

Ron

[Asterix]

Open the Control Toolbox Menu (View, Toolbars, Control Box). Then click on the Design Mode icon. Right click on the commandbutton you are wanting to remove and when the option box appears, select Cut.

Ron

Thankyou - another glass ceiling removed.

[Asterix]

drexcel?:eek:

Can you post the URL, or has MrExcel been awarded a doctorate?
I promoted him as I was so happy with the service.

[Bob Phillips]

Gosh, does that mean we will be generals soon?

[Asterix]

Gosh, does that mean we will be generals soon?

If my increasingly pathetic questions keep getting resolved in the way they are at the moment and I'll vote for this forum at the next election.

[Asterix]

Tee hee, just when I'd thought I had cracked it...

I have now password protected the VBproject (so that normal users "shouldn't" see the code).

Of course, when the macro comes to export the code...kablaamm, it can't.

Must admit, haven't searched extensively yet but (and assuming I don't do this through the userinterface tag as per one of my other threads) how do I unprotect the VBproject using the VB code (and then reprotect it after I've finished exporting)?

[Asterix]

http://www.ozgrid.com/forum/showthread.php?t=23541

Own question answered:(

[rory]

Question: do you use the Email macro in the original workbook?

[Asterix]

Question: do you use the Email macro in the original workbook?

Been thinking along similar lines. I don't need the macro in the original workbook - it's just a convenient place to store and edit.

Could I just store the email macro in a .bas file outside of Excel?
Does Excel recognise and open .bas files?

[rory]

I was thinking more of using a template file for your first macro. The template can contain the button and code to run the Email macro. Much easier than fiddling with VBProjects, which assumes access to the Project is trusted.

[Asterix]

Maybe thinking too far ahead here, but macro is now in .bas file is my current directory (I can open it in Wordpad).

Ahem...can I protect it from view?:whistle:

[Asterix]

I was thinking more of using a template file for your first macro. The template can contain the button and code to run the Email macro. Much easier than fiddling with VBProjects, which assumes access to the Project is trusted.

Hmm, will have to investigate template files and their affect on the use of the original file (which is a file used for administration).

Cheers

[rory]

You just create a new file based on the template, populate it with the data (instead of creating a new sheet in your current workbook and populating that), then save it somewhere. You can use Windows security to prevent people viewing any type of file.