DisplayFormulaBar Problem

[cll]

Hi all.

I have a problem with the Application.DisplayFormulaBar.
Having the following code:


[VBA]
Sub TILPAS_MENU()
Dim i As Integer
Dim AllBars As CommandBar
'
' TILPAS_MENU Makro
'
With ActiveWindow
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
End With
Application.DisplayFormulaBar = False
i = 0
Sheets("Opsamling").Range("C1:C50").Clear
On Error Resume Next
For Each AllBars In Application.CommandBars
If AllBars.Visible = True Then
i = i + 1
With Sheets("Opsamling")
.Cells(i, 3) = AllBars.Name
If AllBars.Name = "Worksheet Menu Bar" Then
AllBars.Enabled = False
Else
AllBars.Visible = False
End If
End With
End If
Next

On Error GoTo 0
End Sub
Sub NULSTIL_MENU()
Dim i As Integer
Dim BarName As String
'
' TILPAS_MENU Makro
'
With ActiveWindow
.DisplayHeadings = True
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With

On Error Resume Next
With Sheets("Opsamling")
For i = 1 To WorksheetFunction.CountA(.Columns(3))
BarName = .Cells(i, 3)
Application.CommandBars(BarName).Enabled = True
Application.CommandBars(BarName).Visible = True
Next i
i = 1
End With
On Error GoTo 0

Application.CommandBars("Worksheet menu bar").Enabled = True

Application.DisplayFormulaBar = True
End Sub
[/VBA]

which I respectively called from Open and BeforeClose events to hide and display Toolbars etc. Initial setting is stored in a sheet called "Opsamling".
All works fine except:

Application.DisplayFormulaBar = True

The formulabar won't show. If I run the code manually with F8 the formulabar will show. But I want to run it from a button in the sheet that closes the workbook.

I've tried a lot of things as setting Application.ScreenUpdating = True, pausing the macro for some seconds, putting Application.DisplayFormulaBar = True in a separate Sub or in the event, call it twice etc.

Any Ideas

Regards

Claus

[lucas]

Try commenting out the error handlers and see if you can get an idea of what is happening maybe.

[lucas]

Another thought although it is a stretch, since it runs every way except when you use the button......maybe you have something in the button code that may be causing you a problem.

[Simon Lloyd]

True Lucas, seems like the code in the command button is exiting before running the desired code, the "On Error Resume Next" in there is masking whatever problem there is in the code.

[cll]

To Lucas and Simon

Thank you very much for reply.
I have tried the suggestions, and have following comments:

The button in the sheet have the following code:

Private Sub CommandButton1_Click()
  ActiveWorkbook.Close (False)
End Sub

In the ThisWorkbook I have the following Events:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Module1.NULSTIL_MENU
End Sub
 
Private Sub Workbook_Open()
Module1.TILPAS_MENU
End Sub

I have tried commenting out the On Error Resume Next, but I get no errors nor will the FormulaBar display.

OK, now I have moved the Application.DisplayFormulaBar = True to the CommandButton code, that is:

Private Sub CommandButton1_Click()
Application.DisplayFormulaBar = True
 
ActiveWorkbook.Close (False)

 
End Sub

and the problem is solved! Why it won't work from within the Sub I can't figure out.

Thanks for giving me a lead!

Regards Claus

[cll]

...and I got another experience.

If I only put Application.DisplayFormulaBar = True in the CommandButton then the formulabar won't show, when closing the workbook/application with the closing X (upper right). That should be obviously.
If I only put Application.DisplayFormulaBar = True in the closing event then the formularbar won't show, when closing the workbook from the button. That I don't understand.

So, put Application.DisplayFormulaBar = True in both will do it!