Type mismatch error
 

Hi I am getting a type mismatch error back on line 2 of the script below. I
have already made a workaround but this is a much neater way of getting the
code to work if i can. I lack understanding of this method and have only
applied it in a couple cases before now so any help would be much appreciated.

Dim Piece As Shapes
For Each Piece In ActiveSheet.Shapes
CurrentTop = Piece.Top
CurrentLeft = Piecee.Left
Piece.Top = 540 - CurrentTop
Piece.Left = 540 - CurrentLeft
Next Piece

Type mismatch error
 

You should be referring to the object as below

Dim Piece As Object
For Each Piece In ActiveSheet.Shapes
CurrentTop = Piece.Top
CurrentLeft = Piece.Left
Piece.Top = 540 - CurrentTop
Piece.Left = 540 - CurrentLeft
Next Piece

If this post helps click Yes
---------------
Jacob Skaria


"Jive" wrote:

Hi I am getting a type mismatch error back on line 2 of the script below. I
have already made a workaround but this is a much neater way of getting the
code to work if i can. I lack understanding of this method and have only
applied it in a couple cases before now so any help would be much appreciated.

Dim Piece As Shapes
For Each Piece In ActiveSheet.Shapes
CurrentTop = Piece.Top
CurrentLeft = Piecee.Left
Piece.Top = 540 - CurrentTop
Piece.Left = 540 - CurrentLeft
Next Piece

Type mismatch error
 

Thank you Jacob that works now.

I know a shape is an object but why cant i refer to it as a shape?

"Jacob Skaria" wrote:

You should be referring to the object as below

Dim Piece As Object
For Each Piece In ActiveSheet.Shapes
CurrentTop = Piece.Top
CurrentLeft = Piece.Left
Piece.Top = 540 - CurrentTop
Piece.Left = 540 - CurrentLeft
Next Piece

If this post helps click Yes
---------------
Jacob Skaria


"Jive" wrote:

Hi I am getting a type mismatch error back on line 2 of the script below. I
have already made a workaround but this is a much neater way of getting the
code to work if i can. I lack understanding of this method and have only
applied it in a couple cases before now so any help would be much appreciated.

Dim Piece As Shapes
For Each Piece In ActiveSheet.Shapes
CurrentTop = Piece.Top
CurrentLeft = Piecee.Left
Piece.Top = 540 - CurrentTop
Piece.Left = 540 - CurrentLeft
Next Piece

Type mismatch error
 

Drop the S from that first Dim line:
Dim Piece As Shapes
should be
Dim Piece As Shape '<-- no trailing S on Shape

But there are lots of shapes that you can't move. You may want to be more
specific with your coding.

Ron de Bruin has lots of info he
http://www.rondebruin.nl/controlsobjectsworksheet.htm




Jive wrote:

Hi I am getting a type mismatch error back on line 2 of the script below. I
have already made a workaround but this is a much neater way of getting the
code to work if i can. I lack understanding of this method and have only
applied it in a couple cases before now so any help would be much appreciated.

Dim Piece As Shapes
For Each Piece In ActiveSheet.Shapes
CurrentTop = Piece.Top
CurrentLeft = Piecee.Left
Piece.Top = 540 - CurrentTop
Piece.Left = 540 - CurrentLeft
Next Piece

--

Dave Peterson

Type mismatch error
 

Ofcourse you can ... as Shape

Activesheet.Shapes is a collection

If this post helps click Yes
---------------
Jacob Skaria


"Jive" wrote:

Thank you Jacob that works now.

I know a shape is an object but why cant i refer to it as a shape?

"Jacob Skaria" wrote:

You should be referring to the object as below

Dim Piece As Object
For Each Piece In ActiveSheet.Shapes
CurrentTop = Piece.Top
CurrentLeft = Piece.Left
Piece.Top = 540 - CurrentTop
Piece.Left = 540 - CurrentLeft
Next Piece

If this post helps click Yes
---------------
Jacob Skaria


"Jive" wrote:

Hi I am getting a type mismatch error back on line 2 of the script below. I
have already made a workaround but this is a much neater way of getting the
code to work if i can. I lack understanding of this method and have only
applied it in a couple cases before now so any help would be much appreciated.

Dim Piece As Shapes
For Each Piece In ActiveSheet.Shapes
CurrentTop = Piece.Top
CurrentLeft = Piecee.Left
Piece.Top = 540 - CurrentTop
Piece.Left = 540 - CurrentLeft
Next Piece

Type mismatch error
 

Dim Piece As Shapes

should be

Dim Piece As Shape ' singular, not plural

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Mon, 17 Aug 2009 07:33:02 -0700, Jive
wrote:

Hi I am getting a type mismatch error back on line 2 of the script below. I
have already made a workaround but this is a much neater way of getting the
code to work if i can. I lack understanding of this method and have only
applied it in a couple cases before now so any help would be much appreciated.

Dim Piece As Shapes
For Each Piece In ActiveSheet.Shapes
CurrentTop = Piece.Top
CurrentLeft = Piecee.Left
Piece.Top = 540 - CurrentTop
Piece.Left = 540 - CurrentLeft
Next Piece