Hi All,

I am having difficulty specifying the docking order of 3 custom
toolbars (with names equal to that stored in the variables W, F and A)
that are loaded by an add-in application. This code resides in the
ThisWorkbook Module of my xla, and is part of a procedure called by the
Workbook_Open procedure. Specifically, I have had a few variations on
the outcome as I have tried revising the code to get it to work: 1)
Initially, code similar to below but using "If" statements instead of
"Select Case" failed to make two of the three toolbars visible (though
they were enabled, they just were not checked, despite code that should
have made them "visible"); 2) Now, with the code below, NO toolbars are
displayed when my load routine ends (I should also mention that an
earlier section of the code disables the default "Worksheet Menu Bar",
"Standard" and "Formatting" toolbars). Further, when I put a Breakpoint
on the "With Cmd" statement following Case "A", and a Watch with the
Expression "cmd.Name = A", and then step through the code, I can see it
get to the Case "A" statement (and the Watch expression then evaluates
as "True"), but when I press F8, it goes immediately to End Select
without ever executing the code for Case "A". Can anyone advise why
this would happen??

Thanks!

Jeff

Partial code follows:

'At the top of the ThisWorkbook Module:

Public Cmd As Object 'CommandBar name
Public cmdbar As Object
Public W As String
Public A As String
Public F As String

In Workbook_Open:

Set cmdbar = Application.CommandBars


'Partial code in the procedure called from Workbook_Open:

For Each cmd In cmdbar
Select Case cmd.Name

Case "W"
With cmd
.Enabled = True
.Visible = True
.Position = msoBarTop
.Left = 0
.Protection = msoBarNoMove
End With

Case "F"
With cmd
.Enabled = True
.Visible = True
.RowIndex = 2
.Left = 0
.Protection = msoBarNoMove
End With

Case "A"
With cmd
.Enabled = True
.Visible = True
' .RowIndex = msoBarRowLast
.Position = msoBarBottom
.Left = 0
.Protection = msoBarNoMove
End With

End Select

Next

Have you tried putting the "With" statement outside the "Select" statement?

Just a thought.

With cmd
Select Case .Name
Case "W"
.Enabled = True
.Visible = True
.Position = msoBarTop
.Left = 0
.Protection = msoBarNoMove
Case "F"
.Enabled = True
.Visible = True
.RowIndex = 2
.Left = 0
.Protection = msoBarNoMove
Case "A"
.Enabled = True
.Visible = True
' .RowIndex = msoBarRowLast
.Position = msoBarBottom
.Left = 0
.Protection = msoBarNoMove
End Select
End With


"Jeff" wrote:

Hi All,

I am having difficulty specifying the docking order of 3 custom
toolbars (with names equal to that stored in the variables W, F and A)
that are loaded by an add-in application. This code resides in the
ThisWorkbook Module of my xla, and is part of a procedure called by the
Workbook_Open procedure. Specifically, I have had a few variations on
the outcome as I have tried revising the code to get it to work: 1)
Initially, code similar to below but using "If" statements instead of
"Select Case" failed to make two of the three toolbars visible (though
they were enabled, they just were not checked, despite code that should
have made them "visible"); 2) Now, with the code below, NO toolbars are
displayed when my load routine ends (I should also mention that an
earlier section of the code disables the default "Worksheet Menu Bar",
"Standard" and "Formatting" toolbars). Further, when I put a Breakpoint
on the "With Cmd" statement following Case "A", and a Watch with the
Expression "cmd.Name = A", and then step through the code, I can see it
get to the Case "A" statement (and the Watch expression then evaluates
as "True"), but when I press F8, it goes immediately to End Select
without ever executing the code for Case "A". Can anyone advise why
this would happen??

Thanks!

Jeff

Partial code follows:

'At the top of the ThisWorkbook Module:

Public Cmd As Object 'CommandBar name
Public cmdbar As Object
Public W As String
Public A As String
Public F As String

In Workbook_Open:

Set cmdbar = Application.CommandBars


'Partial code in the procedure called from Workbook_Open:

For Each cmd In cmdbar
Select Case cmd.Name

Case "W"
With cmd
.Enabled = True
.Visible = True
.Position = msoBarTop
.Left = 0
.Protection = msoBarNoMove
End With

Case "F"
With cmd
.Enabled = True
.Visible = True
.RowIndex = 2
.Left = 0
.Protection = msoBarNoMove
End With

Case "A"
With cmd
.Enabled = True
.Visible = True
' .RowIndex = msoBarRowLast
.Position = msoBarBottom
.Left = 0
.Protection = msoBarNoMove
End With

End Select

Next

Hi Charlie & thanks for your suggestion, but I am getting the same
result as before (NO toolbars are displayed when the code finishes ...
and putting a Breakpoint on the .Enabled = True following Case "A"
never gets triggered - the code gets to the Case "A" statement, and a
Watch I set up to test that cmd.Name = A shows as "True", but F8 at
that point goes directly to the End Select statement without ever
executing the Case "A" code ... as per your suggestion, present code
follows:

Thanks for any ideas!

Jeff


For Each Cmd In cmdbar

With Cmd

Select Case .Name

Case "W"
.Enabled = True
.Visible = True
.Position = msoBarTop
.Left = 0
.Protection = msoBarNoMove

Case "F"
.Enabled = True
.Visible = True
.RowIndex = 2
.Left = 0
.Protection = msoBarNoMove

Case "A"
.Enabled = True
.Visible = True
' .RowIndex = msoBarRowLast
.Position = msoBarBottom
.Left = 0
.Protection = msoBarNoMove

End Select

End With

Next Cmd

"Jeff" wrote in message
oups.com...
Hi All,

I am having difficulty specifying the docking order of 3 custom
toolbars (with names equal to that stored in the variables W, F and A)
that are loaded by an add-in application. This code resides in the
ThisWorkbook Module of my xla, and is part of a procedure called by the
Workbook_Open procedure. Specifically, I have had a few variations on
the outcome as I have tried revising the code to get it to work: 1)
Initially, code similar to below but using "If" statements instead of
"Select Case" failed to make two of the three toolbars visible (though
they were enabled, they just were not checked, despite code that should
have made them "visible"); 2) Now, with the code below, NO toolbars are
displayed when my load routine ends (I should also mention that an
earlier section of the code disables the default "Worksheet Menu Bar",
"Standard" and "Formatting" toolbars). Further, when I put a Breakpoint
on the "With Cmd" statement following Case "A", and a Watch with the
Expression "cmd.Name = A", and then step through the code, I can see it
get to the Case "A" statement (and the Watch expression then evaluates
as "True"), but when I press F8, it goes immediately to End Select
without ever executing the code for Case "A". Can anyone advise why
this would happen??

Thanks!

Jeff

Partial code follows:

'At the top of the ThisWorkbook Module:

Public Cmd As Object 'CommandBar name
Public cmdbar As Object
Public W As String
Public A As String
Public F As String

In Workbook_Open:

Set cmdbar = Application.CommandBars


'Partial code in the procedure called from Workbook_Open:

For Each cmd In cmdbar
Select Case cmd.Name

Case "W"
With cmd
.Enabled = True
.Visible = True
.Position = msoBarTop
.Left = 0
.Protection = msoBarNoMove
End With

Case "F"
With cmd
.Enabled = True
.Visible = True
.RowIndex = 2
.Left = 0
.Protection = msoBarNoMove
End With

Case "A"
With cmd
.Enabled = True
.Visible = True
' .RowIndex = msoBarRowLast
.Position = msoBarBottom
.Left = 0
.Protection = msoBarNoMove
End With

End Select

Next


Weird. What happens if you enter something like:

If (0 = StrComp(cmd.Name, "A",cmd.Name,vbTextCompare)) Then
MsgBox "Yes"
End If

/Fredrik

Thanks for logging in on this Frederick. I had a suggestion elsewhere
to remove the quotes on the Case "W" etc. statements that seemed to
work, in that all 3 toolbars did load, but they all loaded on the same
Row (not good). I finally got it all working (and it took an extra set
of variable names for the toolbars to be ordered which got it working
in Excel 2000, but strangely, it took an extra pass on the ordering
routine to get the same code to work in Excel 2003. Anyway, I am not
sure I understand why the earlier approach didn't work because I have
seen (& used) other examples of Select Case where quotes were used ...
Nevertheless, I have solved my immediate problem - thanks for
everyone's help. The now working code follows:

Jeff

For Each Cmd In cmdbar
Select Case Cmd.Name

Case W
With Cmd
.Enabled = True
.Visible = True
.Position = msoBarTop
.Left = 0
FirstBar_Cmd = Cmd.Name

End With

Case F
With Cmd
.Enabled = True
.Visible = True
.RowIndex = 2
.Left = 0
SecondBar_Cmd = Cmd.Name

End With

Case A
With Cmd
.Enabled = True
.Visible = True
.RowIndex = msoBarRowLast
.Left = 0
ThirdBar_Cmd = Cmd.Name

End With

End Select

Next Cmd
Set Cmd = Nothing

With cmdbar(FirstBar_Cmd)
.RowIndex = msoBarRowFirst
.Left = 0
.Protection = msoBarNoMove
End With

With cmdbar(SecondBar_Cmd)
.RowIndex = 2
.Left = 0
.Protection = msoBarNoMove
End With

With cmdbar(ThirdBar_Cmd)
.RowIndex = msoBarRowLast
.Left = 0
.Protection = msoBarNoMove
End With

' The above worked in Excel 2000, but not in Excel 2003
' Order it again! (Takes another pass to make this work in Excel 2003)

With cmdbar(FirstBar_Cmd)
.RowIndex = msoBarRowFirst
.Left = 0
.Protection = msoBarNoMove
End With

With cmdbar(SecondBar_Cmd)
.RowIndex = 2
.Left = 0
.Protection = msoBarNoMove
End With

With cmdbar(ThirdBar_Cmd)
.RowIndex = msoBarRowLast
.Left = 0
.Protection = msoBarNoMove
End With

"Jeff" wrote in message
oups.com...
Thanks for logging in on this Frederick. I had a suggestion elsewhere
to remove the quotes on the Case "W" etc. statements that seemed to
work, in that all 3 toolbars did load, but they all loaded on the same
Row (not good). I finally got it all working (and it took an extra set
of variable names for the toolbars to be ordered which got it working
in Excel 2000, but strangely, it took an extra pass on the ordering
routine to get the same code to work in Excel 2003. Anyway, I am not
sure I understand why the earlier approach didn't work because I have
seen (& used) other examples of Select Case where quotes were used ...
Nevertheless, I have solved my immediate problem - thanks for
everyone's help. The now working code follows:

Jeff

For Each Cmd In cmdbar
Select Case Cmd.Name

Case W
With Cmd
.Enabled = True
.Visible = True
.Position = msoBarTop
.Left = 0
FirstBar_Cmd = Cmd.Name

End With

Case F
With Cmd
.Enabled = True
.Visible = True
.RowIndex = 2
.Left = 0
SecondBar_Cmd = Cmd.Name

End With

Case A
With Cmd
.Enabled = True
.Visible = True
.RowIndex = msoBarRowLast
.Left = 0
ThirdBar_Cmd = Cmd.Name

End With

End Select

Next Cmd
Set Cmd = Nothing

With cmdbar(FirstBar_Cmd)
.RowIndex = msoBarRowFirst
.Left = 0
.Protection = msoBarNoMove
End With

With cmdbar(SecondBar_Cmd)
.RowIndex = 2
.Left = 0
.Protection = msoBarNoMove
End With

With cmdbar(ThirdBar_Cmd)
.RowIndex = msoBarRowLast
.Left = 0
.Protection = msoBarNoMove
End With

' The above worked in Excel 2000, but not in Excel 2003
' Order it again! (Takes another pass to make this work in Excel 2003)

With cmdbar(FirstBar_Cmd)
.RowIndex = msoBarRowFirst
.Left = 0
.Protection = msoBarNoMove
End With

With cmdbar(SecondBar_Cmd)
.RowIndex = 2
.Left = 0
.Protection = msoBarNoMove
End With

With cmdbar(ThirdBar_Cmd)
.RowIndex = msoBarRowLast
.Left = 0
.Protection = msoBarNoMove
End With


The code looks really weird to me without the quotes. I understand what "W"
means but not W. I would like the person who made this recommendation
explain it.

/Fredrik

I have a question in to him on this very subject & will respond back
here if I get an answer. All I can say is it worked without the quotes
and did not with the quotes ...

Jeff

"Jeff" wrote in message
ups.com...
I have a question in to him on this very subject & will respond back
here if I get an answer. All I can say is it worked without the quotes
and did not with the quotes ...

Jeff


Is it possible that the workbook iscorrupt? I have noticed that if you use
lots of customization and also if you have large pivot tables, the workbook
may get corrupt. Is it possible for you to create a new workbook and insert
the original code without too much effort? My experience is that corrupt
workbooks behave strange and unpredictably.

/Fredrik