How to visible FormulaBar when workbook is colosed?

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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,456
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,144,148
Messages
5,722,779
Members
422,457
Latest member
Mrmuskins

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