Excel 2003 Commandbars

[Walt Weber]

Hi,

Is there something different in Excel 2003 with regard to
creating a commandbar? The code below works fine with
Excel 2000 and 2002, but fails where indicated below with
Excel 2003 (It's been reported on 2 machines so far).

Best Regards,
Walt

NOTES:
1) I may have to advance the timing when I buy Excel 2003.
2) I don't specify the MenuBar argument since its default
is False and that's what I want.
3) Please forgive the text wrap.
**********************

Sub ResetBars()
For Each bar In Application.CommandBars
If Not bar.BuiltIn And bar.Name <> "MyBar" Then
bar.Delete
Next
End Sub

'******** A LA CARTE ENTRY SCREEN TOOL BAR ************
Sub FloatingAlaCarteToolBar()
If NoToolBar = True Then Exit Sub
Call ResetBars

'WORKS IN EXCEL 2000 & 2002, BUT FAILS HERE IN EXCEL 2003
Set TB_AlaCarte = CommandBars.Add(Name:=" ",
Position:=msoBarFloating, Temporary:=True) '<<<<<<<<

Set button1 = TB_AlaCarte.Controls.Add
(Type:=msoControlButton)
With button1
.FaceId = 1016
.Style = msoButtonIconAndCaption
.Tag = "Home"
.Caption = "&Go Home"
.TooltipText = "HOME (Alt-G)"
.OnAction = "GoHome"
End With
Set button2 = TB_AlaCarte.Controls.Add
(Type:=msoControlButton)
With button2
.FaceId = 442
.Style = msoButtonIconAndCaption
.Tag = "All"
.Caption = "View &All"
.TooltipText = "View all columns (Alt-A)"
.OnAction = "AC_ViewAllCols"
End With
Set button3 = TB_AlaCarte.Controls.Add
(Type:=msoControlButton)
With button3
.FaceId = 444
.Style = msoButtonIconAndCaption
.Tag = "Detail"
.Caption = "View &Cost"
.TooltipText = "View just the cost detail
columns. (Alt-C)"
.OnAction = "AC_ViewCostDetail"
End With
Set button4 = TB_AlaCarte.Controls.Add
(Type:=msoControlButton)
With button4
.FaceId = 445
.Style = msoButtonIconAndCaption
.Tag = "Summary"
.Caption = "View &Summary"
.TooltipText = "View just the summary columns.
(Alt-S)"
.OnAction = "AC_ViewSummary"
End With
Set button5 = TB_AlaCarte.Controls.Add
(Type:=msoControlButton)
With button5
.FaceId = 295
.Style = msoButtonIconAndCaption
.Tag = "Add&Rows"
.Caption = "Add &Row(s)"
.TooltipText = "Add the number of rows selected
at the selection point. (Alt-R)"
.OnAction = "AC_AddRows"
End With
Set button6 = TB_AlaCarte.Controls.Add
(Type:=msoControlButton)
With button6
.FaceId = 293
.Style = msoButtonIconAndCaption
.Tag = "DelRows"
.Caption = "De&lete Row(s)"
.TooltipText = "Delete the rows selected. (Alt-
L)"
.OnAction = "AC_DelRows"
End With
Set button7 = TB_AlaCarte.Controls.Add
(Type:=msoControlButton)
With button7
.FaceId = 1763
.Style = msoButtonIconAndCaption
.Tag = "AC_EditPKG"
.Caption = "Edit &Pkg"
.TooltipText = "Add, Delete or Change Packaging
Items. (Alt-P)"
.OnAction = "AC_EditPKG"
End With
Set button8 = TB_AlaCarte.Controls.Add
(Type:=msoControlButton)
With button8
.FaceId = 1020
.Style = msoButtonIconAndCaption
.Tag = "AC_SortRows"
.Caption = "Sort Ite&ms"
.TooltipText = "Sort the items. (Alt-M)"
.OnAction = "AC_SortRows"
End With
TB_AlaCarte.Top = 90
TB_AlaCarte.Left = 200
TB_AlaCarte.Height = TB_AlaCarte.Height * 1.5
TB_AlaCarte.Visible = True
Set button1 = Nothing
Set button2 = Nothing
Set button3 = Nothing
Set button4 = Nothing
Set button5 = Nothing
Set button6 = Nothing
Set button7 = Nothing
Set button8 = Nothing
Set TB_AlaCarte = Nothing
End Sub

 

[Jim Cone]

Walt,

I don't have Excel 2003, but I took a look at it anyway.
There is nothing obvious as to why there is a problem.
I did go thru and cleanup and simplify the code.
I added the name to the command bar.
I declared all variables.
Note that "Option Explicit" is used at the top of the module
It may suit your purposes or it may not.

Regards,
Jim Cone
San Francisco,CA

'-----------------------------------------------------------------
Option Explicit

Sub ResetBars()
'JUST DELETE THE BAR TO MAKE SURE NO DUPLICATE EXISTS.
On Error Resume Next
Application.CommandBars("MyBar").Delete
On Error GoTo 0
End Sub

Sub FloatingAlaCarteToolBar()
'If NoToolBar = True Then Exit Sub'???
Call ResetBars

'DECLARED VARIABLES - TEMPBUTTON IS USED FOR EACH CONTROLBUTTON
Dim TB_AlaCarte As CommandBar
Dim TempButton As CommandBarButton

'ADDED "MyBar" NAME
Set TB_AlaCarte = CommandBars.Add(Name:="MyBar", _
Position:=msoBarFloating, Temporary:=True)

Set TempButton = TB_AlaCarte.Controls.Add(Type:=msoControlButton)
With TempButton
.FaceId = 1016
.Style = msoButtonIconAndCaption
.Tag = "Home"
.Caption = "&Go Home"
.TooltipText = "HOME (Alt-G)"
.OnAction = "GoHome"
End With
Set TempButton = TB_AlaCarte.Controls.Add(Type:=msoControlButton)
With TempButton
.FaceId = 442
.Style = msoButtonIconAndCaption
.Tag = "All"
.Caption = "View &All"
.TooltipText = "View all columns (Alt-A)"
.OnAction = "AC_ViewAllCols"
End With
Set TempButton = TB_AlaCarte.Controls.Add(Type:=msoControlButton)
With TempButton
.FaceId = 444
.Style = msoButtonIconAndCaption
.Tag = "Detail"
.Caption = "View &Cost"
.TooltipText = "View just the cost detail columns. (Alt-C)"
.OnAction = "AC_ViewCostDetail"
End With
Set TempButton = TB_AlaCarte.Controls.Add(Type:=msoControlButton)
With TempButton
.FaceId = 445
.Style = msoButtonIconAndCaption
.Tag = "Summary"
.Caption = "View &Summary"
.TooltipText = "View just the summary columns. (Alt-S)"
.OnAction = "AC_ViewSummary"
End With
Set TempButton = TB_AlaCarte.Controls.Add(Type:=msoControlButton)
With TempButton
.FaceId = 295
.Style = msoButtonIconAndCaption
.Tag = "Add&Rows"
.Caption = "Add &Row(s)"
.TooltipText = _
"Add the number of rows selected at the selection point. (Alt-R)"
.OnAction = "AC_AddRows"
End With
Set TempButton = TB_AlaCarte.Controls.Add(Type:=msoControlButton)
With TempButton
.FaceId = 293
.Style = msoButtonIconAndCaption
.Tag = "DelRows"
.Caption = "De&lete Row(s)"
.TooltipText = "Delete the rows selected. (Alt-L)"
.OnAction = "AC_DelRows"
End With
Set TempButton = TB_AlaCarte.Controls.Add(Type:=msoControlButton)
With TempButton
.FaceId = 1763
.Style = msoButtonIconAndCaption
.Tag = "AC_EditPKG"
.Caption = "Edit &Pkg"
.TooltipText = "Add, Delete or Change Packaging Items. (Alt-P)"
.OnAction = "AC_EditPKG"
End With
Set TempButton = TB_AlaCarte.Controls.Add(Type:=msoControlButton)
With TempButton
.FaceId = 1020
.Style = msoButtonIconAndCaption
.Tag = "AC_SortRows"
.Caption = "Sort Ite&ms"
.TooltipText = "Sort the items. (Alt-M)"
.OnAction = "AC_SortRows"
End With
TB_AlaCarte.Top = 90
TB_AlaCarte.Left = 200
TB_AlaCarte.Height = TB_AlaCarte.Height * 1.5
TB_AlaCarte.Visible = True

Set TempButton = Nothing
Set TB_AlaCarte = Nothing
End Sub

Hi,
Is there something different in Excel 2003 with regard to
creating a commandbar? The code below works fine with
Excel 2000 and 2002, but fails where indicated below with
Excel 2003 (It's been reported on 2 machines so far).
Best Regards,
Walt

-snip-

 

[Walt Weber]

Hi Jim,

Thanks for the thoughts.

I did search through prior postings and noted that there
were some Excel 2003 changes re. commandbars having to do
with button faces, but nothing to indicate the problem my
users are seeing.

The problem is still unresolved.

Best Regards,
Walt

 

[Tom Ogilvy]

Try qualifying with application

Set TB_AlaCarte = CommandBars.Add(Name:=" ",
Position:=msoBarFloating, Temporary:=True)

would be
Set TB_AlaCarte = Application.CommandBars.Add(Name:=" ",
Position:=msoBarFloating, Temporary:=True)

 

[Walt Weber]

Hi Tom,

Thanks for the idea.

I've modified a copy and sent it to one of my users with
Excel 2003 to try. He may have gone for the day (This is
Oregon), but when I do hear back, I'll post his results.

If this turns out to be the solution, why would that be
so? Are there exceptions to the application qualifier
being assumed?

Best Regards,
Walt

 

[Tom Ogilvy]

Is there some reason you give it a space for a name. There is a possibility
that could be a problem. You might want to give it a more natural name.

If the code is in the ThisWorkbook directory, then application would not be
the default. However, that is the case in the other versions as well - but
since you are having problems it is something to try.

 

[Walt Weber]

Hi Tom,

The space is because I didn't want any name showing on the
bar and just a "" didn't seem to work with Excel
2000/2002. I'll try some text in there and let you know
if that's it.

The code is in a module. I didn't know the application
as default would not work in the ThisWorkbook code
section - thanks especially for that one. It could have
easily bitten me later.

Best Regards,
Walt

 

[Walt Weber]

Hi Tom,

That's IT! Mystery solved.

I've tried XXXX,X,=,_,*,/,\,#, and ~ each in place of the
space bar character and they all work. But for some
reason, the space bar character used as the commandbar
name argument fails in Excel 2003 though not in Excel 2000
& 2002.

Thank you Tom. If you're ever in the Portland, Oregon
area, I'd like to buy you a beer.

Best Regards,
Walt