Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape.TextEffect.HorizontalAlignment throws error
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading range w/ 255+ characters throws error | Excel Programming | |||
Find Throws Error 91 | Excel Programming | |||
workbook.open throws 1004 error | Excel Programming | |||
How do I perform a certain function if VBA throws up an error? | Excel Programming | |||
HorizontalAlignment Error in Excel 97 | Excel Programming |