Moving shapes around excel using vba

[Evil Red Smuf]

Hi guys can you help?

I've written some code to move a shape around a square path using the row and column numbers to denote when the shape should change direction. However I've come a bit stuck! The little red circle moves around 3/4 of the square but then pops up with an error. Here's the code where the problem occurs:

With activesheet.shapes("RedDot")
For i = 1 to 16*d
Select Case range("A1").value
case "down"
.top = .top - .topleftcell.offset(-i).height
more stuff.....
case "left"
more stuff
End Select
Next
End With

The debug highlights the line beginning .top = .top......

There's a whole host of other movements contained later on in the code but they all work fine, it's just that 1 line. If anyone can tell me what I've done wrong it would make my year!


Submitted via EggHeadCafe - Software Developer Portal of Choice
How To Create a Custom IE8 Accelerator
http://www.eggheadcafe.com/tutorial...a-11c3e7cc2a61/how-to-create-a-custom-ie.aspx

 

[Peter T]

With the limited amount of information given, and the hand written code
snippet, can only guess maybe the row of the topleftcell is less than 1+i

Regards,
Peter T

 

[Evil Red Smurf]

The shape is supposed to move around a specified path in a series of steps the size of which are decided by a different set of code (That bit works fine). The part of the code that I'm struggling with instructs the shape to change direction when it reaches specific points. What I've done is, throughout the movement operation there is a word in A1, and depending on what that word is changes the dirrection setting of the shape. The movement works brililantly until it reaches 1 corner and the shape should then move in a downwards direction. The code I have for that at the moment is:

With ActiveSheet.Shapes("RedDot")
For i = 1 and d*16
Select Case Range("A1")
Case "down"
.Top = .Top + .TopLeftCell.Offset(-i).Height
DoEvents
WAIT
checkposred
Case "right"
.Left = .Left + .TopLeftCell.Offset(-i).Height
DoEvents
WAIT
checkposred
Case "up"
.Top = .Top - .TopLeftCell.Offset(i).Height
DoEvents
WAIT
checkposred
Case "left"
.Left = .Left - .TopLeftCell.Offset(i).Height
DoEvents
WAIT
checkposred
End Select
Next
End With

Apologies for the hand-written code, I'm new to this and I can't see how to add the code any other way.

Many thanks

Evil Red Smurf



Peter T wrote:

With the limited amount of information given, and the hand written
15-Dec-09

With the limited amount of information given, and the hand written code
snippet, can only guess maybe the row of the topleftcell is less than 1+i

Regards,
Peter T

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Create Multiple Dynamic ASP.NET Datagrids
http://www.eggheadcafe.com/tutorial...0-0c1678c61dfe/create-multiple-dynamic-a.aspx

 

[Peter T]

Still not following, what's this -
For i = 1 and d*16
and why the loop. No doubt there's more to it than you have showed.

Are you saying my first guess is not right. If not sure suggest you have
another look at that. IOW, if .TopLeftCell.Offset(-i) tries to reference a
cell above row 1 your code will error. What is (topleftcell.row - i) as a
number.

I the above doest help post the starting topleftcell address of the object
and whatever you mean by For i = 1 and d*16

Wait What?
No idea what 'checkposred' is but only need to write it and the Wait one
time between End Select & Next.

Regards,
Peter T

 

[Rick Rothstein]

For your "right" and "left" cases... do you really want to add the "Height"
property of the .TopLeftCell.Offset(-i) cell, or did you want to be using
the Width property instead?