Results 1 to 10 of 10

Thread: On current?

  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a VBA command for selecting a tab? I have a box (rounded rectangle) containing some text and I would like a portion of that text to reflect a value in a cell on another tab.

    The text would be 'check delivery rate [currently $xxx]' the xxx would be a value from the other cell.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can do this without VBA, in two steps:

    1) Create a formula in a cell that returns the text that you want to display. It doesn't matter whether the cell is on the sheet with the rounded rectangle or on the other sheet (or any other sheet, for that matter). The formula would look like

    ="check delivery rate [currently $"&B2&"]"

    where B2 is the cell containing the amount.

    2) Select the rounded rectangle, click in the formula box, type = and point to the cell with the formula. You'll see something like

    =$E$9

    or

    =Sheet1!$E$9

    where E9 is the cell with the formula and Sheet1 is the name of the sheet containing this cell.

    The rounded rectangle will now display the value of the cell, and it will be updated automatically.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks a lot. I also tried putting some text in the formula (when the shape was selected.) but it didn't work. Why not?

    [quote name='HansV' post='775762' date='18-May-2009 12:11']You can do this without VBA, in two steps:

    1) Create a formula in a cell that returns the text that you want to display. It doesn't matter whether the cell is on the sheet with the rounded rectangle or on the other sheet (or any other sheet, for that matter). The formula would look like

    ="check delivery rate [currently $"&B2&"]"

    where B2 is the cell containing the amount.

    2) Select the rounded rectangle, click in the formula box, type = and point to the cell with the formula. You'll see something like

    =$E$9

    or

    =Sheet1!$E$9

    where E9 is the cell with the formula and Sheet1 is the name of the sheet containing this cell.

    The rounded rectangle will now display the value of the cell, and it will be updated automatically.[/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The only kind of formula that you can use for a shape is a direct reference to a cell, like =$A$1 or =Sheet1!$A$1. You can't use anything else - no literal text, no functions, no calculations.
    That's why you have to create a cell formula that returns the text that you want to display.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans:

    Thanks for that. It provided a solution that had been escaping me for a while!
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  6. #6
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    tHANKS HANS, VERY HELPFUL.

    [quote name='HansV' post='775790' date='18-May-2009 14:13']The only kind of formula that you can use for a shape is a direct reference to a cell, like =$A$1 or =Sheet1!$A$1. You can't use anything else - no literal text, no functions, no calculations.
    That's why you have to create a cell formula that returns the text that you want to display.[/quote]

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It works as you described, of course. However, I was experimenting with having multiple lines in the cell so I can have 'check delivery' then 'check venue' with each of these referring to other cells for values. I cannot get this to work even in a cell, much less a shape. the entry I am making in the cell is

    ="check delivery "&sheet1!k5&alt-enter
    "check venue "&sheet1!k6

    NOTE: the alt-enter above indicates the keystroke I make to force a line break.


    [quote name='HansV' post='775790' date='18-May-2009 14:13']The only kind of formula that you can use for a shape is a direct reference to a cell, like =$A$1 or =Sheet1!$A$1. You can't use anything else - no literal text, no functions, no calculations.
    That's why you have to create a cell formula that returns the text that you want to display.[/quote]

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can insert a line break in the result of a formula by concatenating with CHAR(10):

    ="Check delivery "&Sheet1!K5&CHAR(10)&"Check venue "&Sheet1!K6

    The cell with this formula will only display the line break if you have ticked the "Wrap text" check box in the Alignment tab of Format | Cells...
    The text in the shape will display the line break even if the cell doesn't.

  9. #9
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i RESOLVED MY PROBLEM... I WAS FORGETTING TO PUT 'AROUND THE TAB NAME - The tab name was not exactly sheet1, it had a hyphen in it.

    [quote name='Don_Sadler' post='775946' date='19-May-2009 11:50']It works as you described, of course. However, I was experimenting with having multiple lines in the cell so I can have 'check delivery' then 'check venue' with each of these referring to other cells for values. I cannot get this to work even in a cell, much less a shape. the entry I am making in the cell is

    ="check delivery "&sheet1!k5&alt-enter
    "check venue "&sheet1!k6

    NOTE: the alt-enter above indicates the keystroke I make to force a line break.[/quote]

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Watch that Caps Lock key...

Posting Permissions

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