useing a macro to move a drawing object in excel

stafire_18

Board Regular
Joined
Dec 8, 2004
Messages
162
is there anyway i can create a macro to move a drawing object like an inserted line back to a default location if it is ever moved?
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,229
Hi,

I can see the drawing object name in Name box when I click on the object. It is "Line 1" in my example.
I would first validate if it was moved by checking Left and Top properties of the shape object then replace it if necessary like below:

Code:
  With Sheet1.Shapes("Line 1")
    If .Left <> 10 Or .Top <> 10 Then
      .Left = 10
      .Top = 10
    End If
  End With


I hope it helps.
 

stafire_18

Board Regular
Joined
Dec 8, 2004
Messages
162
for a single shape it works good. but how do i configure it for shapes that are grouped together?
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,229
Same. You will only use the groupped object name instead "Line 1".
Similarly I created another line, which is called as "Line 2" and groupped these 2 lines and when I click on this groupped object, I can see its name is "Group 3" for example. And used following code instead:

Code:
  With Sheet1.Shapes("Group 3") 
    If .Left <> 10 Or .Top <> 10 Then 
      .Left = 10 
      .Top = 10 
    End If 
  End With

Changed only object name in Shapes collection.

Suat
 

Watch MrExcel Video

Forum statistics

Threads
1,118,760
Messages
5,574,088
Members
412,567
Latest member
mm1
Top