Need help creating custom command buttons using VBA

Question

Hi,

I have a range of dates in z1:z15 (sometime the entire range won't be populated).  Each time I open the workbook I need to loop that range and create multiple command buttons with each .caption being the value of the cell in the range as we loop through.  So, when the workbook is done loading I could have up to 15 command buttons sized the same, place horizontally next to each other and each have a distinct caption from the dates in the range.  To even add more difficulty to it, each command button needs to have code assigned to it.  I have had some success looping through my range and creating the command buttons but am stuck trying to locate them on the page and change their characteristics (backcolor, forcolor, caption).  As well, I have no idea how to add code to each one when I create them (the code will be the same for all the buttons).

Z1         Feb 01 13

Z2         Mar 09 13

Z3         Apr 02 13

...

_______________ _______________ _______________  

Feb 01 13   | | Mar 09 13  | | Apr 02 13  |

______________| |______________| |______________|...............

Each command button needs the same code assigned to it.

Hopefully I am making sense.

Thanks,

Dan

Answer 1

Thanks for the quick response.  I need to be adding command buttons though and have the capability to set their caption, backcolor, forecolor, textsize and add a macro to their commandbutton_click. 

Dan

Answer 2

Hope this helps. 

Option Explicit

Sub Test()

Dim aWS As Excel.Worksheet

Dim myShape As Excel.Shape

Dim myCell As Excel.Range

Set aWS = ActiveSheet

Set myCell = aWS.Range("A10")   'Range where you want to shape to be. 

Set myShape = aWS.Shapes.AddShape(msoShapeOval, Left:=myCell.Left, Top:=myCell.Top, Width:=100, Height:=100)

myShape.TextFrame2.TextRange.Text = "Text on shape"

myShape.OnAction = "Macro1"  'This is the name of the procedure I chose.

End Sub