Moving a shape to a selected cell

benntw

Board Regular
Joined
Feb 17, 2014
Messages
222
Office Version
  1. 365
Platform
  1. Windows
I have a couple of questions about shapes including counting the specific text in them. I have a code that I was helped with some time ago for moving shapes to specific cells. What I want to do is instead of moving it , when click on I want it to copy and paste it into the cell that I select with the mouse. Here is my current code for just moving the shape.

Sheet 1

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Ob Is Nothing Then
Ob.Left = Target.Left - Ob.Width / 2 + Target.Width / 2
Ob.Top = Target.Top - Ob.Height / 2 + Target.Height / 2
End If
Set Ob = Nothing
End Sub



Module 1

Option Explicit
Public Ob As Object


Sub oMove()


Application.ScreenUpdating = False


Set Ob = ActiveSheet.Shapes(Application.Caller)

Application.ScreenUpdating = True


End Sub

I will have a bunch of the same shapes with different text in them as selection sitting on the page. My intent is to place the specific ones in cells across my sheet under a date range that runs horizontal. I want to be able to count in each row the specific text. I want to use shapes instead of having spreadsheet with drop downs in over a 1000 cells. Can anyone help on this ?

Thank you
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
What I want to do is instead of moving it , when click on I want it to copy and paste it into the cell that I select with the mouse.
Try this:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Ob Is Nothing Then
        Ob.Copy
        Target.Select
        Me.Paste
    End If
    Set Ob = Nothing
End Sub
Please use CODE tags.
 
Upvote 0
Thank you. It copies from cell to cell like I want it to. Once it copies to another cell it is still selected like I would manually copy it. Is there a code like a normal " Application.CutCopyMode = False " ? I'm not familiar with objects. Is there a way to count the text in the shape too that is in the column or row ? I really appreciate the help you have already given me.
 
Upvote 0
To unselect the form you could just insert, after Me.Paste and before End If
Code:
Target.Select

I am not sure about what you mean for "Is there a way to count the text in the shape too that is in the column or row"; if you wish to access the text embedded in the selected shape then you might try
Code:
myText = Selection.ShapeRange.TextFrame.Characters.Text
Bye
 
Upvote 0
Works perfect for copy and paste. I plan on having different names on the objects. I was wondering if there was a way to do a counta formula to count the different names in the objects.
 
Upvote 0
Works perfect for copy and paste. I plan on having different names on the objects. I was wondering if there was a way to do a counta formula to count the different names in the objects.
Still in the fog...
Do you mean that you would like to assign to the new form a specific name?
In this case, I normally use the cell address in combination with a constant string. For example:
Code:
    If Not Ob Is Nothing Then
        Ob.Copy
        Target.Range("A1").Select           '***
        Me.Paste
        Selection.ShapeRange.Name = "ZCZC_" & Target.Address(0, 0)
        Target.Select
    End If
Note the suggested modification in the line marked *** (useful in case the user select a range of cells, non a single cell).

Bye
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top