aha!

http://groups.google.com/group/micro...41aaad24117b80

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

:D
susan



On Jun 27, 9:12 am, Susan wrote:
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

On Jun 26, 7:32 pm, Steve the large



wrote:
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?

"Jason Morin" wrote:
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 -