API to draw borders

[Guest]

Hi

I wanted to know if there is any programmer API to draw a border around ranges in Excel. I basically want to demaracate cells the way Excel does it while showing ranges involved in a formula. Is this border functionality exposed to developers

On the same lines, I also want to draw arrows across cells, the way I Excel does when it shows precedents and dependents

Any pointers will be highly appreciated

Bharath

 

[Chip Pearson]

Bharath,

You can use the BorderAround method to draw a border around a
range of cells. E.g.,

Range("C3:F5").BorderAround LineStyle:=xlSolid, Weight:=xlMedium

You can draw arrows with code like

Dim SH As Shape
Set SH = ActiveSheet.Shapes.AddLine(beginx:=Range("C3").Left, _
beginy:=Range("C3").Top, endx:=Range("F5").Left, _
endy:=Range("F5").Top)
SH.Line.EndArrowheadStyle = msoArrowheadTriangle

See the XRay game on my web site for lots of examples of drawing
lines on a worksheet: http://www.cpearson.com/excel/download.htm


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Hi,

I wanted to know if there is any programmer API to draw a

border around ranges in Excel. I basically want to demaracate
cells the way Excel does it while showing ranges involved in a
formula. Is this border functionality exposed to developers?

On the same lines, I also want to draw arrows across cells, the

way I Excel does when it shows precedents and dependents.

 

[Guest]

Hi

Thanks Chip. That was useful

The issue I had with BorderAround is, if there's an already existing border in a decorated Excel sheet (most Excel sheets are), then this will override that, and its hard to get back the original one. I felt the way Excel shows borders of ranges while editing formulae was neat, and wanted to know if there's an API for that

Regards
Bharath

 

[Rob van Gelder]

Chip,

That's excellent! It puts all my border drawing routines to shame. I bet you
can imagine what code might look like without knowledge of BorderAround