Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-03-2014, 02:39 AM
exceere exceere is offline Get the TEXT of Clicked Shape Windows 7 64bit Get the TEXT of Clicked Shape Office 2007
Novice
Get the TEXT of Clicked Shape
 
Join Date: Jun 2014
Posts: 7
exceere is on a distinguished road
Default Get the TEXT of Clicked Shape

Hi, i need a help



I have Three Shapes their texts are A, B, and C

These shapes all have the same name (UF_SinlgeList)

i use the following code to get the text of clicked shape

Code:
     ActiveSheet.Shapes(Application.Caller).Select
     
     MsgBox (Selection.Text)
when i click shape A the returned text is B, why???

(in my case i need the three shapes to have the same names)

Thanks for help
Reply With Quote
  #2  
Old 07-04-2014, 11:22 AM
BobBridges's Avatar
BobBridges BobBridges is offline Get the TEXT of Clicked Shape Windows 7 64bit Get the TEXT of Clicked Shape Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

I had to look at this a while, but I think I get it, exceere. You didn't specify the name but let's assume it's not "A", "B" or "C"; I'll just pretend it's "Shape_Name" for this. Follow VBA's reasoning:

1) Shapes is a collection.

2) You can refer to a member of a collection either by the member's name (data type string), or by its index (numeric).

3) Application.Caller is neither a string nor a numeric value; it's an object.

When VBA needs a scalar value but is handed an object, it goes to that object's default property. Now, in a range object, the default property is the text value. If that were the default object for a shape, then when you clicked on shape B VBA would have resolved that to ActiveSheet.Shapes("B"), and given you an error because there is no shape named "B". So I suppose the default property of a shape is its name. All three shapes have the same name, "Shape_Name". So I think VBA is resolving it to ActiveSheet.Shames("Shape_Name"), and picking one at random.

Truthfully I don't think much of this theory, but without looking at your actual workbook it's the best I've got right now. If it's right, you'll either have to think of some way to give each shape a different name after all, or just refer to them by number. Can you post your workbook? Maybe something else will become clear.
Reply With Quote
  #3  
Old 07-05-2014, 09:00 PM
exceere exceere is offline Get the TEXT of Clicked Shape Windows 7 64bit Get the TEXT of Clicked Shape Office 2007
Novice
Get the TEXT of Clicked Shape
 
Join Date: Jun 2014
Posts: 7
exceere is on a distinguished road
Default

BobBridges, Thank you

sorry for the delay

and i attached my file
Attached Files
File Type: xlsm Shape_Select.xlsm (15.8 KB, 20 views)
Reply With Quote
  #4  
Old 07-08-2014, 07:14 AM
BobBridges's Avatar
BobBridges BobBridges is offline Get the TEXT of Clicked Shape Windows 7 64bit Get the TEXT of Clicked Shape Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Well, I see the problem but I'm not sure what to do about it. I was partly right, before, but not totally.

You're using Application.Caller to determine which shape is selected. But Application.Caller, in this routine, is just a character string representing the shape's name; and since you've given all three shapes the same name, Excel is just picking one of them, perhaps the first one it finds.

What you need is to identify the selected shape uniquely, and apparently Application.Caller isn't going to do that for you. If I knew more about Shapes, I could no doubt suggest something; the problem can't be insoluble, after all. But just now the only thing that occurs to me is to have each of the three shapes call a different routine. Assuming the shapes need similar handling, you can have each routine call a central one with an argument specifying the shape.
Reply With Quote
  #5  
Old 07-08-2014, 06:17 PM
excelledsoftware excelledsoftware is offline Get the TEXT of Clicked Shape Windows 7 64bit Get the TEXT of Clicked Shape Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

I took a look at this myself. The issue is just like Bob said. The application.caller is pulling the first shape it can. Your code works flawlessly when the shapes have different names. I started thinking why it would be necessary to have the shapes all have the same name and then it occurred to me. (I am assuming here) It is very easy to duplicate a shape in Excel by clicking and dragging with control or just copying and pasting. It is really annoying to have to go and rename all the shapes. So based off of this assumption I believe it would be helpful for you to have a way to name all of the shapes with a unique but similar name all at once. I wrote 2 procedures for you that will do this. The first renames every shape followed by a number. So if you decided to name the shapes "Default" the first shape would be Default1 then Default2 etc.

The 2nd procedure will name only the selected shapes. This one is a little more detailed since it needs to check if the name has already been taken otherwise there is no point.

Here are the 2 procedures let me know if this helps.

Code:
Sub NameAllTheShapes()
  'Names all of the shapes a set name with a unique number
  Dim shp As Shape
  Dim NewName As String
  Dim x As Integer
  
    NewName = InputBox("What is the new name for the shapes")
    If NewName = "" Then Exit Sub
    
    x = 1
  
    For Each shp In ActiveSheet.Shapes
      shp.Name = NewName & x
      x = x + 1
    Next shp
  
End Sub

Sub NameSelectedShapes()
  'Names all selected shapes
  Dim shp As Shape
  Dim SR As Variant
  Dim CheckShape(0 To 300) As String
  Dim NewName As String
  Dim x As Integer, y As Integer, TotShapes As Integer
    On Error Resume Next
    Set SR = Selection.ShapeRange
    If SR Is Nothing Then
      MsgBox "No shapes selected"
      End
    End If
    On Error GoTo 0 'Enable Errors again
    
  
    NewName = InputBox("What is the new name for the shapes")
    If NewName = "" Then Exit Sub
    y = 0
    x = 1
    
    
    For Each shp In ActiveSheet.Shapes
      CheckShape(y) = shp.Name
      y = y + 1
    Next shp
    TotShapes = y - 2
    
    
    
    'Check if the name is in use
      For Each shp In Selection.ShapeRange
        For y = 0 To TotShapes
          If CheckShape(y) = NewName & x Then
            MsgBox ("The group name " & NewName & " is already in use." _
            & vbLf & "Program ending")
            End
          End If
        Next y
        x = x + 1
      Next shp
      x = 1
            
      For Each shp In Selection.ShapeRange
        shp.Name = NewName & x
        x = x + 1
      Next shp

    
End Sub
Reply With Quote
  #6  
Old 07-08-2014, 08:38 PM
BobBridges's Avatar
BobBridges BobBridges is offline Get the TEXT of Clicked Shape Windows 7 64bit Get the TEXT of Clicked Shape Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Excelled, it occurred to me while I was reading your post "wait a moment; how does his second routine know which shapes are selected? That, after all, was the crux of the OP's question in the first place". So I looked at your code and, lo and likewise behold, you're using Selection.ShapeRange! Now, there's nothing wrong with you suggesting that routine, but my puzzle was trying to figure out how he could make his routine work given (if it's true) that he has to have all the shapes named the same. Seems to me that if there's some reason other than your guess — and I have no suspicions one way or another — then he can use that to solve his problem, no?
Reply With Quote
  #7  
Old 07-08-2014, 09:13 PM
excelledsoftware excelledsoftware is offline Get the TEXT of Clicked Shape Windows 7 64bit Get the TEXT of Clicked Shape Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

Hi Bob, I thought the same thing but the only thing I can get the shaperange to return is the count. Even when I declare a variant anything like shpName = activesheet.selection.shaperange.name or shpName = activesheet.selection.shaperange just returns an empty variable. The other issue is that when a shape is programmed to a macro and then ran the shape does not get selected. At least from what I can tell. If someone can figure out what we can return with shaperange we might be able to do it the other way but I don't think it's possible with all the shapes having the same name. Though I could be wrong.
Reply With Quote
  #8  
Old 07-09-2014, 02:28 AM
exceere exceere is offline Get the TEXT of Clicked Shape Windows 7 64bit Get the TEXT of Clicked Shape Office 2007
Novice
Get the TEXT of Clicked Shape
 
Join Date: Jun 2014
Posts: 7
exceere is on a distinguished road
Default

Hi,

Sorry for being late in replaying, because I was busy

BobBridges and Excelledsoftware

at first thank you for both

what i understood from both that in my case i should use unique name and it is impossiple, or to hard im my case, to use shapes with the same names

so i will try to change my method and try to change the shape names to be a unique using the codes of excelledsoftware

Thank you very much for both to help me and sharing your experiences
Reply With Quote
  #9  
Old 07-09-2014, 03:16 AM
CoolBlue's Avatar
CoolBlue CoolBlue is offline Get the TEXT of Clicked Shape Windows 7 64bit Get the TEXT of Clicked Shape Office 2013
Advanced Beginner
 
Join Date: Jun 2014
Location: Australia
Posts: 40
CoolBlue is on a distinguished road
Default

This one had me intrigued as well. I think you guys are all correct... the only way with Form Controls is with unique names. ActiveX Controls is the only other way but they don't look nice. Well... kinda...

Seems like there is a fundamental issue in that form controls have nice formatting and poor animation and ActiveX controls have nice animation and poor formatting.
Reply With Quote
  #10  
Old 07-09-2014, 06:51 AM
excelledsoftware excelledsoftware is offline Get the TEXT of Clicked Shape Windows 7 64bit Get the TEXT of Clicked Shape Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

You are welcome Exceere, I was hoping that there was a way around but I just don't see one at this time. The other question that may help is the exact reason why you wanted to name all the shapes the same name. Knowing that we could probably supply you with a different solution. If it was just because the shapes are easier to create by giving them the same name then the codes I wrote should make that a breeze. Thanks for getting back to us.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copied shape/text box pastes as an image in Word spectator Word 0 06-04-2014 08:45 AM
Get the TEXT of Clicked Shape how to paste text as shape bsapaka Excel 1 05-01-2014 06:53 AM
Shape+text issue when saving to PDF HMS Word 1 07-12-2013 04:43 PM
Get the TEXT of Clicked Shape autofit text to shape (msoShapeRectangle) viuf PowerPoint 2 02-20-2012 02:11 PM
Get the TEXT of Clicked Shape Adding text to auto shape and rotating Hot Mumma Word 1 06-14-2011 05:15 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:03 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft