Solved

Formatting window/menu missing in Excel 2013

Posted on 2015-02-02
10
118 Views
Last Modified: 2016-02-10
The window / menu / toolbar (not sure exactly what to call it) that pops up on the right of screen in Excel 2013 when you want to change settings for a chart is missing. Currently I can't change the axis min / max values, change data series chart types or anything at all that requires that side menu/window/toolbar.

Likely related to this, I had a whole lot of strange things going on recently. For example right click menu disappeared altogether. I managed to get this back by reenabling via VBA. I'm wondering if some addin I've used has crashed leaving several features off/disabled. However, I couldn't find any examples of how to force enable the formatting window / menu / toolbar but that is possibly simply as I don't know what to properly call it!

My hunch is that the right vba code will allow me to reanable the formatting window.

Any ideas?
0
Comment
Question by:JDido
  • 4
  • 4
  • 2
10 Comments
 
LVL 23

Expert Comment

by:Michael74
Comment Utility
Try this to restore all toolbars

   Sub ShowAllToolbars()
       'loop variable
       Dim i As Integer

       ' Loop through the total number of toolbars.
       For i = 1 To Application.Toolbars.Count

           ' Show each toolbar.
           Application.Toolbars(i).Visible = True

       ' End of loop.
       Next i

   End Sub

Open in new window

http://support.microsoft.com/KB/109064
0
 

Author Comment

by:JDido
Comment Utility
No, unfortunately that didn't do it.

The right click menu came back after I messed with application.commandbars.
Is there a commandbars that refers to the formatting menu/window/toolbar?
0
 
LVL 23

Expert Comment

by:Michael74
Comment Utility
OK ensure that the menu is selected. Go to Tools > Customize, then click the Toolbar tab. Make sure the option for 'Chart' is selected.

Don't forget that the 'Chart' menu item only appears in chart mode (by starting a chart or activating any chart command).
0
 

Author Comment

by:JDido
Comment Utility
This is Excel 2013 so I don't think there is a Tools menu.

I took a screenshot of what I'm talking about on another PC.

The menu/toolbar is this one that normally appears on the right of screen whenever you right click and go format... for example FORMAT PICTURE... or FORMAT AXIS... or any number of other options. For some reason this will not appear on this particular PC.

Different PC, same workbook = no problem.
I have repaired and restarted several times to no avail.


Screenshot
0
 
LVL 7

Expert Comment

by:Katie Pierce
Comment Utility
Under File> Options>Customize the Ribbon, if you select the drop down for Customize the Ribbon on the right, are all the check boxes checked below it? Specifically, you should see Design and Format checked under the subheading Chart Tools.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:JDido
Comment Utility
Hi Katie,

Yes they are all checked.
I tried turning them off and closing Excel down then starting again and reenabling them: This didn't solve the problem either.

Is there a way this could have been disabled by VBA? (And can be reenabled?)

James
0
 
LVL 7

Expert Comment

by:Katie Pierce
Comment Utility
Unfortunately I have limited knowledge of VBA, so I'm not much help there.

Last ditch effort, though: When the menu is active on the right, you can hover over the edge and get a sizing arrow that will allow you to make the formatting menu narrower and narrower.  Is there any chance that when you try to click format that it does come up, but is very narrow?  Maybe if you hover the cursor there you can see the arrow and expand the menu?
0
 
LVL 23

Expert Comment

by:Michael74
Comment Utility
Sorry about that.

Have you tried opening the task pane using some of the other methods

eg

CTRL + 1
On a chart, select an element. On the Ribbon, select the Chart Tools Format tab, then click Format Selection.
On a chart, select an element. Right-click, then select Format <element> where <element> is the axis, series, legend, title, or area that was selected.
http://blogs.office.com/2013/03/27/format-and-customize-excel-2013-charts-quickly-with-the-new-formatting-task-pane/
0
 
LVL 23

Accepted Solution

by:
Michael74 earned 500 total points
Comment Utility
The following script will display all of the task panes which appear to the right

Sub test()
    Dim i As Long
    Dim cmd As CommandBar
    
    i = 2
    Range("A1").Value = "Name"
    Range("B1").Value = "Index"
    Range("C1").Value = "Enabled"
    
    For Each cmd In Application.CommandBars
        If cmd.Position = msoBarRight Then
            Range("A" & i).Value = cmd.Name
            Range("B" & i).Value = cmd.Index
            Range("C" & i).Value = cmd.Enabled
            i = i + 1
        End If
    Next
End Sub

Open in new window


On my my machine the following listing appears

Name	Index	Enabled
PivotTable Field List	10	FALSE
Task Pane	127	FALSE
XML Source	131	FALSE
Research	132	FALSE
XML Document	133	FALSE
Signatures	134	FALSE
Document Actions	135	FALSE
Clip Art	136	FALSE
Selection	137	FALSE
Format Object	138	TRUE
Document Management	139	FALSE
Document Updates	140	FALSE
Mail Merge Panes	141	FALSE
Fax Service	142	FALSE
Meeting Workspace	143	FALSE
Attachment Options	144	FALSE
Accessibility Checker	145	FALSE
Proofing	146	FALSE
Dictionaries	147	TRUE
Thesaurus	148	TRUE

Open in new window


If any of these are incorrectly disabled then use

Sub EnableCmdBar(idx As Long)
   Application.CommandBars(idx).Enabled = True
End Sub

Open in new window


where idx is the index of the offending commandbar
0
 

Author Closing Comment

by:JDido
Comment Utility
application.CommandBars(140).Enabled=True

This fixed it!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now