2

I'm creating formated worksheets with an Excel VBA application and I would like to add programmatically add a button to each worksheet when created. This button must have a macro attached to it, which display the average of the values in a certain range in a special cell, and change the background color of this cell depending on the value of the average. Is it possible to this, and if yes, how can I do it ?

Improve this question

1 Answer 1

Reset to default
3

You can add the Button like this or example:

ActiveSheet.Buttons.Add(52.5, 7.5, 173.25, 41.25).Select 'Arguments are coordinates
Selection.OnAction = "Button_Click"

Now make sure the Button_Click sub has the logic you want.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Ok, this seems to be fine, but apparently the macros are disabled in the worksheet I generate and the code inside the Button_Click sub doesn't execute when I click on the button, how can I programmatically enable the macros for each worksheet I create ?
You can't programmatically enable macros. What would be the point in this as a security feature :). You need to go to options and lower your level of security to enable macros by default.
@otus what's the error message and in which module is the button_click code located?
@Rory my bad then. Strange though.
@AnalystCave.com Not really - you have to have already enabled macros to run the code that then enables code in the relevant workbook, so it's not really a security flaw. ;)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.