Displaying Excel Cell values in Text Boxes

[bobomutt]

How can you automatically display a cell value in a text box? I want to have a scroll bar on a chart that modifies a cell value displayed somehow on the chart (text box is my un-educated best guess...). I want the chart to be its own individual worksheet, and I want to allow users to scroll the value right from a scroll bar on the chart, so they can see how the value effects the chart (sensitivity analysis).

Any suggestions on how to accomplish are much appreciated!

 

[SkipVought]

Hi,

I often use this technique on Chart Sheets.

1. On the Source Data Table sheet for your CHART, turn on the AutoFilter. The AutoFilter is how the chart will be controlled.

2. Select the chart. Tools/Options/Chart - Check plot visible cells only

3. On a separate sheet, make a list of the unique values from the Source Data Table that will control each chart. You can use the Data/Filter/Advalced filter to filter UNIQUE values (no criteria) to another location. I like to name my lists using Insert/Name/Create - create names in top row.

4. On the Chart, activate the FORMS toolbar and select Combo Box. Place the combobox where you like.

5. Format the Combo Box control. Reference the LIST that you created in the ListFillRange. Create the CellLink reference by selecting the new sheet containing your list, in an empty cell that is NOT adjacent to your list. When the user selects an item from the list, the item index will be in this cell link reference.

6. In a cell adjacent to the cell link reference, enter this lookup formula to get the value that was selected...
[tt]
=INDEX(the list reference,the cell link reference,1)
[/tt]
6. Activate your Source Data Table sheet and MACRO RECORD selecting a value from the AutoFilter (one of the values that will be in your list on the other sheet). Turn OFF the recorder.

7. alt+F11 toggles between the workbook and the VB Editor. Locate Module1 that contains the VBA code that you just recorded. Post back your code to get further help.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[bobomutt]

Hi Skip,

Pretty nifty methodology. However, I am still interested in displaying a cell value on the chart. For instance, I have a calculated value that I want to display along with a few series on a chart.

Best,

Doug

 

[AndyPope]

Here is an explanation of linking the chart title to a cells.

http://www.andypope.info/tips/tip001.htm

The same principle can be applied to other chart text elements and embeded textboxes.

 

[SkipVought]

...and I want to allow users to scroll the value right from a scroll bar on the chart, so they can see how the value effects the chart (sensitivity analysis).

[sub]my emphasis[/sub]


How does my tip NOT address this requirement?

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[AndyPope]

Your reply does address that part of the question.

If does not though answer

However, I am still interested in displaying a cell value on the chart.

 

[SkipVought]

The question was NOT addressed to you, Andy. It was directed to Doug, who stated in his last post...

"Pretty nifty methodology. However, I am still interested in displaying a cell value on the chart. For instance, I have a calculated value that I want to display along with a few series on a chart."

The technique for DISPLAYING cell values on a chart is pretty simple.

Changing the chart by way of a control, either of the PLOTTED values or DISPLAYED values, is a tad more complex. Either one (PLOTTED or DISPLAYED) can be accomplished applying the techniques I described in detail.

Can you perceive the relationship?

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[AndyPope]

Hi Skip,

I read the whole question differently from you.
Your autofilter technique would be used to change the quantity of data displayed where the question is about changing the values displayed. And the textbox link is to a cell that contins information that is not itself plotted within the chart.

Hopefully Doug can post back and clarify.

 

[SkipVought]

The AutoFilter is just a portion of the technique.

The nugget is the Combo Box manipulation of data on the sheet.

All you do is set up a simple lookup table, one column for each textbox and, VOLA, the combobox controlls EVERYTHING!

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[bobomutt]

Hi Skip,

Let me make it a one part question - I want to be able to display a CELL VALUE on a chart. I want a direct link to that cell, displayed not as part of the chart, but like a text box, pasted on top of the chart. Any way to do that? I don't think your explanation does that, although I do like the methodology, and will use it in the future where I can.

Thanks,

Doug

 

[bobomutt]

Andy - excellent tip, thanks. Exactly what I was trying to do.

Best,

Doug

 

[SkipVought]

1. Insert a text box from the FORMS toolbar

2. Editing the Text Box, select in the Formula Bar, enter = and select the cell containing the value you want to display. Hit [Enter]

This simple technique does NOTHING to "scroll" values in this box or anywhere else.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[bobomutt]

Right, but adding a scroll bar to the chart to modify that value displayed in the text box (referring to a cell in a spreadsheet within the same workbook)is very easy. Which, by the way, fully meets the question I posted.

Thanks again for the help. My question has been answered, you both were a great help.

Thanks,

Doug