Jun 2, 2011

Create Shape and assign function to it

Sub setup()
Call createButton("Add", "E2", "addNewKeyTopic")
Call createButton("Delete", "E5", "deleteKeyTopic")
End Sub

Sub createButton(sLabel As String, sPos As String, sFunctionName As String)
    ' sPos is the location for the button
   
    Sheets("Input").Shapes.AddShape(msoShapeRectangle, _
    Range(sPos).Left, _
    Range(sPos).Top, _
    Range(sPos).Width * 2, _
    Range(sPos).Height).Select
   
    'Sheets("Input").Shapes.AddShape(msoShapeRoundedRectangle, _
    'Range(sPos).Left, _
    'Range(sPos).Top, _
    'Range(sPos).Width * 2, _
    'Range(sPos).Height).Select
   
    With Selection.ShapeRange(1).TextFrame.Characters
        .Text = sLabel
        .Font.Name = "Arial" ' Change the font name
        .Font.Size = 12 ' Change the font size
        .Font.Bold = True
        .Font.Color = RGB(0, 120, 156) 'Change font color
    End With
   
    With Selection.ShapeRange(1)
        .Fill.ForeColor.RGB = RGB(255, 153, 0) 'Change the background color of the Shape
        .Line.ForeColor.RGB = RGB(0, 120, 156) 'Change the border line color of the Shape
       
        .Line.Weight = 1.5 ' Set Line weight
        .Line.Style = msoLineSingle
       
        .TextFrame.HorizontalAlignment = xlCenter ' Align the text
        .TextFrame.VerticalAlignment = xlCenter ' Align the text vertically
       
        .OnAction = "'" & sFunctionName & "'" ' Assign the Shape button with a function
    End With

End Sub

No comments: