#1
|
|||
|
|||
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) (in my case i need the three shapes to have the same names) Thanks for help |
#2
|
||||
|
||||
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. |
#3
|
|||
|
|||
BobBridges, Thank you
sorry for the delay and i attached my file |
#4
|
||||
|
||||
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. |
#5
|
|||
|
|||
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 |
#6
|
||||
|
||||
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?
|
#7
|
|||
|
|||
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.
|
#8
|
|||
|
|||
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 |
#9
|
||||
|
||||
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. |
#10
|
|||
|
|||
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.
|
|
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 |
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 |
autofit text to shape (msoShapeRectangle) | viuf | PowerPoint | 2 | 02-20-2012 02:11 PM |
Adding text to auto shape and rotating | Hot Mumma | Word | 1 | 06-14-2011 05:15 PM |