VBA Shapes in Presentation

[fedas18]

Hi,

I have built an excel model that I am planning on presenting to the client. As part of the presentation, I was hoping to draw a circle around certain cells. Then have a line connecting the cirlce to a text box with certain text to appear. I would like this to happen when I press a macro botton.

I hope that made sense, but to give you an alternative way of thinking about it, it is kinda like when you watch sports on TV. When the announcer views the replay and draws on the screen to break down the play in to a simple form.

I tried recording a shape but it did not work.

Any tips or advice?

Thanks!

 

[daverunt]

I found this information.

http://msdn.microsoft.com/en-us/library/aa221788(v=office.11).aspx


An Example for a TextBox

Sub Shape_add()
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, [G3].Left, [G3].Top + 5, _
100, 50#).Select
    Selection.Characters.Text = "ABC"
End Sub

You could also look at adding a coloured border or highlighting cells.

 

[fedas18]

Thanks!

Huge help.

Any chance you know how to do an arrow or a line?

 

[fedas18]

Also if I wanted to put a color fill on the text box how would I do that?

 

[daverunt]

Some references for you to search
http://msdn.microsoft.com/en-us/library/bb225671.aspx

http://msdn.microsoft.com/en-us/library/aa432469.aspx

Shape Object
http://msdn.microsoft.com/en-us/library/aa174301(v=office.11).aspx


LineFormat Object
http://msdn.microsoft.com/en-us/library/aa174241(v=office.11).aspx

 

[daverunt]

I found an example

Sub Shape_add()
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, [G3].Left, [G3].Top + h, _
w, 50#).Select
    Selection.Characters.Text = "Text Box"
 
 
With ActiveSheet.Shapes("TextBox 1")
.Fill.Visible = msoTrue
.Fill.Solid
.Fill.ForeColor.RGB = RGB(255, 255, 0)
.Fill.Transparency = 0#
End With
End Sub