Hi - I am setting up a report system in xl my client has specified that as
far as possible that it doesn't want his clients to realise it's xl.

Thanks to various sources I have cobbled together a series of procedures
which I store in the "thisWorkbook" area (see below if interested).

My problem is that on sheet2 of the worksheet where I have 2 combo box
controls/drp downs - when either of the drop downs is activated and then the
user does something else such as moves to another sheet, a grey bar appears
at the top of the screen. When I right click on the grey bar I can select
toolbars to activate. All the drop downs do is display a list of dummy
values (which I got to work in properties from a worksheet rather than
programmatically).

Even if I rerun the code below the grey bar is still visible. The only way
to get rid of it is to run the opposite code from below i.e. that which re-
excels the xls and then rerun the code below to de-exel.

Is it a bug?

This happens in xl2000/2002 running on xp/win98.

Any help appreciated.

Regards

Andrew
www.jmdata.co.uk


--------------------------------- START CODE (held in
thisWorkbook) -----------------------------------------------------
Public Sub workbook_open()
Application.ScreenUpdating = False

' Now all run from a workbook procedure which should ensure
' any excel based changes are local to THIS workbook

Call workbook_non_select

Sheets("Main Menu").Select

Application.ScreenUpdating = True
End Sub


' Procedure shuttles thro each report worksheet
' calls proc which protects sheets such that no
' cell/item apart from buttons can be selected
' Means user can't select cell, particularly when
' clicking an area around a button by mistake
'
' Called from auto_open
Public Sub workbook_non_select()
Dim i As Integer

Sheets("Main Menu").Select

Application.Caption = "XXXXXXXXXXXXXXXXX "
Application.Cursor = xlNorthwestArrow

Call HideMenus

' 09/01/04 - change this as extra sheets are added
For i = 4 To 14
Worksheets(i).Select
Call set_non_select
Call rid_all_menus2
Range("A1").Select
Next i

Sheets("Main Menu").Select

End Sub

' Part of de xl process
Public Sub set_non_select()
With ActiveSheet
.Unprotect
.EnableSelection = xlNoSelection
.Protect UserInterfaceOnly:=True
End With
End Sub

Sub rid_all_menus2()

If ActiveWindow.DisplayHorizontalScrollBar = True Then
ActiveWindow.DisplayHorizontalScrollBar = False
End If

If ActiveWindow.DisplayVerticalScrollBar = True Then
ActiveWindow.DisplayVerticalScrollBar = False
End If

If ActiveWindow.DisplayGridlines = True Then
ActiveWindow.DisplayGridlines = False
End If

If ActiveWindow.DisplayHeadings = True Then
ActiveWindow.DisplayHeadings = False
End If

If ActiveWindow.DisplayWorkbookTabs = True Then
ActiveWindow.DisplayWorkbookTabs = False
End If

If Application.CommandBars("Standard").Visible = True Then
Application.CommandBars("Standard").Visible = False
End If

If Application.CommandBars("Formatting").Visible = True Then
Application.CommandBars("Formatting").Visible = False
End If

If Application.CommandBars("Drawing").Visible = True Then
Application.CommandBars("Drawing").Visible = False
End If

If Application.DisplayFormulaBar = True Then
Application.DisplayFormulaBar = False
End If

If Application.DisplayStatusBar = True Then
Application.DisplayStatusBar = False
End If

End Sub

' 09/01/04
Public Sub HideMenus()
i = 0
Sheet3.Range("C1:C50").Clear
On Error Resume Next
For Each Allbars In Application.CommandBars
If Allbars.Visible = True Then
i = i + 1
With Sheet3
.Cells(i, 3) = Allbars.Name
If Allbars.Name = "Worksheet Menu Bar" Then
Allbars.Enabled = False
Else
Allbars.Visible = False
End If
End With
End If
Next
Application.DisplayFormulaBar = False
'With Application.CommandBars("MyToolBar")
' .Visible = True
' .Position = msoBarTop
' .Left = 0
' .Protection = msoBarNoMove
'End With
On Error GoTo 0
End Sub