Automatically update Text Box(ActiveX Control)

[mpdsal]

I have an Excel 2007 spreadsheet. There are a couple of cells whose value changes based on updates to the spreadsheet. I added a text box to the spreadsheet by using the Insert ActiveX controls drop down within the Developer ribbon. I was able to edit the name and position it where I want it but I need it to update every time a specific cell value changes. How can I do this? I presume it has to be via some VBA code.

For example:

When the contents of cell A1 change I want the text box to show the value each time the cell value changes. My intention is to have the text box appear at the top of the spreadsheet whereas the cell could be several columns over (or hidden). I would need the text box to reflect changes to approximately 4 different cells.

Can this be done? Also, could it be done to show all 4 cell values in one text box? One value under the other?

Thank you very much.

Mark

 

[ChrisM]

You can do this without a textbox and without code by using the Camera tool. It's tricky to get to the camera tool (google it), but once you have it loaded in your toolbar it is a snap to use.

1. Find a hidden area on your worksheet, and link to the 1-4 cells you are interested in. Put them in a little group/cluster.

2. Highlight the 1-4 cells you want to view at the top of your sheet

3. Click the Camera button

4. Your next mouse click will place a live picture of your cells wherever you click, so click somewhere at the top of your sheet.

5. Drag/move/resize the picture to suit. If you drag it right it will blend in perfectly with other cells.

This "picture" will always show a live view of those four cells.

 

[mpdsal]

It's a beautiful thing!

Exactly what I wanted it to do.

Thanks for the speedy reply.

Mark