Text box referencing cell won't update

[Twee]

Hi all,


I created a relativly simple template that will update values based on what hospitals the users pick, driven by lots of vlookup and match/index functions. The data displayed is in pretty text boxes (which references a cell; I wanted to make it look prettier with the use of text boxes rather than being constricted by a cell.


My problem is the text box values don't seem to change with each selection. However, it changes to the correct value after I click on the text boxes itself upon opening the worksheet. I am not sure how to fix this issue. Can anyone help?


I'd like to keep the look as much as possible becase people are already using it and like how it looks. A solution without macros is ideal because most users get confused by .xlsm format.


Thank you for your help. I very much appreciate it.

 

[Clarity]

Hi,


It might be worth seeing if the camera tool gives you what you want. Here is a post by chandoo:


http://chandoo.org/wp/2008/12/02/excel-camera-tool-help/

 

[Hui]

Twee


I don't know how you made your Text Boxes, but often copying one and pasting elsewhere will show a value, but it won't have a linked formula


Select a Text Box at a Time and check the Formula bar which should have a formula like

=$E$4


Adjust as required.

 

[Destructa]

Twee: I had a similar problem, where I wanted to display text from a cell reference in a nicer text box shape. The cells were updating just fine but the shape wouldn't "refresh" until it was selected. I read somewhere that shapes, including text boxes, have "slow code" and don't always catch up to what they are supposed to be displaying.


Unfortunately, the only solution I could figure out does involve a very short macro. You could set it up as a Worksheet SelectionChange event. Since my text boxes were changing based on a scroll control form, I assigned a Macro to the scroll control in addition to its normal action. I didn't find a "focus" or "refresh" or similar method for Shapes, but it seems if you just get it to do anything, it will wake up and show its proper content. Horizontal flip seemed harmless enough. Heck, do 2 flips, just in case. Know your sheet name and shape name.

[pre]

Sub RefreshTB

Sheets("Sheet1").Shapes("TextBox 1").Flip msoFlipHorizontal
Sheets("Sheet1").Shapes("TextBox 1").Flip msoFlipHorizontal

End Sub

[/pre]

 

[SirJB7]

Hi, Twee!

Check this, please:

http://www.2shared.com/file/u7_xw-DL/Text_box_referencing_cell_wont.html

It's a zip with two Excel 2010 files.

First, .xlsx, with a text box (Insert tab, Text box) that shows cell A1 and it works perfectly as I expected.

Second, .xlsm, with the same text box and an ActiveX text box control (Programmer tab, Controls group, Insert icon, ActiveX controls, TextBox... I've got grayed and unavailable the option for Form Controls text box). The same textbox as First still works as expected. For the second added textbox I used the Worksheet_Change event, checked the target address to be cell A1, and set the text box text property... and it doesn't work at all... as I didn't expect... and I don't know why even trapping the code with breakpoint and F8, it seems as if the event is not triggered.

Hope this serves as a guide.

Regards!

 

[NARAYANK991]

Hi ,


If you are referring to your Textbox1 , it seems to be working properly , after setting the LinkedCell property to A1.


You don't even need the Worksheet_Change event code.


Narayan

 

[SirJB7]

@NARAYANK991

You're right! I omitted that. How could I?... is a question for which I've got no answer

Thank you for your correction, NARAYANK991.

@Twee

Just enter in Design Mode (Programmer tab), right click on TextBox1, and in LinkedCell property enter "A1" without quotes, as NARAYANK991 stated and it'll work... should I add "as I expected"?

Sorry for the mistake, Twee.

Regards!