Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Show text (not message box) when clicking object? (Excel 2002)

    Show text (not message box) when clicking object?

    I have a flowchart in Excel. I would like to be able to assign a macro to one of the arrow lines. The macro will display a message (some text) near the the arrow when the arrow is double-clicked.

    I know how to do something like this using a message box, but I don't like the outcome because
    a) it's big and ugly
    [img]/forums/images/smilies/cool.gif[/img] its centered default position blocks things I want to keep visible when the text message is displayed ,
    and
    c) it requires a user response (e.g. clicking "Ok") before it goes away.

    Is there something more subtle? I'm thinking of something like a ControlTipText, except that this would not be in a control form.

    I guess if this is not possible by double-clcking the arrow, is it possible when you hold your mouse over the arrow?

    I'd be grateful for any help.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Show text (not message box) when clicking object? (Excel 2002)

    Though not exactly what you are asking for, but a relatively simple way (and no coding required!) would be to add a datavalidation message to a cell (or cells) near the arrow. When the cell is selected the message pops up and when the cell is no longer selected, the popup goes away...

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show text (not message box) when clicking object? (Excel 2002)

    You could use a User Form. With a User Form, you can control the size and location of the form and the format of the text.

    You can also create a Text object directly on a worksheet. I recorded the following code to show the basic steps.

    <code>
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHo rizontal, 336.75, 327.75, _
    45#, 11.25).Select
    Selection.Characters.Text = "ABCD"
    With Selection.Characters(Start:=1, Length:=4).Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    End With
    </code>
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Show text (not message box) when clicking object? (Excel 2002)

    Thanks both of you. Legare, your solution is novel. The thing is, I actually have a lot of lines in the flowchart I'd like to do this with. I can position the box by trial and error with the coordinates, although I'm not sure exactly what they denote. Is there any way to see a "map" or something which shows where these coordinates will be on my worksheet? That way I could know just by looking what coordinates to type in for a given line object.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show text (not message box) when clicking object? (Excel 2002)

    If the "arrows" are relative to a cell on the worksheet, then you can use the Top, Left, Width, and Height properties of the cell to get the location of where you want to put the text object or user form.
    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show text (not message box) when clicking object? (Excel 2002)

    The clicked object has a topleftcell property which you can use to determine where it is...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Show text (not message box) when clicking object? (Excel 2002)

    Thanks, I didn't know that.

    By the way, where do I recognise your name? Have you written somethng with J-Walk?

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show text (not message box) when clicking object? (Excel 2002)

    Yes I have: "Excel 2007 VBAprogramming for Dummies". Well spotted!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •