How to visible FormulaBar when workbook is colosed?

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,659
Office Version
  1. 2016
  2. 2003 or older
Platform
  1. Windows
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. :biggrin:

<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>
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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. :biggrin:

<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. :biggrin:

:biggrin:

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

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,659
Office Version
  1. 2016
  2. 2003 or older
Platform
  1. Windows
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.:biggrin:
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! :)
 

Forum statistics

Threads
1,181,236
Messages
5,928,816
Members
436,629
Latest member
abc32

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top