Need to make 250 buttons - can I write a macro?

[CatyH]

Creating a dashboard that will run 250 different individual tasks (and some buttons that will run sets of these individual tasks), but the process of creating a button and assigning a macro to it (250 times) is tedious.

Wondering if there is CODING way to assign macros to existing buttons OR, better yet, to create a button and assign a macro to it?

Thank you in advance for whatever suggestions and help you may provide!

cat

 

[ranman256]

put all items into a sheet.
[caption], [param]

in a form , show a combo, (or listbox),that shows the items in the sheet above
user picks the CAPTION, (param is hidden)
then the Run button runs the code with that param:

sub btnRun_click()
   sParam = me.lstBox.column(1)
   RunMacro sParam
end sub

sub RunMacro(byval psParam as string)
  msgbox psParam
end sub

 

[CatyH]

Thank you RanMan256, for your reply. I'm not sure I understand how 'param' is used, but will look it up in case I need it in the future.

Here's the code I ended up using...

Sub Button_Brunswick_O_LowCase()
' Gen Brunswick_O_LowCase button
Application.ScreenUpdating = False
Sheets("Graphs -->").Select
ActiveSheet.Buttons.Add(415, 63, 120, 27.5).Select
Selection.OnAction = "Create_Brunswick_O_LowCase"
Selection.Characters.Text = "O_LowCase"
Application.ScreenUpdating = True
End Sub

From this one sub I then programmed in cells that would SUBSTITUTE(nested 4x) out four different parameters [Brunswick_O_LowCase; "O_LowCase", 415, 63] to change each of the 231 buttons' locations, names, and referring macros. Seems to work... super excited that you can use a macro to create buttons!

 

[Norie]

What exactly do all these buttons do?

 

[CatyH]

What exactly do all these buttons do?

LOL - in this case each button is set to reference a macro I wrote that opens a new tab and renames it. The process of renaming the tab automates the formulas that point to a dataset and populates the page creating a cool graph. So, in effect, writing this one macro to create the buttons enables me to select a single or a combination of graphs/tabs to create.

It was a project where I was asked to create 250-ish graphs quickly from a dataset that doesn't yet exist and using three different templates for the graphs that haven't been approved. So getting all this set up ahead of time will enable us to run the graphs very quickly at the end when the final pieces come together.

fun with excel!

 

[Norie]

Are you sure you need 250 separate buttons for that?

What changes in the macro based on which button is pressed?

 

[CatyH]

Are you sure you need 250 separate buttons for that?

What changes in the macro based on which button is pressed?

The name of the tab that is created, the template that is referenced (was one of three templates, now one of twelve templates)


each level-1 button creates a different tab/graph.

each level-2 button creates a set of 7 graphs.

each level-3 button creates a set of 21 graphs.