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