Using VBA to assign a macro to a button

I am currently designing a user form which is populated upon activation with as many buttons as there are items in a variable list.
I am resizing, repositioning, and renaming each button depending on how many items are in the list.
What I need to be able to do and haven't been able to find so far, is how to assign a macro to the button once it has been generated.
I should only need one macro, regardless of how many buttons there are, and the macro itself will just take the button's caption value to then decide what to do next.

Thanks for your help

OK, I think I could probably explain this better, because I think the withevents solution may work, but might even be a little overkill.
I am going to have a dynamic list of names, and for each name, each name will have an entire sheet with the actual name.
I would like to call a form, which would populate itself with as many buttons as names, and then each button would activate the appropriate sheet.
Now I know this would be easier as a listbox, or even straight hyperlinks right now, but partly for aesthetics, and also because I am pretty sure I will have other uses for the same form later in the programming, and would like to have a flexibility to its usage if possible, I would like to make it with a form full of buttons.
I have completed the design stage of it and coded the population and reshaping side of things, I would like to know how to get VBA to recognise which button is being clicked, and take a value from the form upon the button being clicked, which can be used in other parts of my code.