+ Reply to Thread
Results 1 to 7 of 7

Text Box value set to a cell does not update when grouped

  1. #1
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Harrisburg, PA
    MS-Off Ver
    Office 365 MSO (16.0.10730.20264) 32-bit
    Posts
    159

    Text Box value set to a cell does not update when grouped

    Hi All.

    Excel 2013 - don't know if this problem exists in other versions.

    Ok... Here it is. I have a text box referencing a cell and grouped with other text boxes and shapes. The text box and the reference cell are on the same sheet. When the cell is updated, the text box updates. But, when I copy the sheet to a new sheet, the text box does update when the cell on the new sheet is changed. The text box does reference the proper cell in the new sheet.

    On the newly created sheet, if I select the text box and hit F2, then hit enter, it starts working again - like re-entering the formula.

    If I do not group all of the many text boxes and shapes together, then copy the sheet to a new sheet, the text box updates when the referenced cell is changed - works properly.

    If I copy the group and paste it onto the same sheet or different sheet, the text box updates properly with the referenced cell. It only does not work when the text box is grouped and I copy the sheet to a new sheet (ctrl-drag the tab, or right click on the tab - move or copy)

    I need these grouped together, because the many text boxes and shapes will need to be copied to a different sheet later and it would be annoying to try to have the users select all shapes and text boxes prior to copy.

    Thanks for the help in advance.
    Steve

  2. #2
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Harrisburg, PA
    MS-Off Ver
    Office 365 MSO (16.0.10730.20264) 32-bit
    Posts
    159

    Re: Text Box value set to a cell does not update when grouped

    One other thing. If I replace the text boxes with form labels, it works as expected regardless if it is grouped or not.

  3. #3
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Harrisburg, PA
    MS-Off Ver
    Office 365 MSO (16.0.10730.20264) 32-bit
    Posts
    159

    Re: Text Box value set to a cell does not update when grouped

    Like I said before, Form Labels do work but formatting is not sufficient and so do ActiveX labels reference a cell.

  4. #4
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Harrisburg, PA
    MS-Off Ver
    Office 365 MSO (16.0.10730.20264) 32-bit
    Posts
    159

    Re: Text Box value set to a cell does not update when grouped

    Ok... This one is solved. 4 hrs of reading info from others all over Google. When a text box is grouped you must use the group item of the shape. Then select the text box and set the function equal to itself and Excel corrects it. It looks like that when it is copied, excel turns it into text by removing the space after the equals sign in the formula bar. By resetting the formula to itself, excel corrects it.

    Please Login or Register  to view this content.
    Steve

  5. #5
    Registered User
    Join Date
    12-17-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Text Box value set to a cell does not update when grouped

    Steve - I am having the same problem, and I can't follow your solution since I'm not experienced in VBA. Is this code specific to your problem and would need modification to use in other contexts?

  6. #6
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Harrisburg, PA
    MS-Off Ver
    Office 365 MSO (16.0.10730.20264) 32-bit
    Posts
    159

    Re: Text Box value set to a cell does not update when grouped

    Hi Doug.

    If you only knew a little about VBA your could use the code in the For Each...Next loop above.

    Like I said above, if you select the text box, hit F2 to edit the value and hit enter, excel fixes it. The code above looks at all groups and resets the formula to itself and excel corrects it.

    I suggest reading up on vba and you will be much more powerful user.

    Steve

  7. #7
    Registered User
    Join Date
    12-17-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Text Box value set to a cell does not update when grouped

    Appreciate the quick response, Steve. I also noticed that just "refreshing" the formulas would re-link them to the original tab, but unfortunately since I was planning to copy/paste this quite a few times, and each one has about 100 such text boxes (i.e. custom data labels I've added manually to about 10 different charts), that would have been a bit too onerous to refresh them all.

    I've rethought my whole approach instead, but again, thanks for the response. The time has definitely come to find myself a VBA course, as I'm starting to find this gap hurting me more often.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 12
    Last Post: 01-10-2014, 07:29 AM
  2. Replies: 8
    Last Post: 09-06-2013, 10:55 AM
  3. Update excel based on userform text box update
    By sivakumar123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2013, 01:01 AM
  4. [SOLVED] Update Cell text (ie title) based on Worksheet location
    By nomoremacs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-26-2012, 03:10 AM
  5. Editing text in text box grouped within a trapezoid
    By Linking to specific cells in pivot table in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-16-2005, 05:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1