locked
another Excel 2003 to 2007 issue RRS feed

  • General discussion

  • Hello. I’m looking for some understanding and guidance with respect to a macro I wrote a few years ago.

    The VBA application was written in Excel 2003/Windows XP. It adds one of two different boilerplate contractual disclaimers as the first tab in the Excel file, allows the user to add additional specific nomenclature to the tab, then adds the same nomenclature to the footer of each succeeding tab in case its is printed. 

    The macro runs by launching a form. The user selects one of the two boilerplate contractual disclaimers, (which are hidden sheets in the application), and a specific additional modifier (which is captured in discrete lists linked to which boilerplate sheet is selected). The app then inserts a copy of the selected boilerplate sheet as the first tab in the target file, then inserts the specific additional modifier on both the boilerplate sheet (as a text box) and on each succeeding tab in the target workbook.

    Everything runs fine in Excel 2003. However, our company has migrated most users to Excel 2007/Windows 7 and a strange thing is happening.  

    Boilerplate sheet A works fine. However, on boilerplate sheet B, the additional modifier is inserted, but the text box comes in misformatted (left justified with a white background, instead of centered and no fill), and every attempt at modifying it is unsuccessful.

    So here’s the code. What am I missing?

     After the selection of Boilerplate A or B, and the specific additional modifier, the code branches to:

     If Boilerplate_A Then

        ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 150.75, 291#, 285.75, 19.5).Select

        Selection.Characters.Text = "Boilerplate_A" & DiscreteA_selection

      ElseIf Boilerplate_B Then

         ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 150.75, 291#, 285.75, 19.5).Select

        Selection.Characters.Text = "Boilerplate_B” & other text & DiscreteB_selection

        With Selection.Characters.Font

            .Name = "Arial"

            .FontStyle = "Regular"

        End With

        With Selection

            .HorizontalAlignment = xlCenter

            .VerticalAlignment = xlCenter

            .ShapeRange.Fill.Transparency = 0

            .ShapeRange.Line.Transparency = 100#

            .ShapeRange.Line.Visible = msoFalse

            .ShapeRange.Fill.Visible = msoFalse

        End With

      End If

    the code then goes of to do some other formatting

    Any suggestions?

    Art

    Monday, August 27, 2012 2:05 AM

All replies

  • The line

            .ShapeRange.Line.Transparency = 100#

    should be

            .ShapeRange.Line.Transparency = 1

    Both in Excel 2003 and in Excel 2007, Transparency runs from 0.0 (opaque) to 1.0 (clear). With that change, the code does produce a transparent, centered text box for me.

    Regards, Hans Vogelaar

    Monday, August 27, 2012 5:34 AM
  • In addition to what Hans has said, the formatting of the Textbox is only processed for  Boilerplate_B because it is all within the ElseIf section of code.

    It looks to me like the End If should be moved up as per the following so that the formatting takes place for both the If and ElseIf conditions.

        If Boilerplate_A Then
            ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 150.75, 291#, 285.75, 19.5).Select
            Selection.Characters.Text = "Boilerplate_A" & DiscreteA_selection
        ElseIf Boilerplate_B Then
            ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 150.75, 291#, 285.75, 19.5).Select
            Selection.Characters.Text = "Boilerplate_B” & other text & DiscreteB_selection"
       
        End If  '<----***** Move to here


            '************************************************************************
            'Code between the asterisk lines was only getting processed for Boilerplate_B
            With Selection.Characters.Font
                .Name = "Arial"
                .FontStyle = "Regular"
            End With
                   
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .ShapeRange.Fill.Transparency = 0
                .ShapeRange.Line.Transparency = 1
                .ShapeRange.Line.Visible = msoFalse
                .ShapeRange.Fill.Visible = msoFalse
            End With
            '***********************************************************************
        'End If  '<---**** Delete


    Regards, OssieMac

    Monday, August 27, 2012 6:26 AM
  • thank you - an oversight on my part, but its now corrected

    Art

    Monday, August 27, 2012 4:42 PM
  • Thanks - this problem is now solved. However, a new problem has occurred.

    When I copy the sheet from the application to the target workbook, I get a popup error window that says:

    "Excel cannot insert the sheets into the destiination workbook, because it has fewer rows and columns than the source workbook...."

    Any suggestions?

    Art


    Art

    Monday, August 27, 2012 4:45 PM
  • You can't copy or move a worksheet from an Excel 2007 workbook (with 1,048,576 rows and 16,384 columns) to an Excel 97-2003 workbook (where worksheets have 65,536 rows and 256 columns). You'll have to copy a range of cells instead.

    Regards, Hans Vogelaar

    Monday, August 27, 2012 4:50 PM
  • Thanks for reminding me - I was getting into an Excel instance issue where the application would fail even moving from 2003 to 2007 if the launched file for the session was launched as 2007. If I launch the application with no other Excel session open, there's no problem.

    Art

    Monday, August 27, 2012 8:40 PM