Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Excel 2003 Commandbars
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 24
Default Excel 2003 Commandbars
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,290
Default Excel 2003 Commandbars
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

"Walt Weber" wrote in message ...
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-
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 24
Default Excel 2003 Commandbars
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

-----Original Message-----
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

"Walt Weber" wrote in message
...
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-
.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Excel 2003 Commandbars
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)

--
Regards,
Tom Ogilvy

"Walt Weber" wrote in message
...
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

-----Original Message-----
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

"Walt Weber" wrote in message
...
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-
.



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 24
Default Excel 2003 Commandbars
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

-----Original Message-----
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)

--
Regards,
Tom Ogilvy

"Walt Weber" wrote in message
...
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

-----Original Message-----
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

"Walt Weber" wrote in message
...
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-
.



.

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Excel 2003 Commandbars
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.

--
Regards,
Tom Ogilvy

"Walt Weber" wrote in message
...
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

-----Original Message-----
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)

--
Regards,
Tom Ogilvy

"Walt Weber" wrote in message
...
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

-----Original Message-----
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

"Walt Weber" wrote in message
...
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-
.



.



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 24
Default Excel 2003 Commandbars
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

-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"Walt Weber" wrote in message
...
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

-----Original Message-----
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)

--
Regards,
Tom Ogilvy

"Walt Weber" wrote in message
...
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

-----Original Message-----
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

"Walt Weber" wrote in message
...
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-
.



.



.

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 24
Default Excel 2003 Commandbars - SOLVED
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

-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"Walt Weber" wrote in message
...
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

-----Original Message-----
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)

--
Regards,
Tom Ogilvy

"Walt Weber" wrote in message
...
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

-----Original Message-----
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

"Walt Weber" wrote in message
...
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-
.



.



.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel CommandBars Daniel Utsch[_2_] Excel Discussion (Misc queries) 3 December 14th 08 07:17 PM
Commandbars Libby Excel Programming 1 August 8th 04 07:11 PM
CommandBars garry Excel Programming 3 June 24th 04 03:12 PM
Resetting Excel commandbars on close event alanperu Excel Programming 4 January 6th 04 02:34 PM
CommandBars defj Excel Programming 2 November 16th 03 12:57 AM


All times are GMT +1. The time now is 12:16 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement