Automatically generated buttons based on number value with different macros

Question

I have one "release" button which: assigns numbers to specific cells, counts numbers, creates PDF documents, makes history stamps and so on.

I need to automatically generate buttons based on number value with different macros.

For example:

If target cell value = 4, then create 4 buttons with assigned macro 1, 2, 3, 4.

If value = 5, then create 5 buttons and assign macro 1, 2, 3, 4, 5 and so on.

Every assigned macro will be different. (Max count of buttons should be 20.)

Hm, that is problem I don´t know where to start. Maybe it could be VBA form where buttons are generated? I don´t realy know. I am VBA begginer. imgur.com/3yGxByg — Nataniell
– Nataniell, Commented Dec 12, 2016 at 17:55

Brad · Accepted Answer · 2016-12-13 12:22:50Z

1

Within your sheet, you should create the 20 buttons. From here assign them to their macro of equal value, and hide each one. From here you can loop to enable visibility for your current target cell value. In this case A1.

Dim MyVal As Long
MyVal = Range("A1").Value

For i = 1 To MyVal
    ActiveSheet.Buttons("Button " + CStr(i)).Visible = True
Next i

edited Dec 13, 2016 at 12:22

answered Dec 12, 2016 at 17:51

Brad

1,4802 gold badges19 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Nataniell Over a year ago

Hm and where should I put this code? :) And how can I control which buttons want hide? I have multiple buttons on my sheet and don´t want hide everything. THx

Brad Over a year ago

@Nataniell in the upper left hand corner you can see the name of the button and can rename them their aswell. In the scenario I provided, you will need to name the 20 buttons you had in mind from "Button 1" through "Button 20" and assign them to each of your macros. This code goes within a Sub() and you can use another button to execute it if you'd like. You can control the amount of buttons shown by placing a value in A1.

Nataniell Over a year ago

I still trying but if i set number to A1 I have debug error : Object doesn´t support this property or method " ActiveSheet.Shapes("Button " + CStr(i)).Visable = True"

Brad Over a year ago

@Nataniell Change ".Shapes" to ".Buttons"

Brad Over a year ago

@Nataniell Are you sure all your buttons are named correctly? IE: Button 1. Button 2, Button 3, etc? Attempt calling each one individually like so; ActiveSheet.Buttons("Button 1").Visable = True

|

Nathan_Sav · Accepted Answer · 2016-12-12 17:58:03Z

0

You'll have to sort the deleting etc, but something like this

Sub SortButtons()

Dim intButton As Integer
Dim cbNewButton As Button

Const intHeight = 30

For intButton = 1 To 4
    Set cbNewButton = ActiveSheet.Buttons.Add(224.25, (intButton * intHeight) + 20, 90.75, intHeight)
    cbNewButton.OnAction = "Macro" & intButton
    cbNewButton.Text = "Button for Macro " & intButton
    cbNewButton.Name = "OK_TO_DELETE_" & intButton
Next intButton

End Sub

answered Dec 12, 2016 at 17:58

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

2 Comments

Nataniell Over a year ago

That look nice, but i need control number of buttons by cell value. If A1 =4 then I have 4 buttons. If A1 = 3 then I have 3 buttons and each button have different macro. Look picture imgur.com/a/Ms86r

Nathan_Sav Over a year ago

look at how brandons done the loop instigation, I think if you don't know how to tackle that, you should read a little first on what the suggested code is doing also.

Collectives™ on Stack Overflow

Automatically generated buttons based on number value with different macros

2 Answers 2

10 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related