Chart Menu Bar Won't Change To Data Menu Bar

AndrewJ · Apr 25th 2007

Hey guys, this one has me stumped.. my worksheet menu bar is displaying the 'Chart' menu no matter what I do. Not only do I have no charts in the workbook, (verified this by: )

Code

For Each ws In Worksheets


ws.Select
MsgBox ActiveSheet.ChartObjects.Count


Next ws

but adding worksheets, selecting various parts of a worksheet, creating a chart and deleting it, and everything else I've tried has no effect.

One interesting thing - the first chart I added (to test if it would 'unstick' itself upon deletion of the new chart) was named 'Chart 2', implying there was a chart1 that existed previously.... although I am fairly certain I never added a chart to this workbook at any point.

Also, the menu bar is working fine in other workbooks, and changes to chart and back to data like normal.

Anyone have any ideas?

**Reafidy** · Apr 25th 2007

Re: Chart Menu Bar Wont Change To Data Menu Bar

can you post the workbook?

AndrewJ · Apr 25th 2007

Re: Chart Menu Bar Wont Change To Data Menu Bar

Unfortunetly, its ~ 5 megs and although I could split it up and upload parts of it, I have no clue where the hidden chart or problem is

AndrewJ · Apr 25th 2007

Re: Chart Menu Bar Wont Change To Data Menu Bar

Ok, if I start the file without macros enabled it runs correctly, if I start the file WITH macros it starts correctly but then something in activeworkbook.open is causing the chart menu to appear, specifically something here:

Code

Sub define_toolbars()


On Error GoTo errorout
      
    Dim x As Integer
    
    For x = 1 To 117
    
    If Application.CommandBars(x).Visible = True Then
      arr(x) = 1
    End If
    
    Next x
         
    Exit Sub


errorout:


Application.Run "errorouttb"
        
End Sub


Sub turn_off()


On Error GoTo errorout


Application.Run "define_toolbars"


Application.CommandBars(1).Enabled = False
Application.DisplayStatusBar = False
Application.DisplayFormulaBar = False
ActiveWindow.DisplayWorkbookTabs = False


' disables red x
 Dim HWnd As Long
    HWnd = FindWindowA(vbNullString, Application.Caption)
    SetWindowLongA HWnd, -16, GetWindowLongA(HWnd, -16) And &HFFF7FFFF


Dim x As Integer


For x = 2 To 117


If arr(x) = 1 Then


Application.CommandBars(x).Enabled = True
Application.CommandBars(x).Visible = False


End If


Next x


    Exit Sub


errorout:


Application.Run "errorouttb"


End Sub

Display More

I run the sub 'turn off' above, which runs both of the above subs, and then when I run turn_on, everything reappears, but with the chart menu in place of the data menu. It still does it with the 9 controls commented out (like below)

Code

Sub turn_on()


On Error GoTo errorout


Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Visible = True


'CommandBars("Worksheet Menu Bar").Controls("File").Enabled = True
'CommandBars("Worksheet Menu Bar").Controls("Edit").Enabled = True
'CommandBars("Worksheet Menu Bar").Controls("View").Enabled = True
'CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = True
'CommandBars("Worksheet Menu Bar").Controls("Format").Enabled = True
'CommandBars("Worksheet Menu Bar").Controls("Tools").Enabled = True
'CommandBars("Worksheet Menu Bar").Controls("Data").Enabled = True
'CommandBars("Worksheet Menu Bar").Controls("Window").Enabled = True
'CommandBars("Worksheet Menu Bar").Controls("Help").Enabled = True


Application.DisplayStatusBar = True
Application.DisplayFormulaBar = True


ActiveWindow.DisplayWorkbookTabs = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayVerticalScrollBar = True
ActiveWindow.DisplayHeadings = True


Application.CommandBars(2).Enabled = True
Application.CommandBars(2).Visible = True


Application.CommandBars(3).Enabled = True
Application.CommandBars(3).Visible = True


' turn on red x
Dim HWnd As Long
    HWnd = FindWindowA(vbNullString, Application.Caption)
    SetWindowLongA HWnd, -16, GetWindowLongA(HWnd, -16) Or &H80000


Dim x As Integer


For x = 2 To 117


If arr(x) = 1 Then


Application.CommandBars(x).Enabled = True
Application.CommandBars(x).Visible = True


End If


Next x


    Exit Sub


errorout:


Application.Run "errorouttb"


End Sub

Display More

AndrewJ · Apr 27th 2007

Re: Chart Menu Bar Wont Change To Data Menu Bar

bump... any ideas?

If I use the 3 macros above in a new workbook, they work correctly and return the data menu as normal... im really stuck on this one...

AndrewJ · Apr 28th 2007

Re: Chart Menu Bar Wont Change To Data Menu Bar

Found it.

For anyone with the same problem - apparently the worksheet menu bar w/ chart menu bar has its own ID separate from the worksheet menu bar w/ data menu? My array that I used to save all open commandbars was apparently changing 'data' to 'chart' at the end of the macro. Application.CommandBars("Worksheet Menu Bar").Enabled = True fixed it.

AndrewJ · May 25th 2007

Re: Chart Menu Bar Wont Change To Data Menu Bar

follow up - The array I was using, I was starting with '2' as the first menu bar to enable, which is actually the worksheet menu bar WITH the chart replacing the data menu. Use the array from 3 to 117 in all subs to avoid turning on chart when you dont want it.

btw, sorry to bump this but my first 'solved' post was pretty unclear about the problem, I ran into it again in another instance and looked this thread up

Chart Menu Bar Won't Change To Data Menu Bar

Participate now!

Share

Users Viewing This Thread