Moving a shape to a selected cell

[benntw]

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

 

[John_w]

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:

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.

 

[benntw]

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.

 

[Anthony47]

To unselect the form you could just insert, after Me.Paste and before End If

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

myText = Selection.ShapeRange.TextFrame.Characters.Text

Bye

 

[benntw]

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.

 

[Anthony47]

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:

    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