AutoShape Re-Sizing IS KILLING ME!!!!

[Guest]

I have added an autoshape, then copied an Excel table over to it, and grouped
them together. In VB there is the following code, that brings up the
autoshape whenever someone clicks on a cell in the range of L2:L200, and
anchors the shape to that cell:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Box190 As Shape
Set Box190 = Me.Shapes("Group 190")

If Not (Intersect(Target, Range("L2:L200")) Is Nothing) Then
With Target
Shapes("Group 190").Visible = True
If Selection.Left + Selection.Width + Box190.Width > Rows(1).Width
Then
Box190.Left = Selection.Left - Box190.Width
Else: Box190.Left = Selection.Left + Selection.Width
End If
If Selection.Top + Selection.Height + Box190.Height >
Columns(1).Height Then
Box190.Top = Selection.Top - Box190.Height
Else: Box190.Top = Selection.Top + Selection.Height
End If
Box190.ZOrder msoBringToFront
End With
Else: Shapes("Group 190").Visible = False
End If

The problem is that the autoshape keeps changing sizes, I assume as a result
of other copy/paste/clear actions taken at different times. I have tried
setting the format of each autoshape (including the original shape, the
picture added, and the group) to all variations possible...don't move/size,
etc. When the other macros are run to copy/paste/clear, I set
application.enableevents to false. I can't figure out why the size of the
shapes keeps changing, and was wondering if it had something to do with the
above code .

Am really really frustrated with this and was hoping someone out there could
help me.....thanks so much.....

 

[Guest]

Just a guess but right click on the shape and go to Format -> Properties.
What is the option selected in terms of moving and sizing?

 

[Guest]

Hi, Jim. It is currently, 'Don't move or size with cells'. I've tried all 3
options at various times, and still have the problem.

 

[Guest]

Sorry I did not read you first post carefully enough. The setting you have
(Don't Move or Size) is the correct setting. I just tried your code (which is
generally very good) and it worked flawlessly. I tweaked a couple of things
but nothing that should materially effect the way it runs.

Here is what I came up with. Give it a try but I don 't hold out a lot of
hope that it will fix your problem, since as I said it worked great for me
without any modifications.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Box190 As Shape
Set Box190 = Me.Shapes("Group 190")

If Not (Intersect(Target, Range("L2:L200")) Is Nothing) Then
With Target
Shapes("Group 190").Visible = True
If .Left + .Width + Box190.Width > Rows(1).Width Then
Box190.Left = .Left - Box190.Width
Else
Box190.Left = .Left + .Width
End If
If .Top + .Height + Box190.Height > Columns(1).Height Then
Box190.Top = .Top - Box190.Height
Else
Box190.Top = .Top + .Height
End If
Box190.ZOrder msoBringToFront
End With
Else
Box190.Visible = False
End If

End Sub

 

[Guest]

Thanks, Jim. I'm ready to try anything, including hari-kari.