SOLVED

When I click a cell, how can I use VBA to move my shape to the bottom center of selected cell?

%3CLINGO-SUB%20id%3D%22lingo-sub-2891704%22%20slang%3D%22en-US%22%3EHow%20do%20I%20move%20my%20shape%20to%20the%20bottom%20center%20of%20selected%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2891704%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20move%20my%20shape%20to%20the%20bottom%20center%20of%20selected%20cell%3F%20For%20that%20matter%2C%20would%20it%20be%20much%20different%20to%20any%20of%20the%20sides%20of%20the%20selected%20cell%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rodsan724_0-1635355288106.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F320496i5FAAC330C448F222%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22rodsan724_0-1635355288106.png%22%20alt%3D%22rodsan724_0-1635355288106.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2891704%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2892152%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20move%20my%20shape%20to%20the%20bottom%20center%20of%20selected%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2892152%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1165219%22%20target%3D%22_blank%22%3E%40rodsan724%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHold%20down%20the%20left%20Alt%20key%20while%20moving%20or%20resizing%20the%20shape.%20It%20will%20then%20snap%20to%20the%20cell%20edges.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2892350%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20move%20my%20shape%20to%20the%20bottom%20center%20of%20selected%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2892350%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20thinking%20more%20like%20when%20you%20helped%20me%20with%20VBA%20to%20do%20this.%20So%20it%20would%20be%20in%20the%20Cell%20Selected%20Event%20%5B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fwhen-i-click-a-cell-is-it-possible-to-move-my-shape-at-the%2Fm-p%2F2856783%22%20target%3D%22_self%22%3EPrevious%20help%20you%20gave%20me%3C%2FA%3E%5D.%20I%20reworded%20OP.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2892760%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20move%20my%20shape%20to%20the%20bottom%20center%20of%20selected%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2892760%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1165219%22%20target%3D%22_blank%22%3E%40rodsan724%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAh%2C%20OK.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_SelectionChange(ByVal%20Target%20As%20Range)%0A%20%20%20%20If%20Target.CountLarge%20%26gt%3B%201%20Then%20Exit%20Sub%0A%20%20%20%20With%20Me.Shapes(1)%0A%20%20%20%20%20%20%20%20.Top%20%3D%20Target.Top%20%2B%20Target.Height%20-%20.Height%0A%20%20%20%20%20%20%20%20.Left%20%3D%20Target.Left%20%2B%20(Target.Width%20-%20.Width)%20%2F%202%0A%20%20%20%20End%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Contributor

When I click any cell, how do I use VBA and cell selected event to move my shape to the bottom center of selected cell? For that matter, would it be much different to any of the sides of the selected cell?

 

rodsan724_0-1635355288106.png

 

5 Replies

@rodsan724 

Hold down the left Alt key while moving or resizing the shape. It will then snap to the cell edges.

I was thinking more like when you helped me with VBA to do this. So it would be in the Cell Selected Event [Previous help you gave me]. I reworded OP.

best response confirmed by rodsan724 (Contributor)
Solution

@rodsan724 

Ah, OK.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    With Me.Shapes(1)
        .Top = Target.Top + Target.Height - .Height
        .Left = Target.Left + (Target.Width - .Width) / 2
    End With
End Sub
Wow, you are a master! Is there a resource you recommend for this kind of stuff for beginners like me?