Why Error Message "End Select without Select Case"?

[GoFigure]

The following is a modification of a procedure to create a first-shee
"Index" of all worksheets in a workbook that's located in multipl
places around the Web. I do not want it to create index entries for th
Index sheet and two others with the names "MenuSheet" and "Ne
Customer".

But when it runs, it generates the compile error ""End Select withou
Select Case" even though there's clearly a Select Case statement an
only one.

What's wrong here?


Code
-------------------
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim wSheetIndex As Long
Dim M As Long
M = 1
Application.ScreenUpdating = False
ActiveSheet.Unprotect ' Unprotect "Index" sheet
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "Customer Index"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
' Don't want an index entry for MenuSheet or New Customer
Select Case wSheet.Name
Case Not Me.Name
Case Not "MenuSheet"
Case Not "New Customer"
Case Else
M = M + 2
' Add 'Return to Index' link on worksheet
' format it to bold yellow with full centering
With wSheet
.Unprotect
.Range("A1").Name = "Start" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("B1:C1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Return to Index"
With .Cells.Range("B1:C1")
.Merge
.Interior.ColorIndex = 6
.Interior.Pattern = xlSolid
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Bold = True
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
End With
wSheet.Protect
End Select
Next wSheet
Application.ScreenUpdating = True
ActiveSheet.Protect ' Protect Index sheet
End Su
-------------------


Many thanks,

- A

 

[Rowan Drummond]

You were actally missing an End With. Try:

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim wSheetIndex As Long
Dim M As Long
M = 1
Application.ScreenUpdating = False
ActiveSheet.Unprotect ' Unprotect "Index" sheet
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "Customer Index"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
' Don't want an index entry for MenuSheet or New Customer
Select Case wSheet.Name
Case Not Me.Name
Case Not "MenuSheet"
Case Not "New Customer"
Case Else
M = M + 2
' Add 'Return to Index' link on worksheet
' format it to bold yellow with full centering
With wSheet
.Unprotect
.Range("A1").Name = "Start" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("B1:C1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Return to Index"
With .Cells.Range("B1:C1")
.Merge
.Interior.ColorIndex = 6
.Interior.Pattern = xlSolid
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Bold = True
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
End With
.Protect '<<changed
End With '<<Added
End Select
Next wSheet
Application.ScreenUpdating = True
ActiveSheet.Protect ' Protect Index sheet
End Sub

Hope this helps
Rowan

 

[GoFigure]

Many thanks, Rowan. Guess I was really tired to miss the "End With".

EXECUTION SPEE

This code must be a kludge because it takes to take an indordantly lon
time to execute. I changed the three original IF statements I had to
Case statement in the hopes of improving the speed of execution. N
soap.

When I use the Timer function for each Sub execution, I get th
following results (seconds):


- 8 worksheets ~ 0.9
- 10 worksheets ~ 1.3
- 13 worksheets ~ 2.0


Of course, this elapsed time is very noticeable.

Does anyone have any ideas for how I could make this code mor
efficient?

Many thanks,

- A

 

[Rowan Drummond]

Hi Al

Not sure why it would be slow but you might have some success speeding
it up if you turn off calculation, execute then turn calculation back on eg:

Dim calcmode As Long

calcmode = Application.Calculation 'Get current calc setting
Application.Calculation = xlCalculationManual

'yourcode here

Application.Calculation = calcmode 'return to initial setting

Also are you sure you need to add the hyperlink to each sheet every time
the index sheet is selected. It seems you would just be doing this over
and over again.

Finally you can probably get rid of the M = 1, M= M + 2 stuff as you are
not using M anywhere from what I can see.

Hope this helps
Rowan

 

[GoFigure]

Hi, Rowan,

What a difference!

I turned calculation off and modified the code so that the
sheet-specific hyperlink executes only when the sheet doesn't already
have a hyperlink.

Now, there's no hourglass and no flicker.

Thanks so much,

- Al

 

[Rowan Drummond]

You're welcome. Thank's for the feedback.