PDA

View Full Version : Copying macro and associated button to new file/sheet "in flight"



Asterix
12-12-2007, 08:13 AM
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.

xld
12-12-2007, 08:43 AM
Use a control toolbox button with the code in the worksheet code module, and just copy the sheet to the new book.

Asterix
12-12-2007, 08:54 AM
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.

xld
12-12-2007, 09:23 AM
Just delete the others, then it does!

Asterix
12-12-2007, 09:39 AM
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:

xld
12-12-2007, 09:45 AM
post your workbook and we'll see what we can do.

Norie
12-12-2007, 09:52 AM
Do you really need 2 seperate buttons?

Asterix
12-12-2007, 10:14 AM
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
12-12-2007, 10:15 AM
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.

xld
12-12-2007, 10:27 AM
I'd love to, but that would contravene our security policy (financial industry).

Can you not even post an obfuscated workbook?

Asterix
12-12-2007, 10:34 AM
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
12-13-2007, 03:30 AM
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
12-14-2007, 04:23 AM
[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
12-14-2007, 04:59 AM
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
12-14-2007, 05:12 AM
http://www.vbaexpress.com/kb/getarticle.php?kb_id=275

Asterix
12-14-2007, 05:42 AM
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.

xld
12-14-2007, 05:52 AM
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
12-14-2007, 07:02 AM
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
12-14-2007, 07:13 AM
d'uh...the school of basic emailing applies

xld
12-14-2007, 07:39 AM
Public Sub DeleteDamnButton()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
obj.Delete
Next obj
End Sub

Asterix
12-14-2007, 07:51 AM
cheers...most appreciated

Norie
12-14-2007, 10:12 AM
drexcel?:eek:

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

CaptRon
12-17-2007, 08:02 PM
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
12-18-2007, 12:45 AM
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
12-18-2007, 12:45 AM
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.

xld
12-18-2007, 02:40 AM
Gosh, does that mean we will be generals soon?

Asterix
12-18-2007, 02:45 AM
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
12-19-2007, 08:55 AM
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
12-19-2007, 08:57 AM
http://www.ozgrid.com/forum/showthread.php?t=23541

Own question answered:(

rory
12-19-2007, 09:25 AM
Question: do you use the Email macro in the original workbook?

Asterix
12-19-2007, 09:33 AM
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
12-19-2007, 09:43 AM
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
12-19-2007, 09:44 AM
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
12-19-2007, 09:46 AM
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
12-19-2007, 09:56 AM
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.