Macro creating a macro

[VBAva]

This is pretty advanced and i am not sure if it can be done but any suggestions would be very welcome...

I have written a program in excel VBA that reads in 3seperate files and creates a single worksheet (in a new workbook) from the values. the data is not much good as is so there are many calculations etc that need to be done.

i have a custom toolbar with 5 buttons, each does calculations or plots a graph. when the file is opened up first with the original data, only 1 button should be enabled (plot Data).

after plot data is done then the other buttons need to be enabled, and for some they can only be used once so they should be disabled after they are used, others can be used many times

now all this is fairly simple when there is only one workbook open, but if there is more than one, with different buttons needing to be enabled, there are big problems. also if a normal workbook is opened , not using my program, none of the buttons should be enabled.

still following?

so i had the idea to insert a worksheet into each workbook that my program opens and keep track of what calculations have been done in it, again it is ok for just one workbook, but as different books are activated the toolbar needs to change. i have no idea what file names the workbooks will have, i can only control the sheet names.

so i think i need some 'This workbook_Activate' function that updates the toolbar depending on what info is in the extra worksheet, the worksheet is hidden so hopefuly the user wont change it.

so when my program creates a workbook, i need to automatically put in something like this in the code for the workbook
Private Sub Workbook_Activate()
Call UpdateToolbar
End Sub

so basically i need to write a macro in the main workbook containing my program that will create a macro or sub in all new workboks created.

is it possible? if the wrong calculation button is selected things go very strange so i need to figure out some way that this can be done

thank you all in advance

 

[TonyJollans]

Hi VBAva,

I'm not entirely sure of all the circumstances you want to do things in and you may be able to do it with Application Events, but this code should do what you ask:

Workbooks("

NewWorkbookName

").VBProject.VBComponents("ThisWorkbook").CodeModule.AddFromString _
    "Private Sub Workbook_Activate()" & vbNewLine & _
    "    Call Update_Toolbar" & vbNewLine & _
    "End Sub"

Enjoy,
Tony

 

[VBAva]

That looks good, ill have a look tomorrow to see if it works, but i think i may have found a way around the problem that i should have thought of earlier ( and will remove the need for some formatting at a later stage). that is to insert the required code in a custom template and use this template for all the workbook created by my program. i think this will make things much easier.
thanks for the code anyway, even if i dont use it at this stage i think it will go into my 'snippets' file for further reference