Activate a worksheet

[Tvercetti]

I have a form which asks the user for a name (from list), and in my module I have the following code, which I hope goes through all the worksheets until it finds the name requested by the user and then activated that worksheet.
But its not working??

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = " & isSelected(i) & " Then
ws.Activate
End If
Next ws

Can anyone tell me whats wrong?

Thanks so much

[Tvercetti]

To be clear, its not giving me error messages but just stays at the frst worksheet

[lucas]

does from list mean from a listbox?

[lucas]

is this on a userform?

would be nice if we could see it.......don't suppose you could post it so we don't have to duplicate it?

[Bob Phillips]

It is bounfd to be the isSelected(i) that has no value.

Assuming the listbox is called Listbox1 use



On Error Resume Next
Worksheets(Listbox1.Value).ACtivate
On Error GoTo 0

[Tvercetti]

Thank you for the replies

It is from a user form yes, the listbox has the name lbNames, in order to get the user's selection I have the following code in the user form (I want the user to be able to select multiple names):

With lbNames
Dim i As Integer
For i = 0 To nNames - 1
isSelected(i + 1) = .Selected(i)
Next
End With

Edited by firefytr: Added VBA tags. my msg

[Norie]

What exactly is isSelected?

[Bob Phillips]

Okay multiple selections, but which do you want activated, the first second, any? You can't have all.

[Tvercetti]

Well, I want to activate the sheet, do something in the sheet and then move on to the next sheet that matches the user selection.

However, for the time being, I would be happy to figure out how to get it to do it for just one selection (i,e. 1 name)

[Norie]

Well if that's what you want to do there is probably no need to activate the sheet.

Can you give some more information on what you are actually trying to achieve?

PS It might also help if you answered the questions that we are asking.:)

[Tvercetti]

Sure, There are several worksheets with the names of students. On each worksheet there are several test results.
A form asks what names I want to select.
Then I want to calculate the average of each student by looping through the worksheets.
Then display a message with the averages.

The issue is at the moment is that it does not seem to respond to the user's selection.

I thought I need to activate the worksheets that match the user inputs to do this.

[Bob Phillips]

With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
nSum = nSum + Worksheets(.List(i)).Range("A1").Value
End If
Next i
MsgBox "Average: " & nSum / .ListCount
End With

[Norie]

There is normally no need to activate/select.

What averages are you actually trying to calculate?