J

#### Jason Zischke

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

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

J

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

R

What do you mean by "reset"? Exactly what are you attempting to do?

J

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

O

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.

J

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

J

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

O

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

J

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

O

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.

J

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.

Thanks in advance!!

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

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.