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

 

[wideboydixon]

You're adding an OLE Object but trying to assign it to a CommandButton type hence the runtime error 13. You need to do this for example:

Sub TxtBoxArray()

Dim t As Range
Dim TBox As OLEObject

For i = 1 To 6
    Set t = Cells(i, 3)
    Set TBox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, DisplayAsIcon:=False, Left:=t.Left, Top:=t.Top, Width:=t.Width, Height:=t.Height)
Next i

End Sub
Sub CmndButtonArray()

Dim t As Range
Dim btn As OLEObject

For i = 1 To 6
    Set t = 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)
Next i

End Sub

WBD

 

[Kyle123]

AS a heads up, this is probably a bad idea. As you specify, you want them precisely positioned and sized, if you do a quick google, you'll see the no-end of problems with ActiveX controls resizing themselves and moving on worksheets. You'd be much better off using either normal Forms buttons or shapes (shapes would give you more control over appearance).

 

[AlphaFrog]

AS a heads up, this is probably a bad idea. As you specify, you want them precisely positioned and sized, if you do a quick google, you'll see the no-end of problems with ActiveX controls resizing themselves and moving on worksheets. You'd be much better off using either normal Forms buttons or shapes (shapes would give you more control over appearance).

I second that. Also, you can't really "assign" a macro to ActiveX command buttons. Each one will need an event procedure in the worksheet code module or you create a class module to intercept the click events.

You can change the font, font color, font size, and name of a Form-type button. Not sure about background color. You can also do all those things with a shape.

 

[kc080304]

Thank you for getting back to me. I will definitely try making this change this evening to see if it is what I am looking for. I appreciate your help!!!
Chris

 

[kc080304]

Kyle123 and AlphaFrog,

Thank you for the heads up. It would have been frustrating going down a road that is destine for failure. I may just give it a try, though. I really need two things... (1) I need to change the background color (which I don't think you can do with normal Forms buttons) and (2) I need a caption. I wasn't able to figure out how to put a caption on a shape file which is why I transitioned to a text box. I colud format the text box to what I needed AND I got the caption. Will the code posted above by Kyle123, specific to text boxes, still have the same issues? If shape files are better to use, is it possible to put a caption on a shape?

Again, I appreciate your help / guidance.

Chris

 

[Norie]

Chris

If you add a shape to a worksheet you can add text to it by right clicking and selecting Edit Text, or just double clicking it.

 

[AlphaFrog]

Here's a start to add Rectangles as buttons. You can make them look better.

[color=darkblue]Sub[/color] ButtonArray()
    [color=darkblue]Dim[/color] cell [color=darkblue]As[/color] Range, i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Range("B2:B6")
        [color=darkblue]With[/color] ActiveSheet.Shapes.AddShape(msoShapeRectangle, Left:=cell.Left, Top:=cell.Top, Width:=cell.Width, Height:=cell.Height)
            i = i + 1
            .TextFrame.Characters.Text = "My Button " & i [color=green]'Caption[/color]
            [color=darkblue]With[/color] .TextFrame.Characters.Font
                .Name = "Tahoma"
                .FontStyle = "Regular"
                .Size = 8
                .Strikethrough = [color=darkblue]False[/color]
                .Superscript = [color=darkblue]False[/color]
                .Subscript = [color=darkblue]False[/color]
                .OutlineFont = [color=darkblue]False[/color]
                .Shadow = [color=darkblue]False[/color]
                .Underline = xlUnderlineStyleNone
                .Color = vbRed  [color=green]'Font color[/color]
            [color=darkblue]End[/color] [color=darkblue]With[/color]
            .Fill.ForeColor.RGB = vbGreen   [color=green]'Background color[/color]
            .OnAction = "MyButton_Click"
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color] cell
[color=darkblue]End[/color] [color=darkblue]Sub[/color]


[color=darkblue]Sub[/color] MyButton_Click()
    [color=darkblue]With[/color] ActiveSheet.Shapes(Application.Caller) [color=green]'The shape that called this macro[/color]
        MsgBox .TextFrame.Characters.Text, , "Button that called this macro"
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] Sub

 

[kc080304]

WOW... I can't figure how I missed that last night. Thank you for pointing that out.
Chris

 

[kc080304]

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