Solved: Dynamic placement of buttons

[Slothboi]

I have a macro that generates several tables of data. At the end of the tables, i want to add a new button that can re-run the macro.

The number of fields tracked is variable, so I have the macro select a cell two rows down and do selection.activesheets.button.add. At this point, if I don't know what the button number of the new button will be, how can I select the button to assign interior text, and assign a macro to it?

Thanks!
Slothboi

[acw]

Hi

Try something like


[VBA]
Set NewButton = ActiveSheet.Buttons.Add(ActiveCell.Top, ActiveCell.Width, ActiveCell.Left, ActiveCell.Width)
NewButton.Characters.Text = "MyText"
NewButton.OnAction = "MyMacro"
[/VBA]

HTH

Tony

[lucas]

Would you consider using a floating toolbar to do this instead of the problems your up against with adding so many buttons and linking code.

If so check out this thread(post #9 specifically)
http://www.vbaexpress.com/forum/showthread.php?t=10994

instead of using them in a sub you can use them in workbook open to create and before close to delete the menu

[Slothboi]

Thanks for the excellent suggestions. Both solutions work, but I am going forward with the toolbar solution. It is a more elegant implementation for what I want to do.

Thanks again!