Select and Sort by tab color - crashes Excel

  • Thread starter Thread starter jeremiah
  • Start date Start date
J

jeremiah

My code below works as long as it finds that tab color, but in the event
there are no tabs with this colorindex it crashes Excel completely. My tabs
colors are assigned earlier in the process by criteria. It is looking for
seasonal employees, which we will not have until March so when this part of
the code runs it crashes. Is there an easy workaround?

Sub Select1seasonal()
Dim s() As String
Dim ws As Worksheet
Dim i As Integer
i = 0
For Each ws In Worksheets
If ws.Tab.ColorIndex = 37 Then
ReDim Preserve s(i)
s(i) = ws.Name
i = i + 1
End If
Next ws
Worksheets(s).Select
 
If no tabs are found, you are telling Excel to select a non-existent
worksheet using an empty array as the worksheet name.
That's probably the reason for the crash.
If any tab has the proper color the value of i will not be zero so
you can use this below the "Next ws" line...
'--
If i > 0 Then
Worksheets(s).Select
Else
MsgBox "No worksheets found. "
End If
--
Jim Cone
Portland, Oregon USA


"jeremiah"
<[email protected]>
wrote in message
My code below works as long as it finds that tab color, but in the event
there are no tabs with this colorindex it crashes Excel completely. My tabs
colors are assigned earlier in the process by criteria. It is looking for
seasonal employees, which we will not have until March so when this part of
the code runs it crashes. Is there an easy workaround?

Sub Select1seasonal()
Dim s() As String
Dim ws As Worksheet
Dim i As Integer
i = 0
For Each ws In Worksheets
If ws.Tab.ColorIndex = 37 Then
ReDim Preserve s(i)
s(i) = ws.Name
i = i + 1
End If
Next ws
Worksheets(s).Select
 
Fortunately have have the variable i:

Sub Select1seasonal()
Dim s() As String
Dim ws As Worksheet
Dim i As Integer
i = 0
For Each ws In Worksheets
If ws.Tab.ColorIndex = 37 Then
ReDim Preserve s(i)
s(i) = ws.Name
i = i + 1
End If
Next ws
If i <> 0 Then
Worksheets(s).Select
End If
End Sub
 
Once again, you guys have saved me. Thank you...
Gary''s Student said:
Fortunately have have the variable i:

Sub Select1seasonal()
Dim s() As String
Dim ws As Worksheet
Dim i As Integer
i = 0
For Each ws In Worksheets
If ws.Tab.ColorIndex = 37 Then
ReDim Preserve s(i)
s(i) = ws.Name
i = i + 1
End If
Next ws
If i <> 0 Then
Worksheets(s).Select
End If
End Sub
Click to expand...
 
you don't even need to use an array, and thus have to worry about a
non-existent sheet.

Sub Select1seasonal()
Dim i As Long
For i = 1 To Worksheets.Count
If Worksheets(i).Tab.ColorIndex = 37 Then
Worksheets(i).Select False
End If
Next
End Sub
 
Back
Top