Using VBA to Generate Array of Command Buttons

[kc080304]

Hello,

First time poster here and I was hoping to get some assistance for a project.

I am trying to automatically generate an array of ActiveX Command buttons and have them precisely sized and positioned. I have the following code, which works very well, but only for “buttons”.

Sub ButtonArray ()
Dim btn As Button
Dim t As Range
For i = 1 To 6
Set t = ActiveSheet.Range(Cells(i, 3), Cells(i, 3))
Set btn = ActiveSheet.Buttons.Add(t.left, t.top, t.width, t.height)
Next i
End Sub


This code works very well to generate 6 buttons, all the perfect size of a worksheet cell, and located in Column B.

Unfortunately, I need to eventually change the background color of these command buttons, which I think you can do with ActiveX Command Buttons (or even text boxes) but not normal buttons. I also need to have font, change font color, set shape name, and assign a Macro. All of these things you can do with a Command Button or Text Box.

I tried to duplicate the above code for both Command Buttons and Text Boxes but they failed. See below attempts:


Sub TxtBoxArray
Dim TBox As TextBox
Dim t As Range
For i = 1 To 6
Set t = ActiveSheet.Range(Cells(i, 3), Cells(i, 3))
Set TBox = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, t.left,t.top,t.width,.height).Select
Next i
End Sub

Sub CmndButtonArray
Dim btn As CommandButton
Dim t As Range
For i = 1 To 6
Set t = ActiveSheet.Range(Cells(i, 3), Cells(i, 3))
Set btn = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
DisplayAsIcon:=False, left:=t.left, top:=t.top, width:=t.width, height:=t.height).Select
Next i
End Sub

WIth both of the sub routines above, I was able to generate only 1 Command Button (or text box) at the right size and the right location (Cell 1, 3). But then, the program errored as follows and did not continue providing the array:

Run-time error ‘424’ – Object Required – This occurred when I kept the “.select” from line 7
Run-time error “13” – Type Mismatch – This occurred when I deleted the “.select” line 7


As I will eventually need 6 columns or buttons with 30 buttons each, I’d like to stay away from the brute force method of assigning coordinates for each button.

Any guidance / assistance would be greatly appreciated.

Thanks,

Chris

 

[AlphaFrog]

Thank you for this code. It is an amazing start to helping me accomplish my project. Thank you for your help. You saved me from another sleepless night trying to figure out how to fix my problem.
Chris

You're welcome. This vid shows what you can do with Shape-buttons. If you record a maro while you create\edit a shape button, it's a great way to get example code.

https://www.youtube.com/watch?v=HzsEm3wGQsM