Hi,

I have an Excel Add-in(XLAM) which opens a workbook and works on it. Among
the things "Pasting Formula As Values" is one of the things which is
performed on the workbook opened. The workbook opened could be an Excel
97-2003 or Excel 2007 workbook.

Depending upon the worksheets for which this option is applicable, the Addin
loops through the sheets and pastes them as values.

In Paste Formula As values option together with cells, all the shapes (i.e.
textboxes, arrows, callouts, stars and banners, etc), if there are any, are
also pasted as values by removing their formula. Here the Addin loops through
all the shapes on the particular sheet and removes the formula. Earlier it
was observed that when the formula of the shape is removed it loses certain
formatting options such as FonType, FontSize, FontBold, FontItalic,
Underline, FontColor. In order to preserve things, the Add-in now stores all
of these options and removes the formual and then re-applies these options on
the shape.

I found that certain shapes behave differently in Excel 2007 as compared to
Excel 97-2007. So in order to be consistent after some research I have come
up with the following code for preserving and re-appliying the formatting
options after removing the formula for a shape.

With objShp
objHAlign = .TextFrame.HorizontalAlignment
objFontName = .TextEffect.FontName
objFontSize = .TextEffect.FontSize
objFontBold = .TextEffect.FontBold
objFontItalic = .TextEffect.FontItalic
objFontUnderLine = .TextFrame2.TextRange.Font.UnderlineStyle
objFontColor = .TextFrame2.TextRange.Font.Fill.ForeColor.RGB

.DrawingObject.Formula = ""

.TextFrame.HorizontalAlignment = objHAlign
.TextEffect.FontName = objFontName
.TextEffect.FontSize = objFontSize
.TextEffect.FontBold = objFontBold
.TextEffect.FontItalic = objFontItalic
.TextFrame2.TextRange.Font.UnderlineStyle = objFontUnderLine
.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = objFontColor
End With

Now this was working perfect untill the time that one of the
workbooks(97-2003) had some textboxes out of which one did not have the
formatting options specified. Because the textbox did not have formatting
options specified the code pasted above fails while getting the
..TextFrame.HorizontalAlignment property value with "Application-defined or
object-defined error" error.

During investigation I found that the TextFrame.HorizontalAlignment property
throws error if the textbox is not applied with the Horizontal alignment
option beforehand.

I am not sure how to handle this or any other way to achieve this.

Any help on this will be appreciated. Please let me know if you need anything.

Thanks in advance.

-Thx
Anand