?
Solved

add text box control to excel 2010 chart with onchange event

Posted on 2011-05-12
8
Medium Priority
?
683 Views
Last Modified: 2012-05-11
Hi Experts,

I would like to know how to add a text box control to an excel 2010 chart with the ability to have an onchange event for the text box.
(The onchange event will change the scale of the chart X-index for example)

Thanks

C
0
Comment
Question by:Craig Lambie
  • 4
  • 2
  • 2
8 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35744896
Chart sheet, or embedded chart?
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35745074
Seems like you can't add a text box to a Chart sheet. But on a Worksheet with a chart it can be done.
Add the textbox to your sheet and then use something like this in the sheet module:
Private Sub TextBox1_Change()
    Me.ChartObjects(1).Chart.Axes(xlCategory).TickLabelSpacing = Me.TextBox1.Value
End Sub

Open in new window


0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35745213
On a chart sheet you could use the MouseUp event to show an inputbox (or custom form even) so the user can enter a value.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 1

Author Comment

by:Craig Lambie
ID: 35745427
rorya: chart sheet

Nicobo: Has to be on the Chart Sheet.
What do you mean by Custom Form?  This might work potentially.  Can you embed this onto the chart?
I am not sure what you mean by this?
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35745463
You could put this in your chart module:
Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Me.Axes(xlCategory).TickLabelSpacing = InputBox("Value?")
End Sub

Open in new window

Now the user has to click the chart to bring up the InputBox where a value can be entered. Instead of the inputbox you could show a form with multiple options for the graph. But the user would still have to click the graph to bring up the screen.
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 500 total points
ID: 35745513
Alternative is to place a Text box on the graph and assign a macro to it. See the attached example. It has a Chart sheet with the click here text, and a worksheet with a text box control.
Graph.xlsm
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 500 total points
ID: 35745550
Depending on what range of adjustments you are making, you could also add a spinner or scrollbar to the chart, then assign it a macro like:
Sub Spinner3_Change()
   ActiveChart.Axes(xlValue, xlPrimary).MaximumScale = ActiveSheet.Spinners(Application.Caller).Value
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 35767057
Good suggestion Nicobo.

My basic understanding is "it is not possible" to have a text box with an onchange event on a chart sheet.

Thanks for the tips.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question