Capturing Shape Name/Text from OnAction property

  How can I capture (assign) the text content of a shape 
(rectangle)into a procedure using the OnAction property 
for the shape?
 I have many shapes (rectangles) on a worksheet, each of 
which is named. When a shape is clicked, I need to assign 
the 'Characters.Text' content of that shape to a variable 
in the procedure called by the OnAction property. I am 
trying to create a usable 'generic' procedure rather than 
having a separate procedure for each shape.
 
0
anonymous (74717)
4/11/2005 7:06:36 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
599 Views

Similar Articles

[PageSpeed] 20

William,

Do you have a separate procedure for each shape,
or does each shape call the same procedure.

Regards,
Jim Cone
San Francisco, USA


"William Bartusek" <anonymous@discussions.microsoft.com> wrote in message 
news:1f5501c53ec9$9558a390$a401280a@phx.gbl...
>   How can I capture (assign) the text content of a shape 
> (rectangle)into a procedure using the OnAction property 
> for the shape?
>  I have many shapes (rectangles) on a worksheet, each of 
> which is named. When a shape is clicked, I need to assign 
> the 'Characters.Text' content of that shape to a variable 
> in the procedure called by the OnAction property. I am 
> trying to create a usable 'generic' procedure rather than 
> having a separate procedure for each shape.
> 
0
jim.coneXXX (771)
4/11/2005 10:51:46 PM
I want to create a common procedure that captures the 
shape name that is clicked (OnAction), then places 
(assigns) the name to a variable in the called procedure. 
Right now I have accomplished this by having a separate 
procedure for each shape. See comment below.

Private Sub Test()
Dim txt As String, c As Range, rng As Range, rw As Long, r 
As Long, ufrm As Object

Worksheets("SERVICE MEASURES-Strategy").Unprotect

Worksheets("SERVICE MEASURES-Strategy").Shapes
("SvcOA_Age").Select 'this is the shape name that 
is 'clicked'
'I want the "OnAction" clicked shape "Character.Text" to 
be captured in a variable in the called procedure rather 
than the pre-coded name "SvcOA_Age" (or whatever the 
Character.Text of the shape) so that I don't need a 
separate procedure for each shape. 

txt = Selection.Characters.Text
'activate the worksheet and named range
Worksheets("STRATEGY GUIDANCE").Activate
Worksheets("STRATEGY GUIDANCE").Range
("STRATEGY_GUID").Activate
rw = Worksheets("STRATEGY GUIDANCE").Range
("STRATEGY_GUID").Rows.Count
'find the Shapes.Text in column 1 of the STRATEGY_GUID 
named range
With Worksheets("STRATEGY GUIDANCE").Range
("STRATEGY_GUID").Columns(1)
Set rng = .Find(txt, LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByColumns, MatchCase:=True)
    If Not rng Is Nothing Then
    r = rng.Rows.Row
    
Set ufrm = SvcMeasure
    With ufrm
    .LblSvcMeasure = Worksheets("STRATEGY GUIDANCE").Range
("B" & r)
    .Guidance.Value = Worksheets("STRATEGY GUIDANCE").Range
("C" & r)
    .Strategy.Value = Worksheets("STRATEGY GUIDANCE").Range
("D" & r)
    .Locate = r
    .Show
    End With
    End If
End With

Worksheets("STRATEGY GUIDANCE").Range("A1").Activate
Worksheets("SERVICE MEASURES-Strategy").Activate
Worksheets("SERVICE MEASURES-Strategy").Protect

End Sub
 >-----Original Message-----
>William,
>
>Do you have a separate procedure for each shape,
>or does each shape call the same procedure.
>
>Regards,
>Jim Cone
>San Francisco, USA
>
>
>"William Bartusek" <anonymous@discussions.microsoft.com> 
wrote in message 
>news:1f5501c53ec9$9558a390$a401280a@phx.gbl...
>>   How can I capture (assign) the text content of a 
shape 
>> (rectangle)into a procedure using the OnAction property 
>> for the shape?
>>  I have many shapes (rectangles) on a worksheet, each 
of 
>> which is named. When a shape is clicked, I need to 
assign 
>> the 'Characters.Text' content of that shape to a 
variable 
>> in the procedure called by the OnAction property. I am 
>> trying to create a usable 'generic' procedure rather 
than 
>> having a separate procedure for each shape.
>> 
>.
>
0
anonymous (74717)
4/12/2005 4:36:21 PM
William,

Assign the "DoThemAll" sub to each shape.
'DoThemAll' determines the text in the shape and then
passes that text in a variable to your main procedure (Test)...
'---------------------------------
Sub DoThemAll()
  Dim strName As String
  Dim strText As String
  strName = Application.Caller
  strText = ActiveSheet.Shapes(strName).TextFrame.Characters.Text
 'Call the main procedure, passing strText to it
  Test strText
End Sub

Private Sub Test(ByRef txt As String)
  Dim c As Range
  Dim rng As Range
  Dim rw As Long
  Dim r As Long
  Dim ufrm As Object

 'Your code
  MsgBox txt
End Sub
'---------------------------------

Regards,
Jim Cone
San Francisco, USA



"William Bartusek" <anonymous@discussions.microsoft.com> wrote in 
message news:05f601c53f7d$c2873570$a601280a@phx.gbl...
> I want to create a common procedure that captures the 
> shape name that is clicked (OnAction), then places 
> (assigns) the name to a variable in the called procedure. 
> Right now I have accomplished this by having a separate 
> procedure for each shape. See comment below.
> 
> Private Sub Test()
> Dim txt As String, c As Range, rng As Range, rw As Long, r 
> As Long, ufrm As Object
> 
> Worksheets("SERVICE MEASURES-Strategy").Unprotect
> 
> Worksheets("SERVICE MEASURES-Strategy").Shapes
> ("SvcOA_Age").Select 'this is the shape name that 
> is 'clicked'
> 'I want the "OnAction" clicked shape "Character.Text" to 
> be captured in a variable in the called procedure rather 
> than the pre-coded name "SvcOA_Age" (or whatever the 
> Character.Text of the shape) so that I don't need a 
> separate procedure for each shape. 
> 
> txt = Selection.Characters.Text
> 'activate the worksheet and named range
> Worksheets("STRATEGY GUIDANCE").Activate
> Worksheets("STRATEGY GUIDANCE").Range
> ("STRATEGY_GUID").Activate
> rw = Worksheets("STRATEGY GUIDANCE").Range
> ("STRATEGY_GUID").Rows.Count
> 'find the Shapes.Text in column 1 of the STRATEGY_GUID 
> named range
> With Worksheets("STRATEGY GUIDANCE").Range
> ("STRATEGY_GUID").Columns(1)
> Set rng = .Find(txt, LookIn:=xlValues, LookAt:=xlWhole, _
>         SearchOrder:=xlByColumns, MatchCase:=True)
>     If Not rng Is Nothing Then
>     r = rng.Rows.Row
>     
> Set ufrm = SvcMeasure
>     With ufrm
>     .LblSvcMeasure = Worksheets("STRATEGY GUIDANCE").Range
> ("B" & r)
>     .Guidance.Value = Worksheets("STRATEGY GUIDANCE").Range
> ("C" & r)
>     .Strategy.Value = Worksheets("STRATEGY GUIDANCE").Range
> ("D" & r)
>     .Locate = r
>     .Show
>     End With
>     End If
> End With
> 
> Worksheets("STRATEGY GUIDANCE").Range("A1").Activate
> Worksheets("SERVICE MEASURES-Strategy").Activate
> Worksheets("SERVICE MEASURES-Strategy").Protect
> 
> End Sub
>  >-----Original Message-----
> >William,
> >
> >Do you have a separate procedure for each shape,
> >or does each shape call the same procedure.
> >
> >Regards,
> >Jim Cone
> >San Francisco, USA

0
jim.coneXXX (771)
4/12/2005 5:38:07 PM
Reply:

Similar Artilces:

Rich Text versus HTML format and attachments
1) Hi, I notice that if I use Rich Text as the format to send any files I attach are attached as an icon with the body of the message rather than when I use html as the format and they are attach under the Attach "section" below the Send line of the email. This messes with a user form I use to email as the icon messes up the format, etc. (in Rich Text). Is this normal? 2) I recently switched to Rich Text from html as default send format as I am led to believe that many large (fortune 500) corporations do not let HTML formatted emails through but automatically convert them to plain t...

Forcing WMP to use the file names I have used
How can I force WMP to use the names I have given my sound files, like "Lecture on Dystopian Futures" or "Rain sounds" rather than the original names they did have like DS400789? It's very irritating of WMP to ignore me like this. In WMP's Options, make sure "Rename music files using rip music settings" (on the Library tab) is checked. Regards -- Tim De Baets http://www.bm-productions.tk FranOnTheEdge wrote: > How can I force WMP to use the names I have given my sound files, like > "Lecture on Dystopian Futures" or &...

Median of Even Set; How to Capture them?
Hi everyone, Say I have a dat set such as: 4 5 6 7 The median is (5+6)/2=5.5! However, I am not interested in the answer here, but the 5 and the 6!!! Is there an excel function or way to do so? Thanks alot, Mike Do you want the output in one single cell or in 2 separate cells? Mike Wrote: > Hi everyone, > > Say I have a dat set such as: > > 4 > 5 > 6 > 7 > > The median is (5+6)/2=5.5! > > However, I am not interested in the answer here, but the 5 and the > 6!!! > > Is there an excel function or way to do so? > > Thanks alot, ...

Excel will not show all text entered in a Cell merged from 9 rows
I have tried to autofit and format cell, but nothing works. I have to manually drag the cell border to make it longer. Hi Pat, Have you Also Tried Wrap Text. All the Best. Paul Yes, that was the first thing. "Paul Black" wrote: > Hi Pat, > > Have you Also Tried Wrap Text. > > All the Best. > Paul > > Autofitting rowheight and merged cells don't play nice. Jim Rech wrote a macro called AutoFitMergedCellRowHeight that you may like: http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkmsftngp05 If you're new to macros, you may wa...

Printing Customer name on Receipts
Hi, I posted a question recently asking how to print customer's name on our receipts especially for credit card transactions where the name is automatically printed on Customer's copy but not our own. Some answers I received lead me to feel that I need to edit some XML files - but the actual answer is much simpler and I wanted to share it here. All you need to do is go to Manager/Database/Registers/Receipt Formats. Choose the "40 Column Receipt Format" and choose to edit the "Sales" receipt. Then in one of the Footer Lines (near bottom) you can just ente...

Outlook 2003 Folder Names Chopped Off
I began using Outlook 2003 with an old format of .pst file. Upon opening Outlook, all folder names apear complete, but once I clicked on any folder, the font jumped in size and many, if not all folder names chopped a few characters, sometimes parts of a character. It did not appear to be systematically resetting a fixed limit, so a longer folder name would have its name chopped by a few characters as would a smaller folder name. I applied all office updates including SP1. I also created a new .pst folder and imported the old .pst file into it. Same behavior in new .pst. Any clues as...

email capture
I'm having a problem with Outlook not pulling the mail off my pop server. When I do a send and receive the mail is identified, "grabs" the messages like it's doing a download but when it reaches the end I'm getting an unknown error message. Any thoughts? 1) First, try to set up Outlook Express to download the email set. Sometimes it has better luck. 2) Another way is to clear the queue manually. This is technical, and not really recommended by anyone, but you may have a stuck message... Telnet to get it... (this method permanently deletes messages before you c...

justified text alignment without hypens?
I want to use a justified text alignment in Publisher, but instead of streaching the line of text it uses hpyens? How do dI disable this feature? I don't want the hypens. Tools | Language | Hyphenation and uncheck automatically hyphenate ... -- Rae Drysdale "irish2mac" wrote: > I want to use a justified text alignment in Publisher, but instead of > streaching the line of text it uses hpyens? How do dI disable this feature? > I don't want the hypens. ...

personalize OrgChart template, stencil and master properties
Hello, I need to adapt the OrgChart Template to get the following features: 1. need to make custom properties to master shapes (so that they are availables each time i drag and drop a master from the stancil to the drawing). This means two things: first remove the default built-in properties (name, title, email, etc), and second add my own properties. I want ONLY my own properties on masters!! 2. need a custom stencil (based on the OrgChart stencil) with master with my custom properties defined at point 1. 3. On the masters' istances on the drowaing I want to display my own custom ...

Capture user selection of font
If I put a font combo box on a Ribbonbar is there a way for me to capture the user's font choice? I need to capture that choice and use it in a macro. Whate *exactly* are you trying to achieve with your macro? -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "dplaut"...

Slanted Text
I would like to make a matrix with slanted column headings. I can slant the text but the text is truncated the above & below rows. How do I get slanted text? Thank you Mike, Have you set the Wrap Text option in Format>Cells>Alignment, and made the row higher? -- HTH Bob Phillips "Mike" <mikedenton81@yahoo.com> wrote in message news:0c0a01c3621d$92c65170$a101280a@phx.gbl... > I would like to make a matrix with slanted column > headings. I can slant the text but the text is truncated > the above & below rows. How do I get slanted text? > >...

Change Data Capture: Preserve capture instance data when adding co
When a new column is added to table that is configured for change data capture (cdc), the capture instance table will not have the new column until cdc is disabled and re-enabled for the source table. In the process the existing capture instance is dropped. I thought I could copy existing data out to a temp table and then copy back using the following SQL. However, other CDC meta information, such as the cdc.change_tables.start_lsn, becomes invalid. How can the capture instance history be preserved, using the same capture instance name, if at all? Thanks, Rich /*...

Named range as chart data reference (error)
Based on Steve Bullen's FunChart1 example (in that particular example, auto-expanding named ranges) I've set up some graphs that used named ranges as their data source. I copied the worksheet of graphs into a new workbook and now have global named ranges that match the graphs' original named ranges (using Jon Peltier's suggestion to use Jan Pieterse et al's Name Manager v4.0, in which I removed all the sheet named ranges which were duplicates, leaving me with just one set of global named ranges) The graphs, which still have their original named range sources, update ...

deleting sheets and shapes
I have a routine that should go through and delete the shapes and other sheets in an Excel workbook. The problem is that the button(shape) that is clicked to perform this operation is not deleted. Also the sheet doesn't want to delete as well. Sub CreateSheet() Dim Shp As Shape, i As Integer, FName, WName FName = ActiveWorkbook.FullName WName = ActiveWorkbook.Path & "\Week" & Range("A2") ActiveWorkbook.Save With thisworkbook.ActiveSheet .Unprotect On Error Resume Next For Each Shp In .Shapes Shp.Visible = False Next Shp A...

Messy Text to Columns
I am trying to fix up a poorly designed Access database. One of the tables has a Next of Kin field in which the typical entry looks like this once it has been exported to Excel: Mary Smith Next of Kin: Notify: Mr. & Mrs. John M. Smith (Parents) (live with Son,Gerard) Mr.Gerard H. Smith (brother) 1234 Brookside BLVD. Kansas City, MO 12345 Tel: 1-816-123-4567 Mrs. Regina Thomas (sister) 123 Beech St. Worcester, PA 12345 Tel. 1-215-123-4567 Within the field there are paragraph entries. I exported it to Excel in hopes of deleting some of the extrane...

Visio 2003: Space shape could not be Auto Sized!!!
Hi There, I am using Visio 2003 with SP2. Following action returns error message every time on Visio 2003.. Please try it: 1- Open Visio 2003 and select "Building Plan" 2- Select "Home Plan" or "Floor Plan" from right pane. 3- Drag and Drop "Room" layout from Shapes on the left pane to the blank page. (Would be good, expanding room shape a little bit.) 4- Drag and Drop "Space" shape on the Room shape that you've already insterted. 5- Finally right click on "Space" shape and select "Auto Size". Bingo! You may see s...

Using range name typed in cell to reference array
I have defined ranges of cells with names (ie TCO71, TCO72). I have a column of data 71, 72 ... I am trying to use a function ie count to use this name. I tried the following with mixed results =count(TCO71) gave correct result =count(a3) with TCO71 typed in A3 gave a zero result =count(indirect(A3)) gave a zero result =count(concatenate("TCO",text(B3,0)) gave a zero result in reality I would like to use a form similar to the last line so I do not have to add another column with the range name. Any help would be appreciated Landmine Landmine Wrote: > I have defined rang...

Capturing date/time modified on a form?
I have a form where staff can go and add student information, I would like to be able to capture when and if the form has been modified. We are running reports on students with certain behaviours right now but we don't know how up-to-date the data is if we don't capture if it's been changed recently. I added TimeModified and DateModified to my main table and put the fields on my form that I need it to capture but I'm not sure how to actually get it to fill those fields once data has been modified, any help would be appreciated. Monique Duane OK, what I've done now...

Prohibiting the change of Author Name on Worksheets
Is there a way I can probihit someone from changing the name of Author that created a Worksheet? Thanks for help. Turnerpla I don't know if you can prevent the change from being made, but you could automatically return it to a pre-set value with a Change Event macro, like Before Close, or Before Save..........include this line of code: ThisWorkbook.BuiltinDocumentProperties("Author").Value = "OriginalAuthorName" Of course replacing the text OriginiaAuthorName with the name of the Author you choose..... But remember, if macros are disabled, this won't w...

Confusing newsgroup name
I notice lots of people misinterpret the name of this particular newsgroup, resulting in many messages that belong in other groups. I'm sure the reason for this is the name of the group: '...public.access' . Wouldn't it be a good idea to change the newsgroup name? Has this ever been tried? Regards, john Given that newsgroups are propagated onto literally thousands of servers around the world, changing names isn't an easy proposition. Microsoft could change the name on their server, but because many people post using other servers, that wouldn't eliminate this ...

Advanced Find / Full-Text Index not working
Server: XCH 2k3 (w/o SP) / Win 2k3 Clients: Outlook 2003/2002 Advanced Find from client's Outlook returns no results using any criteria. Full-text Index working on defined schedule and mailbox store without Event log errors/warnings. Guidelines from "Best practices for deploying FTI" followed for FTI configuration. Strange: Microsoft Search and Gatherer Performance objects do not exist. Or, could this be language settings issue as described in Best practices...? Help? 1.) What is the query that is being sent and are you sure that there are messages that satisfy the req...

Build a sheet name in an Excel formula
I have an Excel 2007 workbook with a summary page and a number of other sheets named sequentially as in Spend(1), Spend(2), Spend(3), etc. On the summary sheet, I have a series of numbers, one per row, that correspond with the sheet numbers. (1, 2, 3, etc.) I am trying to build a formula that would exist on the summary sheet that would return the results of cell G14 from the applicable sheet. (for the number 4, Spend(4), cell G14 or for the number 8, the results from sheet Spend(8), cell G14. I have tried to build it as follows: =?Spend(?&A19&?)!)&G14 (where A19 contains the ...

Paste Text
I often find the need to paste plain (un-formatted) text into a cell. To avoid pasting the formatting I have to: double-click (or F2) into the cell before I paste OR or Rght-Clk -> Paste Special -> Text Can I accomplish this w/ a single key stroke or toolbar click; i.e. paste plain text into the active/current cell w/o having to be in insert mode (F2). I tried to record a macro, but it appears the clipboard is emptied when you start recording because the Paste Special function is grayed out. Thanks Say we make the following macro: Sub oneshot() ActiveCell.PasteSpecial Paste:=xlPast...

Capture signature
I have a Ingenico 6550 signature capture device. When i sign on the device, it didn't capture the name. The display and the card capture worked, only the signature part doesn't work. I set the device name and everything, it loaded the form, and the tab for the signature on the pos poped up. I use the form designer to test the capture device it worked. The signature show on the form designer. I don't know what could go wrong. Could anyone help!!! By the way i am using RMS 1.3. "TJW" wrote: > I have a Ingenico 6550 signature capture device. When i sign on...

Changing reply to address / name
We receive support e-mails to a public folder, which several employees have permission to work (read / reply to). However, when an employee replies it displays their name and e-mail address in the reply. I'd like for their replys to display the generic support e-mail address and support name (instead of my name and e-mail address I want it to display the support name and e-mail address). Is this possible to setup within outlook? I'm also interested in this: I've got around the problem by setting up a unique Exchange account to which the responders have to login in order to...