PDA

View Full Version : "Format" menu is not visible.



arrun
12-02-2007, 10:24 AM
In my Excel file, "Format" menu is not displayed. I have all the menu from "File" to "Window", but not the "Format" menu. Can anyone tell me how to display that?

Bob Phillips
12-02-2007, 10:46 AM
Just enable it

It the VBIDE immediate window type

Application.Commandbars(1).Controls("Format").Enabled=True

or



Application.Commandbars(1).Controls("Format").Visible=True

arrun
12-02-2007, 10:52 AM
Thanks for the mail. However still it is not clear to me. Where you want me to type? Can you be more specific? what is VBIDE?

Regards,

Bob Phillips
12-02-2007, 11:37 AM
The VBA window.

arrun
12-02-2007, 11:59 AM
Still not :( In the code window for "ThisWorkbook" I have written following code:
Private Sub Workbook_Open()
Sheets("Introduction").Activate
Application.EnableCancelKey = xlDisabled
UserForm1.Show
Application.CommandBars(1).Controls("Format").Enabled = True
End Sub


However I am getting following error :

Run time error '5':
Invalid procedure call or argument

mikerickson
12-02-2007, 12:11 PM
Are you using the English version of Excel. I think that the Controls("Format") may be language dependant.

Bob Phillips
12-02-2007, 12:23 PM
Still not :( In the code window for "ThisWorkbook" I have written following code:
Private Sub Workbook_Open()
Sheets("Introduction").Activate
Application.EnableCancelKey = xlDisabled
UserForm1.Show
Application.CommandBars(1).Controls("Format").Enabled = True
End Sub


However I am getting following error :

Run time error '5':
Invalid procedure call or argument

Which line?

arrun
12-02-2007, 12:27 PM
It seems that the error coming from your syntax only. I rewrite the whole thing as :

Private Sub Workbook_Open()
'Sheets("Introduction").Activate
'Application.EnableCancelKey = xlDisabled
'UserForm1.Show
'Application.CommandBars("Format").Enabled = True

Application.CommandBars(1).Controls("Format").Enabled = True
End Sub

And the same error message coming.

arrun
12-02-2007, 12:30 PM
Ya I am using the English version only. Any suggestion?

mikerickson
12-02-2007, 12:44 PM
no I don't

mikerickson
12-02-2007, 01:21 PM
Yes I do have an idea. Perhaps the caption got changed. Try running this.


Sub Macro1()
Dim xControl As Object
Dim captionStr, promptStr As String

captionStr = "&File &Edit &View &Insert &Tools &Data &Window &Help "

For Each xControl In Application.CommandBars(1).Controls

If InStr(captionStr, Mid(xControl.Caption, 2)) = 0 Then

promptStr = "A control has a caption :" & xControl.Caption & vbCrLf & vbTab & vbTab
promptStr = promptStr & "that is not ususaly on the Menu Bar." & vbCrLf
promptStr = promptStr & "Do you want to reset it?"

If MsgBox(promptStr, vbYesNo) = vbYes Then
xControl.Reset
End If

End If

Next xControl

Application.CommandBars(1).Controls("Format").Enabled = True
Application.CommandBars(1).Controls("Format").Visible = True

End Sub

The only other thing I can think of is a loop resetting every control of every commandBar and destroying all the customization. That should reset the Format control.

Bob Phillips
12-02-2007, 02:29 PM
Maybe try



Application.CommandBars(1).FindControl(ID:=30006).Enabled = True