Shape Height Goes Down!

[Guest]

Hello. I have a shape (cylinder) that changes height when I change the value
via a spin button. However, instead of the shape increasing in height, it
decreases from the base (i.e. the shape stretches down the worsheet as I
increase the value).

I need the cylinder to increase in height (upward), not down. Using XP
Office, XL2000. Thanks.

Private Sub SpinButton3_Change()
Dim ws As Worksheet
Dim iLevel As Long

Set ws = ActiveSheet

iLevel = Sheets("Spin").[B23]

With ws.Shapes("AutoShape 11")
.Height = iLevel * 10
End With

End Sub

 

[Guest]

try rotating 180

Sub SpinButton3()
Dim ws As Worksheet
Dim iLevel As Long

Set ws = ActiveSheet

iLevel = Sheets("Spin").[B23]

With ws.Shapes("AutoShape 1")
.Height = iLevel * 10
.IncrementRotation 180#
End With

End Sub

 

[Susan]

jason - i got it to work with your original code, in the proper
direction.

i just had to change this line; you had [ ] around it......

iLevel = Sheets("Spin").Range("B1")

nifty idea, i had to try it out

susan

try rotating 180

Sub SpinButton3()
Dim ws As Worksheet
Dim iLevel As Long

Set ws = ActiveSheet

iLevel = Sheets("Spin").[B23]

With ws.Shapes("AutoShape 1")
.Height = iLevel * 10
.IncrementRotation 180#
End With

End Sub

Hello. I have a shape (cylinder) that changes height when I change the value
via a spin button. However, instead of the shape increasing in height, it
decreases from the base (i.e. the shape stretches down the worsheet as I
increase the value).

I need the cylinder to increase in height (upward), not down. Using XP
Office, XL2000. Thanks.

Private Sub SpinButton3_Change()
Dim ws As Worksheet
Dim iLevel As Long

Set ws = ActiveSheet

iLevel = Sheets("Spin").[B23]

With ws.Shapes("AutoShape 11")
.Height = iLevel * 10
End With

End Sub- Hide quoted text -

- Show quoted text -

 

[Guest]

???
Susan:Is the rotation bar pointing up or down? The height always moves away
from the rotation bar. the () or [] didn't make a difference on my excel
worksheet. when the rotation bar is on the top of the part, the part grows
down the page. when the rotation bar is on the bottom of the part, the part
grow up the page. where up is towards row 1 and down is toward row 65,536.

jason - i got it to work with your original code, in the proper
direction.

i just had to change this line; you had [ ] around it......

iLevel = Sheets("Spin").Range("B1")

nifty idea, i had to try it out

susan

try rotating 180

Sub SpinButton3()
Dim ws As Worksheet
Dim iLevel As Long

Set ws = ActiveSheet

iLevel = Sheets("Spin").[B23]

With ws.Shapes("AutoShape 1")
.Height = iLevel * 10
.IncrementRotation 180#
End With

End Sub

Hello. I have a shape (cylinder) that changes height when I change the value
via a spin button. However, instead of the shape increasing in height, it
decreases from the base (i.e. the shape stretches down the worsheet as I
increase the value).

I need the cylinder to increase in height (upward), not down. Using XP
Office, XL2000. Thanks.

Private Sub SpinButton3_Change()
Dim ws As Worksheet
Dim iLevel As Long

Set ws = ActiveSheet

iLevel = Sheets("Spin").[B23]

With ws.Shapes("AutoShape 11")
.Height = iLevel * 10
End With

End Sub- Hide quoted text -

- Show quoted text -

 

[Susan]

ummm, what rotation bar???


i drew a cylinder auto shape, stuck your code in the worksheet module,
adapted it to fit my autoshape (1), inserted a CONTROL BOX spin
button, changed the cell link to b1, and voila!

yes, it moves upwards, towards row 1.
if you make it big enough that it's hitting the top of row 1, then it
moves downward instead.
the only problem i have with it is that the cylinder doesn't exactly
stay PUT while it's growing & shrinking. each time i increment the
spin button, it moves slightly up & to the left.

this is the code i put in:

Option Explicit

Private Sub SpinButton2_Change()

Dim ws As Worksheet
Dim iLevel As Long

Set ws = ActiveSheet

iLevel = Sheets("Spin").Range("B1")

With ws.Shapes("AutoShape 1")
.Height = iLevel * 10
End With

'Joel said to try this, but it just makes for
'very interesting movement!
'With ws.Shapes("AutoShape 1")
' .Height = iLevel * 10
' .IncrementRotation 180#
'End With

End Sub


i commented out your code, & tried joel's, but all that does is flip
the shape around from top to bottom.
susan

???
Susan:Is the rotation bar pointing up or down? The height always moves away
from the rotation bar. the () or [] didn't make a difference on my excel
worksheet. when the rotation bar is on the top of the part, the part grows
down the page. when the rotation bar is on the bottom of the part, the part
grow up the page. where up is towards row 1 and down is toward row 65,536.

jason - i got it to work with your original code, in the proper
direction.

i just had to change this line; you had [ ] around it......

iLevel = Sheets("Spin").Range("B1")

nifty idea, i had to try it out

susan

try rotating 180
Sub SpinButton3()
Dim ws As Worksheet
Dim iLevel As Long
Set ws = ActiveSheet
iLevel = Sheets("Spin").[B23]
With ws.Shapes("AutoShape 1")
.Height = iLevel * 10
.IncrementRotation 180#
End With
End Sub
:
Hello. I have a shape (cylinder) that changes height when I change the value
via a spin button. However, instead of the shape increasing in height, it
decreases from the base (i.e. the shape stretches down the worsheet as I
increase the value).
I need the cylinder to increase in height (upward), not down. Using XP
Office, XL2000. Thanks.
Private Sub SpinButton3_Change()
Dim ws As Worksheet
Dim iLevel As Long
Set ws = ActiveSheet
iLevel = Sheets("Spin").[B23]
With ws.Shapes("AutoShape 11")
.Height = iLevel * 10
End With
End Sub- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Susan]

i'd be happy to send you the workbook if that would help..........
susan

 

[Guest]

Susan: Its all RELEATIVE to the direction the part was placed on the
spreadsheet.. if you rotate the part 180 degrees it will grow in the
opposite direction. if yourotate it 90 degrees it will grow left and right.
the simple solution for Jason is to rotate the part in the correct directtion
so it grows the way he wants it to.

One pill makes you larger
And one pill makes you small,
And the ones that mother gives you
Don't do anything at all.
Go ask Alice
When she's ten feet tall.

And if you go chasing rabbits
And you know you're going to fall,
Tell 'em a hookah smoking caterpillar
Has given you the call.
Call Alice
When she was just small.

When the men on the chessboard
Get up and tell you where to go
And you've just had some kind of mushroom
And your mind is moving low.
Go ask Alice
I think she'll know.

When logic and proportion
Have fallen sloppy dead,
And the White Knight is talking backwards
And the Red Queen's "off with her head!"
Remember what the dormouse said:
"Feed your head. Feed your head. Feed your head"

An English mathmetician by the name of Charles Dodgson wrote a children’s
book on the subject of higher dimensional geometry and parallel universes.
Under a pen name of Lewis Carrol, he wrote Alice’s Adventures in Wonderland
in 1865. we are all familiar with the bit when Alice chases the white rabbit
down an Einstein-Rosen bridge into another universe. But it was called a
rabbit whole. Of coarse dodgson was unaware what sort of mechanism would
cause such a tunnel to join our wirld with another because it was before
relativity, quantum mechanics and modern cosmology. The story was based soley
on geometrical ideas about how space could be curved and how 2 spaces could
link together in some higher dimensional hyperspace. 50 years later Einstein
showed that such a curvature of space occurs wherever there is strong enough
concentration of mass (or energy). Theory of gravity (general relativity)
provided the physical basis for such tunnels to other worlds.

 

[Susan]

all i know is when i changed it to your code, when i incremented the
spin button it flipped the cylinder around 180 degrees WHILE it grew.
the next time i clicked the spin button, it flipped around 180 degrees
again, and grew again (or shrunk again, depending on which way i
clicked the spin button).

perhaps it's time for me to shuddup now. lol

i guess i don't think in more than 2 dimensions, especially when it
comes to excel!!!!

susan

 

[Nick Hebb]

[Note: I tried posting this before, but it didn't seem to go through.
My apologies if this ends up as a double post.]

Would the rotate trick fail the second time, since it would rotate
again?

I would do the following to keep the bottom position static, but
change the Top property based on the new Height:

Private Sub SpinButton3_Change()

Dim ws As Worksheet
Dim shp as Shape
Dim sBottom as Single

Set ws = ActiveSheet
Set shp = ws.Shapes("AutoShape 11")

sBottom = shp.Top + shp.Height

Application.ScreenUpdating = False
shp.Height = Sheets("Spin").[B23] * 10
shp.Top = sBottom - shp.Height
Application.ScreenUpdating = True

Set shp = Nothing
Set ws = Nothing

End Sub

You might also want to add some code to handle a situation where the
height grows to the point where Top < 0.

HTH,

Nick Hebb
BreezeTree Software
http://www.breezetree.com

 

[Susan]

<whisper> shhhhhhhhh i'm not really here
it worked for me
the moving upward & to the left stopped when the shape was decreasing
in height, but on the upwards direction, it still shifts slightly with
each incrementation.

susan

 

[Guest]

This code worked for me:

Private Sub SpinButton1_Change()
Dim iDiff As Long

Application.ScreenUpdating = False
iDiff = Me.Shapes("Cylinder").Height - Me.SpinButton1.Value
Me.Shapes("Cylinder").Height = Me.SpinButton1.Value
Me.Shapes("Cylinder").Top = Me.Shapes("Cylinder").Top + iDiff
Application.ScreenUpdating = True
End Sub

I added a Spinbutton to a worksheet and did not link it to a cell.
You can certainly link it to a cell if you wish.

Note that the bottom will change a small amount due to the bottom and top
getting "rounder", -this is due to the pseudo parallax being imposed on the
shape as it changes. But overall the impression is that the cylinder is
"growing" in the up direction.

Note that the top of screen is 0, positive direction is down, negative
direction is up.


If the orientation of the shape is changed, the code won't work, as is. You
would have to use trigonometry to determine the new settings based on the
rotation of the shape. Remember vectors?

 

[Susan]

Private Sub SpinButton2_Change()

Dim iDiff As Long


Application.ScreenUpdating = False
iDiff = Me.Shapes("AutoShape 1").Height - Me.SpinButton2.Value
Me.Shapes("AutoShape 1").Height = Me.SpinButton2.Value
Me.Shapes("AutoShape 1").Top = Me.Shapes("AutoShape 1").Top +
iDiff
Application.ScreenUpdating = True

End Sub


variables changed to fit my sheet........
it works, as in goes up & down with the spin button, but the movement
of the shape is worse than it was previously.
with this code:


Private Sub SpinButton2_Change()

Dim ws As Worksheet
Dim shp As Shape
Dim sBottom As Single

Set ws = ActiveSheet
Set shp = ws.Shapes("AutoShape 1")

sBottom = shp.Top + shp.Height

Application.ScreenUpdating = False
shp.Height = Sheets("Spin").Range("B1") * 10
shp.Top = sBottom - shp.Height
Application.ScreenUpdating = True

Set shp = Nothing
Set ws = Nothing

End Sub


the shape stays put while going down, and moves slightly going up.
i'm using windows 2k XP & excel 2k.
i've seen something in the newsgroup about this issue of moving up &
to the left.... but darned if i can find anything right this minute.

susan

This code worked for me:

Private Sub SpinButton1_Change()
Dim iDiff As Long

Application.ScreenUpdating = False
iDiff = Me.Shapes("Cylinder").Height - Me.SpinButton1.Value
Me.Shapes("Cylinder").Height = Me.SpinButton1.Value
Me.Shapes("Cylinder").Top = Me.Shapes("Cylinder").Top + iDiff
Application.ScreenUpdating = True
End Sub

I added a Spinbutton to a worksheet and did not link it to a cell.
You can certainly link it to a cell if you wish.

Note that the bottom will change a small amount due to the bottom and top
getting "rounder", -this is due to the pseudo parallax being imposed on the
shape as it changes. But overall the impression is that the cylinder is
"growing" in the up direction.

Note that the top of screen is 0, positive direction is down, negative
direction is up.

If the orientation of the shape is changed, the code won't work, as is. You
would have to use trigonometry to determine the new settings based on the
rotation of the shape. Remember vectors?

Hello. I have a shape (cylinder) that changes height when I change the value
via a spin button. However, instead of the shape increasing in height, it
decreases from the base (i.e. the shape stretches down the worsheet as I
increase the value).

I need the cylinder to increase in height (upward), not down. Using XP
Office, XL2000. Thanks.

Private Sub SpinButton3_Change()
Dim ws As Worksheet
Dim iLevel As Long

Set ws = ActiveSheet

iLevel = Sheets("Spin").[B23]

With ws.Shapes("AutoShape 11")
.Height = iLevel * 10
End With

End Sub- Hide quoted text -

- Show quoted text -

 

[Susan]

aha!

http://groups.google.com/group/micr...&q=anchor+shape&rnum=8&hl=en#eb41aaad24117b80

if you anchor the shape, it stops moving (other than up & down like
it's supposed to)!

Private Sub SpinButton2_Change()

Dim ws As Worksheet
Dim shp As Shape
Dim sBottom As Single

Set ws = ActiveSheet
Set shp = ws.Shapes("AutoShape 1")

With ws.Shapes("AutoShape 1")
.Left = .Parent.Columns(3).Left
End With

sBottom = shp.Top + shp.Height

Application.ScreenUpdating = False
shp.Height = Sheets("Spin").Range("B1") * 10
shp.Top = sBottom - shp.Height
Application.ScreenUpdating = True

Set shp = Nothing
Set ws = Nothing

End Sub


susan

Private Sub SpinButton2_Change()

Dim iDiff As Long

Application.ScreenUpdating = False
iDiff = Me.Shapes("AutoShape 1").Height - Me.SpinButton2.Value
Me.Shapes("AutoShape 1").Height = Me.SpinButton2.Value
Me.Shapes("AutoShape 1").Top = Me.Shapes("AutoShape 1").Top +
iDiff
Application.ScreenUpdating = True

End Sub

variables changed to fit my sheet........
it works, as in goes up & down with the spin button, but the movement
of the shape is worse than it was previously.
with this code:

Private Sub SpinButton2_Change()

Dim ws As Worksheet
Dim shp As Shape
Dim sBottom As Single

Set ws = ActiveSheet
Set shp = ws.Shapes("AutoShape 1")

sBottom = shp.Top + shp.Height

Application.ScreenUpdating = False
shp.Height = Sheets("Spin").Range("B1") * 10
shp.Top = sBottom - shp.Height
Application.ScreenUpdating = True

Set shp = Nothing
Set ws = Nothing

End Sub

the shape stays put while going down, and moves slightly going up.
i'm using windows 2k XP & excel 2k.
i've seen something in the newsgroup about this issue of moving up &
to the left.... but darned if i can find anything right this minute.

susan

This code worked for me:

Private Sub SpinButton1_Change()
Dim iDiff As Long

Application.ScreenUpdating = False
iDiff = Me.Shapes("Cylinder").Height - Me.SpinButton1.Value
Me.Shapes("Cylinder").Height = Me.SpinButton1.Value
Me.Shapes("Cylinder").Top = Me.Shapes("Cylinder").Top + iDiff
Application.ScreenUpdating = True
End Sub

I added a Spinbutton to a worksheet and did not link it to a cell.
You can certainly link it to a cell if you wish.

Note that the bottom will change a small amount due to the bottom and top
getting "rounder", -this is due to the pseudo parallax being imposed on the
shape as it changes. But overall the impression is that the cylinder is
"growing" in the up direction.

Note that the top of screen is 0, positive direction is down, negative
direction is up.

If the orientation of the shape is changed, the code won't work, as is. You
would have to use trigonometry to determine the new settings based on the
rotation of the shape. Remember vectors?

Hello. I have a shape (cylinder) that changes height when I change the value
via a spin button. However, instead of the shape increasing in height, it
decreases from the base (i.e. the shape stretches down the worsheet as I
increase the value).
I need the cylinder to increase in height (upward), not down. Using XP
Office, XL2000. Thanks.
Private Sub SpinButton3_Change()
Dim ws As Worksheet
Dim iLevel As Long
Set ws = ActiveSheet
iLevel = Sheets("Spin").[B23]
With ws.Shapes("AutoShape 11")
.Height = iLevel * 10
End With
End Sub- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -