?
Solved

Renaming a control in Excel 2010 Chart??

Posted on 2011-04-18
23
Medium Priority
?
472 Views
Last Modified: 2012-08-13
Hi Experts,

I am having some issues with renaming a chart control in Excel 2010.
A couple of things are going on, I will best describe the situation and see how we go.

1. I have added a spinner control to the chart area, above the actual chart, its name "spinner51" appears in the Name box, where you would see the name of a cell or range.
If i try to change the control name in this box, it doesn't work?

2. When I try to find the name of a control already on the screen, I find it very difficult.  Neither the properties dialog box (accessed by Developer>Controls>Properties) or the right click properties dialog box window has the Control name in it, and it seems very hard or impossible to change just this control name?

Thoughts, experience others have had with changing control names in Excel 2010?
0
Comment
Question by:Craig Lambie
  • 13
  • 10
23 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35420969
May I see the file?

Sid
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 35421000
Hi Sid,
Sorry I can't share the file.
I can tell you it has/ is being converted from an Excel 2003 file to 2010.
C
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35421003
No Worries :)

Is it possible to see the screenshot of the control that you are referring to?

Sid
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:Craig Lambie
ID: 35421054
Thanks Sid -

 Excel Chart Screen Shot
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35421092
I guess you are using a 'Form' Control instead of an 'ActiveX' Control?

Sid
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 35421106
Yes - the ActiveX controls are greyed out, no idea why?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35421118
Hmmm.

Check this for me please. I am doing this from memory. Else I will ahve to reboot so that I can open Excel 2010

File > Options > Trust Center > Trust Center Settings button > ActiveX Settings and make sure 'Disable all controls without notification' has not been selected.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35421152
Ok, I rebooted and now have office 2010 open. My memory, didn't fail me :)

File > Options > Trust Center > Trust Center Settings button > ActiveX Settings > Select "Enable All Controls with restriction..."

You might have to exit Excel and Re-Open open it for settings to take effect.

Sid
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 35421153
I don't think that is the case - see SS
 Trust Centre
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 35421175
I have done as you suggested (Thanks for restarting)....
Still greyed out on Chart.

 Excel-SS-AllControlsEnabled
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35421179
>>>Still greyed out on Chart.

Yes on Chart, it will :)

Select a cell and then create a spinner and then drag it on the chart.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35421186
Quick question. Is the chart in a spreadsheet or is it a standalone chart as in a Chart Sheet?

Sid
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 35421210
Ok, so you are suggesting to me that the process for adding controls to a chart in Excel 2010 is this:

1.
Add control to a worksheet
2.
Put required code behind into the vba class for the worksheet
3.
Drag the Control to a Chart
??

I don't get it.  Once the control is on the chart the code behind doesn't work, and the control is not "accessible" via the usual channels, ie in VBA editor select control in top left drop down, then select event in top right drop down.

Chart is on Chart Sheet.

Thanks

C
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 35421219
The only way I could get the spinner to the Chart Sheet was via cut and paste, no dragging was possible.
The Spinner control is now just a simple drawing object I think, I can assign a macro, but knowing if the event is up/ down is not possible apparently?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35421220
>>>Chart is on Chart Sheet.

In that case, You cannot use an activeX Control in the Chart Sheet and you cannot rename Form controls (AFAIK and I could be wrong).

Had the Chart been in a spreadsheet then you could have created an activeX control and then placed it over the Excel Chart. For example See this.

Sid
Book2.xlsm
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 35421287
Ok - do my ultimate goal here is to place a spinner control on a chart sheet to then change the content of the chart.
Not being able to name the form control is just the first part of not being able to access the control on the chart sheet.  How does one access the code behind of a control on a chart sheet in order to program it to do something?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35421291
Quick question. Why do you want to rename the control? Probably there might be an alternative?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35421297
Seems like we posted at the same time :)

Let me go through your post.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35421392
>>>How does one access the code behind of a control on a chart sheet in order to program it to do something?

You write a macro and paste it in a module and then assign the macro to the spinner by right clicking on it and assigning the macro.

But I believe you want to increase or decrease the value in the chart. If yes, then do this.

Right Click on the Spinner and click on Format control
Go To Control Tab and in "Cell Link" mention the cell it should relate to. See snapshot.

Now when you increase or decrease a value, you will see the change in the chart

Sid
Untitled.jpg
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35421464
I am stepping out for a moment. Will be back shortly.

Sid
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 35421509
Thanks Sid,
So I am gathering from this that I can't use a Spinner control on a chart the same way I would use it (in code) on a worksheet - ie. assign a different macro to the up event and the down event.

I think based on this I am better off creating two buttons to do the same thing (ie. Up / Down buttons) that I can then assign to each result.
I will be simply changing the index of the DropDownList on the chart when the up/down is pressed.
I think I will do this - thoughts?
Thanks so much for your time and effort on this btw.
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35421511
>>>So I am gathering from this that I can't use a Spinner control on a chart the same way I would use it (in code) on a worksheet - ie. assign a different macro to the up event and the down event.

Yes you are right.

>>>I will be simply changing the index of the DropDownList on the chart when the up/down is pressed.
I think I will do this - thoughts?

Yes for that you can you two buttons :)

Sid
0
 
LVL 1

Author Closing Comment

by:Craig Lambie
ID: 35421533
Pity MS haven't allowed full controls access on Charts in 2010 - I would of thought it was here by now :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
I came across an unsolved Outlook issue and here is my solution.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

807 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