VBA works from a Button but cant Run if I call it from a Sheet

[PingJu]

I have a Macro that creates a SheetList and then resizes the named range of the List based on the sheets in a work book. The Code works great when I assign the Macro to a button. What I want it to do is run when I select Sheet 1 ("BOM") but excel fails and closes out the application.

Here is my Code that runs perfectly from my button:

Previous Code ....
**************************************************************************
' CREATE INDEX
' **************************************************************************
Call IndexMacro 'See Below



Sub ReIndex()


Application.ScreenUpdating = False
Application.CutCopyMode = False
Worksheets("BOM").Unprotect
' **************************************************************************
' CREATE INDEX
' **************************************************************************
Dim CurRow As Long, Sh As Worksheet, LastRow As Long
Worksheets("INDEX").Activate
CurRow = 2

'*** RESET SHEETS ***
Cells(CurRow, "A").Select
Range(Selection, Selection.End(xlDown)).ClearContents

'*** REBUILD SHEET LIST ***
For Each Sh In ActiveWorkbook.Sheets
If Sh.Name <> "INDEX" And Sh.Name <> "BOM" Then
Cells(CurRow, "A").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
Sh.Name & "!A1", TextToDisplay:=Sh.Name
CurRow = CurRow + 1
End If
Next Sh

' **************************************************************************
' CREATE INDEX RANGE
' **************************************************************************
LastRow = Cells(Rows.Count, "A").End(xlUp).row - 1
With ActiveWorkbook.Names("SheetList")
.RefersTo = .RefersToRange.Resize(LastRow, 1)
End With


' **************************************************************************
' CLEAR PICK UP QTY
' **************************************************************************
Worksheets("BOM").Range("W12:W200").ClearContents


' **************************************************************************
' END OF PROGRAM
' **************************************************************************
Worksheets("BOM").Protect
Worksheets("BOM").Activate
Application.ScreenUpdating = True
Application.CutCopyMode = True
End Sub



Here is the Code that i have on my BOM Sheet:

'Private Sub Worksheet_Activate()
' Call ReIndex
'Worksheets("BOM").Activate
'End Sub


Works on the Button, not when sheet is activated. Any Help would be appriciated.

 

[PingJu]

Sorry, the Actual Code is:
Private Sub Worksheet_Activate()
Call ReIndex
Worksheets("BOM").Activate
End Sub

I had made the lines comments because of the Application Crash.