This example checks the Location property of the first script in worksheet one in the active workbook and displays the location in a message box.
If ActiveWorkbook.Worksheets(1).Scripts(1).Location = 1 Then
MsgBox "The script in located in the header."
End If
If ActiveWorkbook.Worksheets(1).Scripts(1).Location = 2 Then
MsgBox "The script in located in the body of the worksheet."
End If