How to visible FormulaBar when workbook is colosed?

[Colo]

Hi all,
I'm troubled with that . . .It looks so easy but I have no Idea.

The book has UserForm1 and CommandButton1 and the codes are as follows.
When I open this book, all CommandBars except for Worksheet Menu Bar will be disabled.
Also formulabar too.

I would like to enable all CommandBars and FormulaBar, when I click CommandButton1.
But FormulaBar are still disabled.

It works when I call this code except for CommandBars. (I mean when book is closed�@manually.)

Please Help me, guys.

<pre>
'-- This workbook module ---------------------------------------------
Option Explicit: Option Base 0

Private Sub Workbook_Open()
Call EnableCmbs(False)
UserForm1.Show
AppActivate Application.Caption
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call EnableCmbs(True)
ThisWorkbook.Saved = True
End Sub

'-- Standard module ---------------------------------------------------
Option Explicit: Option Base 0: Option Private Module

Public Sub EnableCmbs(Optional blnCBs As Boolean = True)
Dim cmb As CommandBar
For Each cmb In CommandBars
If Not cmb.Name = "Worksheet Menu Bar" Then cmb.Enabled = blnCBs
Next
Application.DisplayFormulaBar = blnCBs
End Sub

'--UserForm1 Module ---------------------------------------------------
Option Explicit: Option Base 0

Private Sub CommandButton1_Click()
ThisWorkbook.Close
End Sub
</pre>

 

[Ivan F Moala]

On 2002-09-18 18:10, Colo wrote:
Hi all,
I'm troubled with that . . .It looks so easy but I have no Idea.

The book has UserForm1 and CommandButton1 and the codes are as follows.
When I open this book, all CommandBars except for Worksheet Menu Bar will be disabled.
Also formulabar too.

I would like to enable all CommandBars and FormulaBar, when I click CommandButton1.
But FormulaBar are still disabled.

It works when I call this code except for CommandBars. (I mean when book is closed�@manually.)

Please Help me, guys.

<pre>
'-- This workbook module ---------------------------------------------
Option Explicit: Option Base 0

Private Sub Workbook_Open()
Call EnableCmbs(False)
UserForm1.Show
AppActivate Application.Caption
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call EnableCmbs(True)
ThisWorkbook.Saved = True
End Sub

'-- Standard module ---------------------------------------------------
Option Explicit: Option Base 0: Option Private Module

Public Sub EnableCmbs(Optional blnCBs As Boolean = True)
Dim cmb As CommandBar
For Each cmb In CommandBars
If Not cmb.Name = "Worksheet Menu Bar" Then cmb.Enabled = blnCBs
Next
Application.DisplayFormulaBar = blnCBs
End Sub

'--UserForm1 Module ---------------------------------------------------
Option Explicit: Option Base 0

Private Sub CommandButton1_Click()
ThisWorkbook.Close
End Sub
</pre>

Hi Please Help me, guys.



The Application Formula bar is actually 2 bars in one
ie it Displays as ONE but really belongs to 2 classes where as the Commandbars belong to the commandbar classes (ONE). What has this got to do with the displaying. Well the Formula Bar is also a Commandbar THE actual Input bar and NOT the Full Formula bar which also has the Name Box and [Class] Edit. For some reason when setting this through code and with the commandbars enable code etc it actually disables one so it doesn't show ??
The only way around this is to exercute this 1st from the UF code ?? and before your enable/disable code.



Try

<pre/>
'--UserForm1 Module ---------------------------------------------------
Option Explicit: Option Base 0

Private Sub CommandButton1_Click()
Application.DisplayFormulaBar = True
ThisWorkbook.Close
End Sub
</pre>

 

[Colo]

Thanks a lot Ivan. I did not know it belongs to 2 classes.
In fact, I knew how to show it as you wrote, but I had no idea why we must do so.
I posted same question on Jpn's board but there was no one who can answer the reason.
Now my question is absolutely solved. Again, thank you so much, mate!