Pages

Friday, January 6, 2012

Shape Background Color using VBA

If you have added the shape in the sheet and now you want to change it color , text , font size ,etc using VBA. Snapshot below


Code-

Sub shapes_in_vba()

' Rounded Rectangle 1 IS THE NAME OF SHAPE

'add text to shape
Sheets(1).Shapes("Rounded Rectangle 1").TextFrame.Characters.Text = "ap"
' chnage font color of shape
Sheets(1).Shapes("Rounded Rectangle 1").TextFrame.Characters.Font.Color = vbBlack
'bold text
Sheets(1).Shapes("Rounded Rectangle 1").TextFrame.Characters.Font.Bold = True
' chnage font type
Sheets(1).Shapes("Rounded Rectangle 1").TextFrame.Characters.Font.Name = "comic sans"
' chnage font size
Sheets(1).Shapes("Rounded Rectangle 1").TextFrame.Characters.Font.Size = 15
' fill background color
Sheets(1).Shapes("Rounded Rectangle 1").Fill.ForeColor.RGB = RGB(0, 255, 0)
'if you want to set the background color as the shapestyles snapshot below


And if you want to choose any style from the list all you have to add the number of the style after msoShapeStylePreset like msoShapeStylePreset1, msoShapeStylePreset24, msoShapeStylePreset35 ,etc in below statement

Sheets(1).Shapes("Rounded Rectangle 1").ShapeStyle = msoShapeStylePreset24
End Sub

No comments:

Post a Comment