MenuBar/CommandBar(s) reappearing after being disabled??

[Daniel Ross]

This is my first shot at participating in a discussion group, but I have
learned and implemented much from reading many of your comments and
suggestions. This one however I cannot solve myself.

I have a small problem with what appears to be the blue background of the
Worksheet Menu Bar reappearing after I have disabled all commandbars. It
reappears over the top row of all worksheets in the workbook, covering up
whatever is located there. In my case, I have a row of commandbuttons at the
top of the worksheet that are inaccessible whenever this happens. Sometimes
clicking on this blue strip produces a fatal error.

The problem may be easily reproduced by copying the following code into
Sheet1 (Event Code) of a blank Excel document. Then supply Worksheet (1)
with a commandbutton (“CommandButton1â€), a combobox (“ComboBox1â€), and a
checkbox (“CheckBox1â€). After clicking the [CommandButton1] to initialize
the worksheet, enable the checkbox and make a selection from the drop-down
combobox to observe the problem with the blue strip.

If anyone can help me work around the problem or show me how to prevent it I
would be much obliged. My testing has led me to believe that it happens as a
result of three events taking place in succession: (1) The Combobox still
has the focus when (2) another sheet is activated and then (3) ScreenUpdating
is enabled and disabled. However the problem does not appear until the first
worksheet is reactivated.

My system is Windows XP/Excel 2003 (Service Pack 2). Thanks in advance for
you comments—here’s the code:


Private Sub CommandButton1_Click()
If Cells(1, 4) = "" Then SetButtonData
ShowCommandBars
HideCommandBars
End Sub

Private Sub ComboBox1_Change()
If ComboBox1.Text = "Close & Restore Settings" Then
Cells(2, 4) = "Click the [Reset] button to initialize"
Cells(1, 4) = ""
ShowCommandBars
Application.Quit
End If
If CheckBox1 = True Then Application.ScreenUpdating = True
Cells(11, 4) = ComboBox1.Text + " is selected."
Cells(3, 4) = "What causes the Blue strip over the top of the Reset
button "
Cells(4, 4) = "when a choice is selected and ScreenUpdating is enabled?"
If CheckBox1 = True Then Application.ScreenUpdating = False

Worksheets(2).Activate
Worksheets(1).Activate
End Sub

Private Sub SetButtonData()
Dim Arr(4) As String
Arr(0) = "Select Choice"
Arr(1) = "Choice 2"
Arr(2) = "Choice 3"
Arr(3) = "Choice 4"
Arr(4) = "Close & Restore Settings"
With Worksheets(1)
With CommandButton1
.Caption = "Reset"
.Left = 0
.Top = 0
.Height = 60
.Width = 140
End With
With CheckBox1
.Caption = "Enable ScreenUpdating"
.Left = 10
.Top = 80
.Height = 20
.Width = 130
End With
With ComboBox1
.List() = Arr
.Text = "Select Choice"
.Left = 0
.Top = 120
.Height = 24
.Width = 140
End With
End With
Cells(7, 4) = "Make your first selection with this control unchecked."
Cells(8, 4) = "The blue strip happens only when ScreenUpdating is
enabled."
Worksheets(1).ComboBox1.Text = "Select Choice"
Cells(1, 4) = "Initialized"
Cells(2, 4).ClearContents
Cells(2, 4).Font.FontStyle = "Bold"
End Sub

Private Sub ShowCommandBars()
Application.DisplayFormulaBar = True
ActiveWindow.DisplayHeadings = True
For Each bar In Application.CommandBars
bar.Enabled = True
Next
End Sub

Private Sub HideCommandBars()
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
For Each bar In Application.CommandBars
bar.Enabled = False
Next
End Sub