Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MenuBar/CommandBar(s) reappearing after being disabled??
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€”heres 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 -- All the Best! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hidden rows reappearing when using filter | Excel Worksheet Functions | |||
How to keep web tool bar turned off in Excel-It keeps reappearing | Excel Discussion (Misc queries) | |||
reappearing headers | Excel Worksheet Functions | |||
How can I prevent the reviewing toolbar from reappearing? | Setting up and Configuration of Excel | |||
Office 97 override commandbar /menubar events | Excel Programming |