Access VBA AddShape to spreadsheet

S

Stu W

I've got Access code to create a spreadsheet and to add a shape therein. I
now need to color the shape. How do I do that? Following is the code I've
got so far....

Sub test()


Dim appExcel As Excel.Application
Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Add
appExcel.Visible = True
appExcel.ActiveSheet.Shapes.AddShape Type:=msoShapeOval, Left:=387, Top:=50,
Width:=10, Height:=10

End Sub
 
C

Cindy M.

Hi =?Utf-8?B?U3R1IFc=?=,

Declare a variable to hold the shape, then set it to what you're adding.
Intellisense should be able to help you from there (try the Fill property, for
starters). Roughly

dim shp as Excel.Shape

Set shp = appExcel.ActiveSheet.Shapes.AddShape('params here)
shp.Fill = 'etc.
I've got Access code to create a spreadsheet and to add a shape therein. I
now need to color the shape. How do I do that? Following is the code I've
got so far....

Sub test()


Dim appExcel As Excel.Application
Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Add
appExcel.Visible = True
appExcel.ActiveSheet.Shapes.AddShape Type:=msoShapeOval, Left:=387, Top:=50,
Width:=10, Height:=10

End Sub

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :)
 
S

Stu W

Thanks for the response Cindy. I've modified the code as following, but
still cannot seem to get the coloring to be applied. The shape gets created
ok, but the fill effect doesn't work.

Sub test()

Dim appExcel As Excel.Application
Set appExcel = CreateObject("Excel.Application")

Dim mCircle As Excel.Shape

appExcel.Workbooks.Add
appExcel.Visible = True
Set mCircle = appExcel.ActiveSheet.Shapes.AddShape(Type:=msoShapeOval,
Left:=387, Top:=50, Width:=10, Height:=10)
mCircle.Fill.BackColor.RGB = RGB(255, 255, 0)

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top