none
.IncrementTop works differently depending on position (Excel 2010) RRS feed

  • Frage

  • Hello

    I have a problem with placing an object in a proper place. In Excel 2003 and 2007 this function worked perfectly, now with Excel 2010 it's making problems...

    Sub TestMove()
        With ActiveSheet.Shapes("myTextBox")
            .Width = 43
            .height = 23
            .Top = ActiveSheet.Rows(140).Top
            .Left = ActiveSheet.Columns("K").Left
            .IncrementTop -3
        End With
    End Sub

    When myTextBox ist somewhere up in the sheet, for example around the row 20 the IncrementTop works fine and the box is being moved a bit upwards. But when the same myTextBox  is somewhere down in the sheet (like in the example above - row 140) it's beeing moved DOWNWARDS althouh it says MINUS 3.

    Even more mysterious... when i try it with .IncrementTop 0 it also moves my object downwards....... only when I comment it ('.IncrementTop 0) it doesn't move my object.

    Does anyone have an Idea how I can solve this problem? Maybe some way to define the value of IncrementTop in some other unit than points?

    PS. I must add that I have a lot of objects like myTextBox so it won't work to adjust each of them separately. 

    .Top = ActiveSheet.Rows(140).Top <- in the proper code I have the variable instead of the row number and of course variable instead of myTextBox

    Freitag, 10. August 2012 08:00

Antworten

  • .Top = ActiveSheet.Rows(140).Top <- in the proper code I have the variable instead of the row number and of course variable instead of myTextBox

    Doesn't matter, IncrementTop is just a workaround for ".Top = .Top - Increment". Try the code below.

    Andreas.

    Sub TestMove()
      With ActiveSheet.Shapes("myTextBox")
        .Width = 43
        .Height = 23
        .Top = ActiveSheet.Rows(140).Top - 3
        .Left = ActiveSheet.Columns("K").Left
      End With
    End Sub

    Montag, 13. August 2012 10:11
  • I'll try to explain that. The problem is your monitor, resp. your graphic card, this device has 1 Pixel as smallest resolution. The smallest resolution of any object inside Excel (and Excel itself) is 1 Point.

    See ActiveWindow.PointsToScreenPixelsY for more details.

    If you try to move the shapes to position 1580.123456578983654615 then you'll never meet the exact resolution of your graphics settings with the edge of your shape. Means the upper left point of the shape is located on 2 pixels.

    But that is not possible with our hardware, a pixel can have only one color! So Excel must move the shape a little to fit the pixels.

    And thats the reason also why some print outs look different. Your printer has an other resolution as your monitor.

    Andreas.

    Donnerstag, 16. August 2012 15:15

Alle Antworten

  • .Top = ActiveSheet.Rows(140).Top <- in the proper code I have the variable instead of the row number and of course variable instead of myTextBox

    Doesn't matter, IncrementTop is just a workaround for ".Top = .Top - Increment". Try the code below.

    Andreas.

    Sub TestMove()
      With ActiveSheet.Shapes("myTextBox")
        .Width = 43
        .Height = 23
        .Top = ActiveSheet.Rows(140).Top - 3
        .Left = ActiveSheet.Columns("K").Left
      End With
    End Sub

    Montag, 13. August 2012 10:11
  • Andreas, thanks! This one helped! But it didn't solve my problem 100%...

    The shapes that I want to resize and align are in a few different worksheets. When I run the procedure with    .Top = ActiveSheet.Rows(z).Top - 3.5    only the shapes on the currently active sheet are placed properly! The ones on not active sheets are moved with the error described above. I observed that when I read the top position of the shape ( Sheets("Fragen5").Shapes("TextBox8").Top ) shortly after the procedure runs it is ok (1580.5). But when I close the VBA emulator and go to the sheet, the position is different at once  (1566,338)!!! 

    Cany somebody explain it? Any idea how to solve it? I could always activate the sheet in which the shapes are being moved, but it's not a nice solution. 

    Mittwoch, 15. August 2012 12:49
  • I'll try to explain that. The problem is your monitor, resp. your graphic card, this device has 1 Pixel as smallest resolution. The smallest resolution of any object inside Excel (and Excel itself) is 1 Point.

    See ActiveWindow.PointsToScreenPixelsY for more details.

    If you try to move the shapes to position 1580.123456578983654615 then you'll never meet the exact resolution of your graphics settings with the edge of your shape. Means the upper left point of the shape is located on 2 pixels.

    But that is not possible with our hardware, a pixel can have only one color! So Excel must move the shape a little to fit the pixels.

    And thats the reason also why some print outs look different. Your printer has an other resolution as your monitor.

    Andreas.

    Donnerstag, 16. August 2012 15:15
  • Hallo caysee,

    Ich gehe davon aus, dass die Antworten Dir weitergeholfen haben.
    Solltest Du noch "Rückfragen" dazu haben, so gib uns bitte Bescheid.

    Grüße,
    Robert


    Robert Breitenhofer, MICROSOFT  Twitter Facebook
    Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip „Entwickler helfen Entwickler“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.

    Freitag, 24. August 2012 11:13
    Moderator