ExcelBanter - custom commandbar questions

hi C,

answers & explanations inline!!!

example
Option Explicit
Const BAR = "TestBar"

Private Sub BarMake()
Dim n&, i&, sAct$

On Error Resume Next
CommandBars(BAR).Delete
On Error GoTo 0
sAct = ThisWorkbook.Name & "!modMain.BarExec"

With CommandBars.Add(BAR, msoBarTop, False, True)
For n = 1 To 3
With .Controls.Add(msoControlPopup, , "mnu" & n)
.Caption = "&Menu"
For i = 1 To 4
With .Controls.Add(msoControlButton, , "itm" & n & i)
.Caption = "&Item"
.OnAction = sAct
End With
Next
With .Controls.Add(msoControlPopup, , "mnu" & n & i)
.Caption = "&SubMenu"
For i = 1 To 4
With .Controls.Add(msoControlButton, , "itm" & n & i)
.Caption = "&Item"
.OnAction = sAct
End With
Next
End With
End With
Next
..Visible = True
..RowIndex = CommandBars("Standard").RowIndex

End With

ActiveWindow.WindowState = xlMaximized
ActiveWorkbook.Protect Windows:=True

#If VBA6 Then
If Val(Application.Version) = 10 Then
CallByName CommandBars, "DisableAskAQuestionDropdown", VbLet, True
End If
#End If

End Sub

Private Sub BarExec()
Select Case CommandBars.ActionControl.Parameter
Case "itm11"
MsgBox "Item1 on Menu 1 was pressed"
Case Else
MsgBox "you want more?"
End Select
End Sub

--

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

ckoch wrote :

I have several questions about the custom commandbar I am creating
from vb6. I'm using Excel XP BUT I need to be able to code for any
Excel version. So far I think that's working. Except for some
cosmetic issues ...

1) When I make the new commandbar visible, the built-in menu
(File,Edit,...) in Excel disappears.

apparently your bar is a menubar, not a toolbar with icons.
there can only be 1 visible menu bar.

Commandbars.add("MyBar",MenuBar:=False,Temporary:= True)

2) There is an icon on the left of the commandbar that has the Excel
logo. Clicking on it gives options to minimize/restore/close. How can
I hide that? It takes up too much real estate.

this is the icon from the maximized workbook window.

Workbook protection (windows only)
ActiveWorkbook.Protect Structu=False, Windows:=True

windowstate to normal, size to xldesktop. wb caption takes realestate.

or fiddle with api's to remvoe the icon from the workbook window.
I've done this. and it will not work in xl97)

3) There are minimize/restore/close buttons on the right side. How can
I hide those? (probably related to #3) I want the close button in the
header of the commandbar and at the moment it is missing?

see 2

4) Sometimes, there is a "Type a question for help" box on the right.
How can I make sure that stays away?

commandbars.DisableAskAQuestionDropdown
i'm fairly sure this is Office XP (10) + only!!!!

5) One of my controls is a popupmenu. Is it possible to display a
little arrow at the right of the button text to indicate that a menu
will appear?

it it contains submenu items it should be auto.

These are all things I have seen on other custom commandbars but I'm
not finding any way to control them.