Select Case Statement does not evaluate

J

Jeff

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
 
C

Charlie

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
 
J

Jeff

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
 
F

Fredrik Wahlgren

Jeff said:
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
Click to expand...

Weird. What happens if you enter something like:

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

/Fredrik
 
J

Jeff

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
 
F

Fredrik Wahlgren

Jeff said:
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
Click to expand...

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
 
J

Jeff

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
 
F

Fredrik Wahlgren

Jeff said:
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
Click to expand...

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
 
J

Jeff

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
 
F

Fredrik Wahlgren

Jeff said:
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
Click to expand...

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
 
J

Jeff

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
 
F

Fredrik Wahlgren

Jeff said:
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
Click to expand...
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Select Case Code does not run... 10
Select Text to End of Footnote 9
Macro to Select text from a point given to a specific word 2
Word macro that can call cells out of Excel for a find and replace 0
VBA word change keys 0
Select Case code error 8
Keep First Letters, with exceptions 0

Top