Add Command Buttons to User Form at Runtime

I have a number of macros that I would like to call from a User Form. I want to be able to pass an array of the names of these macros to a function that will runtime create a UserForm with Command Buttons linked to these macros. Is anybody able to assist with this?

Attached is as far as I have got.

I am having difficulty with the following:

1. Create the form dynamically (currently using the first form in ThisWorkbook)
2. Spacing the Cmd Buttons over the form (currently overwriting the same button)
3. Adding the Calls to the Command Buttons

Here's a simple example of 1 and 2.

Please Login or Register  to view this content.

I'll also post this as it's quite handy when playing about with this sort of thing.

Please Login or Register  to view this content.

Thanks Norie. +1

See attached workbook. Almost there.

Two problems left.

I haven't thought through how to initialize the form each time I send an array of macros to it. I have thought of two options:

1. I don't add the form dynamically. Instead I create a named form and programatically add the buttons to this. However the forms code would have to be erased every time before the buttons are added. Is this possible?!
2. I add the form at runtime (like I originally planned) but somehow set the form to self destruct when the form is closed. Is this possible?!

The second problem - just an aesthetic thing - I would prefer to see the buttons spread over the form rather than one long column. I'm not sure how to do this. (Maybe count number of macros to add and then add the buttons as, say, 3/4/5 columns by X no. of rows - depending on how many buttons we will have to play with).

Almost there Only one thing left:

I would prefer to see the buttons spread over the form rather than appearing as one long column. I'm not sure how to do this. (Maybe count number of macros to add and then add the buttons as, say, 3/4/5 columns by X no. of rows - no. of columns chosen depending on how many buttons we will have to play with).

If you want to remove the temporary form after it's closed put this after the code to show the form.

Please Login or Register  to view this content.

Changing the layout of the buttons is straightforward, but the exact code really depends on how you want to show them.

All you need to do is change Top and Left appropriately as you add the buttons.

Please Login or Register  to view this content.

To Norie: Nice work. I like it!

Well I have been making a lot of progress (thanks to Norie ). See attached.

A few things I have planned:

1. Have the buttons placed from left to right then down (instead of down then left to right)
2. Work out a way to have a visually pleasing number of rows & columns. As can be seen from my sub, I am toying with the idea of using mod (once the number of buttons is greater than 10) to determine whether the number of buttons would fit neatly into X number of columns (say 5/7/9 columns). If no mod found on the desired column options then I will have to choose the number of columns that will have the closest fit (so that there isn't a large empty space on the last row). I'm not sure how to go about that yet.

1 You just need to swap things around a bit.

2 What is a 'visually pleasing' no of rows and columns?

1. I've solved the 'left to right'. See attached.

2. A good question. I guess I don't want to see a really elongated user form. (I would like to see the buttons laid out in a ratio that doesn't cause the form to hog the screen). I also don't want to see a single button on the last row. I am stumped over a good way to achieve these two objectives.

For sake of sanity, I think a max of 50 buttons per form would be more then could ever possibly need.
I would prefer to work in columns of 5/7/9. (Odd numbers to help reduce problems with Mod function)

For less than 10 buttons, I have a Select Case choose a preset number of columns & rows (refer code "SetMaxRowsAndCols")

But how do I work button quantities 11 to 50?
I was thinking of a loop. For qty X, does it mod 0 on 5? If yes choose 5 cols. Else does it mod 0 on 7? If yes choose 7. And so on. Obviously not all numbers between 11 to 50 will divide neatly into 5, 7 or 9. So after running the 'perfect division' loop and getting nowhere, it would need to check the next closest match. One gap on the last row is acceptable. So for X + 1, does it mod 0 into 5/7/9? And so on.

I've made some changes to the code.

• Allow different button name to macro name (array is made up of pairs, 1st=button name, 2nd=macro name)
• Delete Form after close (Thanks Norie)
• Unload Form before running sub
• Button width set by len of largest button name string
• Attempted to calc a more pleasing ratio of columns to rows (THIS STILL WIP )

Updated Module below:

Please Login or Register  to view this content.

Some array code is required for the updated module in the previous post (have to do separate post due to exceeded character limit!)

Please Login or Register  to view this content.

Originally Posted by Kyle123

Out of interest, what's the use case for this?

I need a userform to act as a temporary menu for the end user to choose an option from. The number of buttons I require will keep changing over time.

The code is currently working but I have two headaches:

1. Choosing an aesthetic layout of buttons & form height/width ratio. http://www.excelforum.com/excel-gene...d-outputs.html
2. I can't use breakpoints in the sub called from the button. http://www.excelforum.com/excel-prog...t-runtime.html

Originally Posted by Kyle123

Wouldn't it just be easier to add controls dynamically in the normal way without adding them to the project?

I discovered yesterday that I had learnt Userforms wrong. I need to unlearn everything I thought I knew about UserForms and re-learn it correctly

I'm confused "adding controls without adding them to the project"? Could you give a quick demo?

Originally Posted by Izandol

Why do you not use the Ribbon or contextmenus if you require a menu? If you must use a userform may you not use comboboxes or listboxes over so many buttons?

Thanks. Both are good suggestions. And normally I would do just that.

The thing is I have this one-off project where I need to use a UserForm to display a grid of buttons.

mc84excel

Is all this code you've posted just to allow the user to choose an option?

Originally Posted by Norie

Is all this code you've posted just to allow the user to choose an option?

Yes and no - There is more to it than just selecting an option. I can't say much more than that. Anyway I broke down the code into portions and only uploaded the part that I need solving.

(But yes I am guilty of code bloat on this particular project if that's what you are inferring ).

Any chance you could give us a hint of what you are actually trying to do?

Thanks a lot

I've tested it out now and have a few questions/comments:

1. I don't get the "Can't enter break mode" issue! For my learning - Why does your code not cause this?
2. :S It uses the same button name as the sub name. But I should be able to rework the code to pick it up from every second element...
3. The buttons appear as a column.

Thanks for the info Kyle

Originally Posted by Kyle123

Why do you care what the buttons are called?

I have my reasons

Originally Posted by Kyle123

Indeed, I'm not doing it all for you

I wasn't expecting you to. Many thanks for what you provided.

Originally Posted by Kyle123

you've done UDTs now, you've got no excuse...

Almost done learning UDTs But you're right. I can't keep putting it off

SOLVED. I've tweaked Kyles code so that the form does what I want it to.
Below is a generic version for anyone who needs this code.

Form Code:

Please Login or Register  to view this content.

Module code:

Please Login or Register  to view this content.

Class code:

Please Login or Register  to view this content.

There are a few array functions used by the code above, see below:

Please Login or Register  to view this content.