Engineer Joe
Well-known Member
- Joined
- Jun 10, 2005
- Messages
- 549
Forgive the lack of correct language, this is new territory for me. For reasons I'd rather not articulate, I'm trying to generate a userform on the fly that creates controls based on ranges or sheets in a workbook. I've done that and so I have a userform where I've created a ton of controls in the userform_initialize event.
So, the buttons are easy to generate, but I'm not sure how to assign them an action - which I'd like to do dynamically. What I'd love is to have something that no matter what button is pressed, it passes the button name on to another sub or something like that. I just don't see anything in the controls that allows to assign a macro or anything. I can actually generate txt files with the desired code in them for each button press and then call the text files up as strings...i know how to do that, which means I think I really just need a way to assign the buttons a sort of "on action," then run this code...
The other option that I'm not opposed to is some vba that generates userforms that are not dynamic or a way to preserve the userform I'm generating dynamically as something more permanent. e.g., i'm generating a bunch of controls at initialization, so there's no actual private subs that get generated just because a button is created.
tl;dr: I'm looking for a way to create userforms based on input from a spreadsheet, and the userforms need to be able to call macros from button presses... A general sort of catch-all funnel macro that takes action based on the button name regardless of which button is pressed would be great instead of having to copy/paste a billion times.
VBA Code:
Set btn = Me.Controls.Add("Forms.CommandButton.1", sht.Name, True)So, the buttons are easy to generate, but I'm not sure how to assign them an action - which I'd like to do dynamically. What I'd love is to have something that no matter what button is pressed, it passes the button name on to another sub or something like that. I just don't see anything in the controls that allows to assign a macro or anything. I can actually generate txt files with the desired code in them for each button press and then call the text files up as strings...i know how to do that, which means I think I really just need a way to assign the buttons a sort of "on action," then run this code...
The other option that I'm not opposed to is some vba that generates userforms that are not dynamic or a way to preserve the userform I'm generating dynamically as something more permanent. e.g., i'm generating a bunch of controls at initialization, so there's no actual private subs that get generated just because a button is created.
tl;dr: I'm looking for a way to create userforms based on input from a spreadsheet, and the userforms need to be able to call macros from button presses... A general sort of catch-all funnel macro that takes action based on the button name regardless of which button is pressed would be great instead of having to copy/paste a billion times.
