Select Case Statement does not evaluate

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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

Fredrik (sorry I misspelled your name earlier),

There aren't any pivot tables and I don't believe the file is corrupt -
I pretty regularly use Rob Bovey's CodeCleaner to help avoid that. Here
is the explanation I had from my other "source" (Brad Yundt, Top Excel
Expert at Experts-Exchange):

"The Select Case is comparing your command bar names to the expressions
that follow each Case statement. With the double quotes, you are
comparing a name like "Format tool bar" to the letters W, F and A.
Obviously, there will never be a match. Without the double quotes, you
are comparing the name to the contents of the variables W, F and A--one
of which may very well contain the string "Format tool bar". If so,
then there would be a match for that Case, and the subsequent bit of
code is executed."

Perhaps I hadn't made it clear that W, F & A were variable names that
were developed in earlier parts of the code (not shown here).

HTH

Jeff

"Jeff" <[email protected]> wrote in message
news:[email protected]...
> Fredrik (sorry I misspelled your name earlier),
>
> There aren't any pivot tables and I don't believe the file is corrupt -
> I pretty regularly use Rob Bovey's CodeCleaner to help avoid that. Here
> is the explanation I had from my other "source" (Brad Yundt, Top Excel
> Expert at Experts-Exchange):
>
> "The Select Case is comparing your command bar names to the expressions
> that follow each Case statement. With the double quotes, you are
> comparing a name like "Format tool bar" to the letters W, F and A.
> Obviously, there will never be a match. Without the double quotes, you
> are comparing the name to the contents of the variables W, F and A--one
> of which may very well contain the string "Format tool bar". If so,
> then there would be a match for that Case, and the subsequent bit of
> code is executed."
>
> Perhaps I hadn't made it clear that W, F & A were variable names that
> were developed in earlier parts of the code (not shown here).
>
> HTH
>
> Jeff
>

No problelm, Frederick looks nicer, I wish the Swedish spelling was the
same. Anyway, this explains a great deal. I think it's bad practice to
comparw the value of a property with a variable within a Select Case
statement. Is there something that prevents you from using a constant
expression?

/Fredrik

Hi Fredrik,

Sorry for my late response here ... kind of lost track of this. As far
as "something that prevents me from using a constant expression", the
toolbars that the variables W, F & A refer to are different for
different versions of Excel and screen resolutions so to answer your
question, I think this precludes me from using a constant expression.
Why do you think "it's bad practice to compare the value of a property
with a variable within a Select Case statement"?

Jeff

> No problelm, Frederick looks nicer, I wish the Swedish spelling was
the
> same. Anyway, this explains a great deal. I think it's bad practice
to
> comparw the value of a property with a variable within a Select Case
> statement. Is there something that prevents you from using a constant
> expression?
>
> /Fredrik

"Jeff" <[email protected]> wrote in message
news:[email protected]...
> Hi Fredrik,
>
> Sorry for my late response here ... kind of lost track of this. As far
> as "something that prevents me from using a constant expression", the
> toolbars that the variables W, F & A refer to are different for
> different versions of Excel and screen resolutions so to answer your
> question, I think this precludes me from using a constant expression.
> Why do you think "it's bad practice to compare the value of a property
> with a variable within a Select Case statement"?
>
> Jeff
>
Ahh.. Interesting. The reason I think it's bad parctice is beacuse it makes
me believe the design of the code is bad. There is an alternative way to
code this. You could use nested select case like below:

With cmd
Select Case ExcelVers
Case 10
Select Case .Name
'Your code here
End Select

Case 11
Select Case .Name
'Your code here
End Select

Case Else
MsgBox "Not supported version"
End Select
End Select
End With

With this code, you can compare against constant values. I think it's easier
to debug although it may look somewhat messy.

/Fredrik