Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Checking for guarded shapesheet cells

30 views
Skip to first unread message

YossiD

unread,
Dec 20, 2010, 4:38:32 AM12/20/10
to
I have modified a macro that I found in this group to resize shapes
according to the text they contain.

A problem arises when one of the selected shapes has already had the
macro applied and the width or height is already guarded. What
function(s) do I use to check if cell height or width is already
guarded and skip to the next iteration of the loop if it is?

The code is below.

Thanks very much.


Sub AutoSizeShape2Text()
'
' Auto resize shape to fit text
'

Dim shp As Shape
Dim sel As Selection
Dim i As Integer

'Set the initial selection
Set sel = ActiveWindow.Selection

'Run through the items in the selection
For i = 1 To sel.Count
Set shp = sel(i)
With shp
.CellsSRC(visSectionObject, visRowXFormOut, visXFormWidth).FormulaU =
"Guard(TextWidth (TheText))"
.CellsSRC(visSectionObject, visRowXFormOut, visXFormHeight).FormulaU =
"Guard(TextHeight (TheText, Width))"
End With
Next i

End Sub

Paul Herber

unread,
Dec 20, 2010, 7:37:57 AM12/20/10
to

Instead of .FormulaU use .FormulaForceU
This will override the guard.


--
Regards, Paul Herber, Sandrila Ltd.
Electronics for Visio http://www.sandrila.co.uk/visio-electronics/
Electrical for Visio http://www.sandrila.co.uk/visio-electrical/
Electronics Packages for Visio http://www.sandrila.co.uk/visio-electronics-packages/

YossiD

unread,
Dec 21, 2010, 12:59:32 PM12/21/10
to
On Dec 20, 2:37 pm, Paul Herber <p...@pherber.com> wrote:

Thanks Paul, that will certainly solve my immediate problem.

For future reference, can you tell me if there's a way to check if a
cell is guarded before trying to change it?

Thanks,

YossiD

Paul Herber

unread,
Dec 21, 2010, 5:24:25 PM12/21/10
to

the guarded cell will contain a formula =GUARD(...) so you could check for the text
=GUARD( in the left 7 text characters, or just always use FormulaForceU.

0 new messages