Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can You Reset a shape's Left & Top position
Hi All
I was wondering if you can reset a shapes top & left position after it is rotated to reflect its new position? Thanks in Advance Jason |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can You Reset a shape's Left & Top position
What do you mean by "reset"? Exactly what are you attempting to do?
-- Rick (MVP - Excel) "Jason Zischke" wrote in message ... Hi All I was wondering if you can reset a shapes top & left position after it is rotated to reflect its new position? Thanks in Advance Jason |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can You Reset a shape's Left & Top position
Hi Rick
What I mean is for example I place a rectangle on a worksheet then rotate it 90 degrees, its left & top position still talks to the position it was before it rotates. Is there a way to change the property. Thanks Jason "Rick Rothstein" wrote: What do you mean by "reset"? Exactly what are you attempting to do? -- Rick (MVP - Excel) "Jason Zischke" wrote in message ... Hi All I was wondering if you can reset a shapes top & left position after it is rotated to reflect its new position? Thanks in Advance Jason |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can You Reset a shape's Left & Top position
Hi Jason,
Is the following what you are after. With ActiveSheet.Shapes("Rectangle 1") .IncrementLeft 10 .IncrementTop 10 .IncrementRotation 30 End With Note that negative values are used to move up or left and rotate counter-clockwise positive values for the other directions. -- Regards, OssieMac "Jason Zischke" wrote: Hi Rick What I mean is for example I place a rectangle on a worksheet then rotate it 90 degrees, its left & top position still talks to the position it was before it rotates. Is there a way to change the property. Thanks Jason "Rick Rothstein" wrote: What do you mean by "reset"? Exactly what are you attempting to do? -- Rick (MVP - Excel) "Jason Zischke" wrote in message ... Hi All I was wondering if you can reset a shapes top & left position after it is rotated to reflect its new position? Thanks in Advance Jason |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can You Reset a shape's Left & Top position
Hi OssieMac
Thanks for your quick responce. Unfortunately that isn't what I'm looking for. I want to be able to talk to the new left and top position of a shape after it has been rotated rather then the left and top position before it was rotated? Thanks Jason "OssieMac" wrote: Hi Jason, Is the following what you are after. With ActiveSheet.Shapes("Rectangle 1") .IncrementLeft 10 .IncrementTop 10 .IncrementRotation 30 End With Note that negative values are used to move up or left and rotate counter-clockwise positive values for the other directions. -- Regards, OssieMac "Jason Zischke" wrote: Hi Rick What I mean is for example I place a rectangle on a worksheet then rotate it 90 degrees, its left & top position still talks to the position it was before it rotates. Is there a way to change the property. Thanks Jason "Rick Rothstein" wrote: What do you mean by "reset"? Exactly what are you attempting to do? -- Rick (MVP - Excel) "Jason Zischke" wrote in message ... Hi All I was wondering if you can reset a shapes top & left position after it is rotated to reflect its new position? Thanks in Advance Jason |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can You Reset a shape's Left & Top position
Excel rotates a shape around the center of the shape. It is simple to put
the upper left corner of the shape back to its original position by saving the old top and left properties before you rotate and then putting the upper left corner back to its original position With ActiveSheet.Shapes("Rectangle 1") oldleft = .Left oldtop = .Top .Rotation = 25 .Left = oldleft .Top = oldtop End With "Jason Zischke" wrote: Hi OssieMac Thanks for your quick responce. Unfortunately that isn't what I'm looking for. I want to be able to talk to the new left and top position of a shape after it has been rotated rather then the left and top position before it was rotated? Thanks Jason "OssieMac" wrote: Hi Jason, Is the following what you are after. With ActiveSheet.Shapes("Rectangle 1") .IncrementLeft 10 .IncrementTop 10 .IncrementRotation 30 End With Note that negative values are used to move up or left and rotate counter-clockwise positive values for the other directions. -- Regards, OssieMac "Jason Zischke" wrote: Hi Rick What I mean is for example I place a rectangle on a worksheet then rotate it 90 degrees, its left & top position still talks to the position it was before it rotates. Is there a way to change the property. Thanks Jason "Rick Rothstein" wrote: What do you mean by "reset"? Exactly what are you attempting to do? -- Rick (MVP - Excel) "Jason Zischke" wrote in message ... Hi All I was wondering if you can reset a shapes top & left position after it is rotated to reflect its new position? Thanks in Advance Jason |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can You Reset a shape's Left & Top position
Hi again Jason,
After some testing I now see what you mean. Joel's comment that the shape rotates on its axis is correct and while the shape appears to have altered its left and top positon, reading the values shows them as unchanged and therefore resetting them to the old values does nothing. The following code uses maths to relocate a rectangle shape. Firstly makes it a square then rotates and then returns it to a rectangle and resets the left position. I haven't gone into it to the nth degree and so it only works with rotation from zero to 90 degrees. however, it might point you in the right direction to achieve what you want. To test the code, create a rectangle shape from the drawing toolbar on the active sheet. (Ensure that it is Rectangle 1). Sub RotateRectangleShape() 'NOTE: Not all variables required in test 'Left for future reference only 'Code only works for rotation from zero to 90 degrees 'Will require Case statement and test for current 'rotation position and adjust maths to cope 'with rotation from/to other positions. Dim dblLeft As Double Dim dblTop As Double Dim dblWidth As Double Dim dblHt As Double Dim dblRot As Double Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") With shp dblLeft = .Left dblTop = .Top dblWidth = .Width dblHt = .Height dblRot = .Rotation 'Make rectangle into square .Height = dblWidth 'Rotate .IncrementRotation 90 'Return to rectangle .Height = dblHt 'Reset left coordinate .Left = dblLeft - (dblWidth - dblHt) / 2 End With End Sub Just for interest I used the following code to test what occurs when a rectangle shape is rotated. As for the previous code create a Rectangle shape on the active sheet. Sub ShapeRotationTest() Dim dblLeft As Double Dim dblTop As Double Dim dblWidth As Double Dim dblHt As Double Dim dblRot As Double Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") With shp dblLeft = .Left dblTop = .Top dblWidth = .Width dblHt = .Height dblRot = .Rotation MsgBox "Left = " & dblLeft & vbCrLf & _ "Top = " & dblTop & vbCrLf & _ "Width = " & dblWidth & vbCrLf & _ "Height = " & dblHt & vbCrLf & _ "Rotation = " & dblRot .IncrementRotation 90 '.Left = dblLeft 'does nothing '.Top = dblTop 'does nothing MsgBox "Left = " & .Left & vbCrLf & _ "Top = " & .Top & vbCrLf & _ "Width = " & .Width & vbCrLf & _ "Height = " & .Height & vbCrLf & _ "Rotation = " & .Rotation End With End Sub -- Regards, OssieMac |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can You Reset a shape's Left & Top position
OssieMac:
I think yoiur solution only works because you are rotating by 90 degrees. Here is the general solution Let x = original left position Let y = original top position Let h = height of figure let w = width of figure let R = radius of figure sqrt[(1/2h**2)+(1/2w**2) let p = original angle of figure arctan(-1 * h/w) The minus 1 is required because the left position is negative to the center and the y is positive from the center. h and w are absolute numbers The center of the figure is then Let cx = x + 1/2w Let cy = y + 1/2h, note negative y direction is positive on the screen because positive pixels (x,y) is in the negative y direction. The formula for the origianal top left corner of the figure with respect to the center is Left = cx + Rcos(p) Top = cy + Rsin(p) Now if you add a rotation d to the above formulas Left = cx + Rcos(p + d) Top = cy + Rsin(p + d) The distance the left and top will move is the diference between the orignal and new postions delta left = Rcos(p + d) - Rcos(p) delta top = Rsin(p + d) - Rsin(p) The new Left and Top are the following x2 = x + Rcos(p + d) - Rcos(p) y2 = y + Rsin(p + d) - Rsin(p) Note the above formulas don't reduce since sin(p + d) = sin p cos d + cos p sin d cos(p + d) = cos p cos d - sin p sin d Note : When you rotate 90 degrees Sin A = cos B sin(p + 90) = sin p cos 90 + cos p sin 90 sin(p + 90) = cos p cos(p + 90) = cos p cos 90 - sin p sin 90 cos(p + 90) = sin p "OssieMac" wrote: Hi again Jason, After some testing I now see what you mean. Joel's comment that the shape rotates on its axis is correct and while the shape appears to have altered its left and top positon, reading the values shows them as unchanged and therefore resetting them to the old values does nothing. The following code uses maths to relocate a rectangle shape. Firstly makes it a square then rotates and then returns it to a rectangle and resets the left position. I haven't gone into it to the nth degree and so it only works with rotation from zero to 90 degrees. however, it might point you in the right direction to achieve what you want. To test the code, create a rectangle shape from the drawing toolbar on the active sheet. (Ensure that it is Rectangle 1). Sub RotateRectangleShape() 'NOTE: Not all variables required in test 'Left for future reference only 'Code only works for rotation from zero to 90 degrees 'Will require Case statement and test for current 'rotation position and adjust maths to cope 'with rotation from/to other positions. Dim dblLeft As Double Dim dblTop As Double Dim dblWidth As Double Dim dblHt As Double Dim dblRot As Double Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") With shp dblLeft = .Left dblTop = .Top dblWidth = .Width dblHt = .Height dblRot = .Rotation 'Make rectangle into square .Height = dblWidth 'Rotate .IncrementRotation 90 'Return to rectangle .Height = dblHt 'Reset left coordinate .Left = dblLeft - (dblWidth - dblHt) / 2 End With End Sub Just for interest I used the following code to test what occurs when a rectangle shape is rotated. As for the previous code create a Rectangle shape on the active sheet. Sub ShapeRotationTest() Dim dblLeft As Double Dim dblTop As Double Dim dblWidth As Double Dim dblHt As Double Dim dblRot As Double Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") With shp dblLeft = .Left dblTop = .Top dblWidth = .Width dblHt = .Height dblRot = .Rotation MsgBox "Left = " & dblLeft & vbCrLf & _ "Top = " & dblTop & vbCrLf & _ "Width = " & dblWidth & vbCrLf & _ "Height = " & dblHt & vbCrLf & _ "Rotation = " & dblRot .IncrementRotation 90 '.Left = dblLeft 'does nothing '.Top = dblTop 'does nothing MsgBox "Left = " & .Left & vbCrLf & _ "Top = " & .Top & vbCrLf & _ "Width = " & .Width & vbCrLf & _ "Height = " & .Height & vbCrLf & _ "Rotation = " & .Rotation End With End Sub -- Regards, OssieMac |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can You Reset a shape's Left & Top position
Hi again Jason and Joel,
To Jason: As you can see the maths part of this can be complex or it might be quite easy depending on exactly what is required. Therefore perhaps you would like to share a little more information otherwise we are really flying in the dark. What sort of shapes are you working with? How much do you want to rotate them? Is it in multiples of 90 degrees or are you using intermediate rotations? Have you considered resetting dimensions in lieu of rotation? To Joel: I can see you put some real effort into the maths. I tried to implement it in test VBA code but failed. Not sure if it is me or if there is an error in the maths logic. Perhaps you would like to create some test code similar to the example that I posted. -- Regards, OssieMac |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can You Reset a shape's Left & Top position
The proof I posted is simple geometery that any high schooler should know.
Can you post the test code you wrote. The error is porbably a sign error on my part. To understnd what I did addume the center of the figure is in the center of a coordinate axis (x,y). I used zero degreees to be on the positive x-axis and positive rotation in the counter-clockwise direction. The original upper left corner of the figure would then be (-1 * width/2, height/2). the screen coordinates are in pixels since left, top, width, and height are al in pixels. The (0,0) screen locztion is in the upper left corner of the screen. Positive x direction is left to right. Positive y direction is from top to bottom which is opposite from standard conventions. I used standard conventions so I didn't get confused. The differences in convention is only important to keep the direction straight. There is a trick I learned in geometry where we refered to the quandrants of the coordinate axis by "CAST". This stood for Cosine, All, Sine, Tan. The quandrants are as follows 1st - x positive, y positive 2nd - x negative, y positive 3rd - x negative, y negative 4th - x positive, y negative CAST was the order for 4, 1, 2, and 3 quadrants indicating which trig functions were positive. C: 4th quandrant cosine is positive A: 1st quadrant all trig functions are positive S: 2nd quadrant sine is positive T: 3rd quadrant Tan is positive Therefore in the other quadrants the functions were negative. The trig functions in VBA will follow these conventions as long as you use standad conventions where 0 degrees is on the positive x axis and the positive rotation is in the counter-clockwise directions. You then hae to remember to multiply by -1 to compensate for the Y Pixels being in the oppositive direction from the Y coordinate axis. "OssieMac" wrote: Hi again Jason and Joel, To Jason: As you can see the maths part of this can be complex or it might be quite easy depending on exactly what is required. Therefore perhaps you would like to share a little more information otherwise we are really flying in the dark. What sort of shapes are you working with? How much do you want to rotate them? Is it in multiples of 90 degrees or are you using intermediate rotations? Have you considered resetting dimensions in lieu of rotation? To Joel: I can see you put some real effort into the maths. I tried to implement it in test VBA code but failed. Not sure if it is me or if there is an error in the maths logic. Perhaps you would like to create some test code similar to the example that I posted. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I reset the default cell alignment format to Left-Top? | Excel Discussion (Misc queries) | |||
How do you reset the position that pressing CTRL-END moves you to? | Excel Discussion (Misc queries) | |||
Position of X-Axis (Top, Left) | Excel Programming | |||
change sheet position right to left | Excel Discussion (Misc queries) | |||
Position A2 at top-left of window | Excel Programming |