For...Next or Select Case Statement

[VBA_Newbie79]

I am having difficulty determining which decision statement is the best for
my situation. What the code is supposed to do, is loop through the multiple
choices made by a user in a userform listbox and perform one or more of the
listed statements depending on the choice. Below is my attempt at using the
Select Case statement, but I don't think Select Case will allow more than one
case to be true. As you can see, I am in desperate need of your help.
Thanks in advance!

For i = 0 To Change_Region.ListBox1.ListCount - 1
If Change_Region.ListBox1.Selected(i) = True Then
Change_Region.Hide
Application.ScreenUpdating = False
Select Case ListBox1.Selected(i)
Case Is = 0
Application.Goto ("Africa")
Selection.Copy
Worksheets("CAPSDATA").Activate
Worksheets("CAPSDATA").Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Worksheets("CAPSDATA").Range("A1").Select
Case Is = 1
Application.Goto ("Central_Europe")
Selection.Copy
Worksheets("CAPSDATA").Activate
Worksheets("CAPSDATA").Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Worksheets("CAPSDATA").Range("A1").Select
Case Is = 2
Application.Goto ("Fmr_Soviet_Union")
Selection.Copy
Worksheets("CAPSDATA").Activate
Worksheets("CAPSDATA").Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Worksheets("CAPSDATA").Range("A1").Select
Case Is = 3
Application.Goto ("Indian_Subcontinent")
Selection.Copy
Worksheets("CAPSDATA").Activate
Worksheets("CAPSDATA").Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Worksheets("CAPSDATA").Range("A1").Select
End Select
End If
Next i

 

[Chip Pearson]

Your code tests ListBox1.Selected(i) in the Select Case statement.
Selected(i) will return only True of False, no other value. You have already
tested in your For i statement the Selected(i) property, so you certainly
don't want to test it again in the Select Case. Instead, if I follow your
code properly, you should test only the value of i itself. E.g.,

Select Case i
Case ....
End Select

Each Case clause will perform action to specified Worksheets(i).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Mark Lincoln]

I am having difficulty determining which decision statement is the best for
my situation. What the code is supposed to do, is loop through the multiple
choices made by a user in a userform listbox and perform one or more of the
listed statements depending on the choice. Below is my attempt at using the
Select Case statement, but I don't think Select Case will allow more than one
case to be true. As you can see, I am in desperate need of your help.
Thanks in advance!

For i = 0 To Change_Region.ListBox1.ListCount - 1
If Change_Region.ListBox1.Selected(i) = True Then
Change_Region.Hide
Application.ScreenUpdating = False
Select Case ListBox1.Selected(i)
Case Is = 0
Application.Goto ("Africa")
Selection.Copy
Worksheets("CAPSDATA").Activate
Worksheets("CAPSDATA").Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Worksheets("CAPSDATA").Range("A1").Select
Case Is = 1
Application.Goto ("Central_Europe")
Selection.Copy
Worksheets("CAPSDATA").Activate
Worksheets("CAPSDATA").Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Worksheets("CAPSDATA").Range("A1").Select
Case Is = 2
Application.Goto ("Fmr_Soviet_Union")
Selection.Copy
Worksheets("CAPSDATA").Activate
Worksheets("CAPSDATA").Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Worksheets("CAPSDATA").Range("A1").Select
Case Is = 3
Application.Goto ("Indian_Subcontinent")
Selection.Copy
Worksheets("CAPSDATA").Activate
Worksheets("CAPSDATA").Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Worksheets("CAPSDATA").Range("A1").Select
End Select
End If
Next i

Select Case will use the code for the first true condition found. But
it will do so for each iteration of the For-Next loop. If the loop
runs four times, the Select Case will run each time.

Mark Lincoln

 

[VBA_Newbie79]

Thank you, both. This clears up my confusion!