Area references highliting from addin

[Ivan V. Inozemtsev]

In Excel when typing formula with references or when selecting chart with
linked data worksheet shows color rectangles, associated with references.
I am writing addin and I need to highlight some areas on worksheet from
addin. How can I use existing highlight functionality? Or I have to write my
own implementation?

 

[Tom Ogilvy]

To the best of my knowledge, this capability is not programmable. However,
if you are writing a custom function, I believe selecting the arguments for
that function in the formula bar will produce the same effect. At least it
does for me.

 

[Bob Phillips]

Ivan,

Attached is a sample of code from the CFPlus add-in that highlights a named
range (similar to what I think you want). Feel free to utilise it

'---------------------------------------------------------------------------
Public Sub CFHighlight(oName As Name, oSheet As Worksheet)
'---------------------------------------------------------------------------
' Function: Highlights all applied conditional formats for this sheet
' Uses shape objects for doing the highlighting
'---------------------------------------------------------------------------
Dim oShape As Shape
Dim CFRange As Range
Dim oCFAreas As Range
Dim dTop As Double
Dim dLeft As Double
Dim dWidth As Double
Dim dHeight As Double
Dim iTxtSize As Long
Dim iArea As Integer

On Error Resume Next

Set CFRange = Range(oName.RefersTo)
If CFRange Is Nothing Then
Exit Sub
End If
iArea = 0
For Each oCFAreas In CFRange.Areas
iArea = iArea + 1
dTop = oCFAreas.Cells(1, 1).Top
dLeft = oCFAreas.Cells(1, 1).Left
dWidth = oCFAreas.Cells(oCFAreas.Rows.Count,
oCFAreas.Columns.Count).Left + _
oCFAreas.Cells(oCFAreas.Rows.Count,
oCFAreas.Columns.Count).Width - dLeft
dHeight = oCFAreas.Cells(oCFAreas.Rows.Count,
oCFAreas.Columns.Count).Top + _
oCFAreas.Cells(oCFAreas.Rows.Count,
oCFAreas.Columns.Count).Height - dTop
iTxtSize = CInt(Application.Min(36, Application.Max( _
Application.Min(dWidth / 2,
dHeight / 30), 8)))

Set oShape = oSheet.Shapes.AddTextbox(msoTextOrientationHorizontal,
dLeft, _
dTop, dWidth, dHeight)
With oShape
.Name = "CFPlus - " & iArea & "-" & Right(oName.Name, 4)
.Fill.ForeColor.SchemeColor = 13
.Fill.Transparency = 0.9
.OnAction = "CFHighlightClick"
With .TextFrame
.Characters.Text = Right(oName.Name, 4)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
If dWidth > dHeight Then
.Orientation = xlHorizontal
Else
.Orientation = msoTextOrientationUpward
End If
.AutoSize = False
End With
With .Line
.Weight = 1#
.DashStyle = msoLineDash
.Style = msoLineSquareDot
.Transparency = 0#
.Visible = msoTrue
.ForeColor.SchemeColor = 54
.BackColor.RGB = RGB(255, 255, 255)
End With
With .TextFrame.Characters(1, .TextFrame.Characters.Count).Font
.Name = "Arial"
.Size = iTxtSize
.Underline = xlUnderlineStyleNone
.ColorIndex = 47
End With
End With
Next

'Clear variables
Set oShape = Nothing
Set CFRange = Nothing
Set oCFAreas = Nothing
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)