I have been writing some code to add to our company's Bill of Materials template Excel file. Every project that we do has its own unique Bill of Materials. I am attempting to make it more dynamic and to add some functionality that it didn't previously have. I have created an add-in BOM MACROS.xlam that each user will add, and stored it on the company network so that I can modify the code and push it out to every file at once. As such, each file will contain the following code within the main worksheet:'Require all variables to be defined within the sub.Option ExplicitPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)Application.Run 'BOM MACROS.xlam'! & WorksheetSelectionChange, ActiveWorkbook, ActiveSheet, TargetEnd SubThe add-in then contains the following code:'Require all variables to be defined within the sub.Option ExplicitSub WorksheetSelectionChange(wb As Workbook, ws As Worksheet, ByVal Target As Range)'This sub will run whenever the selection is changed on this sheet. It will'check if any of the required headings have been deleted and prompt the user to'add the heading back if it has. It will also check if the selection is in the'DOC or PO ATTACHMENTS columns and proceed accordingly.Dim ErrorNo             As LongDim HeaderRow           As LongDim POAttachmentsColumn As LongDim POFilesColumn       As LongDim CodeColumn          As LongDim QTYColumn           As LongDim DescriptionColumn   As LongDim CostColumn          As LongDim LastRow             As LongDim YNAnswer            As IntegerDim DOCColumn           As LongDim Option1Row          As LongDim AdderDeductColumn   As LongDim OptionTotalRow      As LongDim Option1RowCount     As LongDim i                   As Long'Disable screen updating if it is currently enabled.If Not (Application.ScreenUpdating = False) Then Application.ScreenUpdating = False'Go to Error_Handling on an error. Check if any of the required headings have been'deleted. If so, the Find function will throw an error and go to Error_Handling.'Renumber the ErrorNo variable before each Find so it reflects which heading was deleted.On Error GoTo Error_HandlingErrorNo = 1HeaderRow = ws.Columns(1).Find(What:=ITEM, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).RowErrorNo = 2LastRow = ws.Columns(1).Find(What:=LAST ROW, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).RowIf Target.Columns.Count = 1 And Target.Rows.Count = 1 And Target.Row < LastRow Then    ErrorNo = 3    POAttachmentsColumn = ws.Rows(HeaderRow).Find(What:=ATTACHMENTS, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column    ErrorNo = 4    POFilesColumn = ws.Rows(HeaderRow).Find(What:=PO FILES, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column    ErrorNo = 5    DOCColumn = ws.Rows(HeaderRow).Find(What:=DOC, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column    ErrorNo = 6    CodeColumn = ws.Rows(HeaderRow).Find(What:=CODE, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column    ErrorNo = 7    QTYColumn = ws.Rows(HeaderRow).Find(What:=QTY, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column    ErrorNo = 8    DescriptionColumn = ws.Rows(HeaderRow).Find(What:=DESCRIPTION, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column    ErrorNo = 9    CostColumn = ws.Rows(HeaderRow).Find(What:=COST, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column    ErrorNo = 0    If Target.Column = POAttachmentsColumn And Target.Row > HeaderRow And Target.Value <>  Then    'If the selection is in the POAttachmentsColumn, and it is between the HeaderRow and    'the LastRow, and only one cell is selected, and the selected cell is not empty, ask    'the user if they would like to manage the attachments for this item. If so, show the    'AddLinkForm userform.        YNAnswer = MsgBox(Would you like to manage the attachments to be added to the PO for this component?, vbYesNo)        If YNAnswer = vbYes Then            AddLinkForm.Show        End If    ElseIf Target.Column = DOCColumn And Target.Row > HeaderRow And Target.Value = + Then    'If the selection is in the DOCCOlumn, and it is between the HeaderRow and the    'LastRow, and only one cell is selected, and the cell value is +, then run    'the InsertDocumentationRow sub.        InsertDocumentationRow Target.Row, LastRow    End IfElseIf Target.Columns.Count = 1 And Target.Rows.Count = 1 And Target.Row >= LastRow Then    If Target.Column = 1 And Target.Value = + ADD OPTION Then        YNAnswer = MsgBox(Would you like to add another option?, vbYesNo)        If YNAnswer = vbYes Then            With ws                ErrorNo = 10                Option1Row = .Columns(1).Find(What:=OPTION 1, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row                ErrorNo = 11                AdderDeductColumn = .Rows(Option1Row + 1).Find(What:=ADDER/DEDUCT, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column                ErrorNo = 12                OptionTotalRow = .Range(.Cells(Option1Row + 1, AdderDeductColumn), .Cells(Option1Row + 100, AdderDeductColumn)).Find(What:=TOTAL:, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row                ErrorNo = 0                Option1RowCount = OptionTotalRow - Option1Row + 1                LastRow = .Cells(.Rows.Count, AdderDeductColumn).End(xlUp).Row                .Rows(Option1Row & : & OptionTotalRow + 1).Copy                .Rows(LastRow + 2).Insert Shift:=xlDown                Application.CutCopyMode = False                .Cells(LastRow + 2, 1).Value = OPTION  & Right(.Cells(Target.Row, 4), Len(.Cells(Target.Row, 4)) - 14) &  - DESCRIPTION                .Cells(LastRow + 2, 1).Characters(Start:=12, Length:=11).Font.Color = -16776961                If OptionTotalRow - Option1Row > 3 Then                    For i = LastRow + 1 + Option1RowCount To LastRow + 5 Step -1                        .Rows(i).Delete                    Next i                End If            End With        End If    ElseIf Target.Column = DOCColumn And Target.Row > LastRow And Target.Value = + ADD ITEM Then        YNAnswer = MsgBox(Would you like to add an item to this option?, vbYesNo)        If YNAnswer = vbYes Then            ws.Rows(Target.Row).Copy            ws.Rows(Target.Row + 1).Insert Shift:=xlDown            Application.CutCopyMode = False        End If    End IfEnd IfError_Handling:'If the Find function fails, check the ErrorNo variable and proceed accordingly.    If Err.Number = 91 And ErrorNo = 1 Then        MsgBox The ITEM header has been deleted. Please put this header back before continuing.    ElseIf Err.Number = 91 And ErrorNo = 2 Then        MsgBox The last row designator has been deleted. Please put LAST ROW back in the row under the last BOM item.    ElseIf Err.Number = 91 And ErrorNo = 3 Then        MsgBox The ATTACHMENTS header has been deleted. Please put this header back before continuing.    ElseIf Err.Number = 91 And ErrorNo = 4 Then        MsgBox The PO FILES header has been deleted. Please put this header back before continuing.    ElseIf Err.Number = 91 And ErrorNo = 5 Then        MsgBox The DOC header has been deleted. Please put this header back before continuing.    ElseIf Err.Number = 91 And ErrorNo = 6 Then        MsgBox The CODE header has been deleted. Please put this header back before continuing.    ElseIf Err.Number = 91 And ErrorNo = 7 Then        MsgBox The QTY header has been deleted. Please put this header back before continuing.    ElseIf Err.Number = 91 And ErrorNo = 8 Then        MsgBox The DESCRIPTION header has been deleted. Please put this header back before continuing.    ElseIf Err.Number = 91 And ErrorNo = 9 Then        MsgBox The COST header has been deleted. Please put this header back before continuing.    ElseIf Err.Number = 91 And ErrorNo = 10 Then        MsgBox The OPTION 1 header has been deleted at the bottom of the sheet. Please put this header back before continuing.    ElseIf Err.Number = 91 And ErrorNo = 11 Then        MsgBox The ADDER/DEDUCT header for OPTION 1 has been deleted at the bottom of the sheet. Please put this header back before continuing.    ElseIf Err.Number = 91 And ErrorNo = 12 Then        MsgBox The TOTAL: cell for OPTION 1 has been deleted at the bottom of the sheet. Please put this header back before continuing.    ElseIf Err > 0 Then    'If the error is something else, run the error handler.        ErrorHandler    End If    'If screen updating is disabled, enable it.    If Not (Application.ScreenUpdating = True) Then Application.ScreenUpdating = TrueEnd SubThe main function of this code is to check whether certain headings have been deleted. Since the Excel file will be used for many different project types by many different users, I need to make the code as dynamic as possible. As such, I do not have a good way to avoid using the Find function to locate headers for the code. Therefore, if certain headers are deleted, I need to notify the user to add them back.This code also allows the user to add options at the bottom of the sheet. Each option consists of a few rows with certain headers and formulas that I need to keep consistent for every row in the option, so I want the user to use the code to add lines to the option or to add new options. I also want to avoid using buttons on the sheet. I am considering moving this section of the code to a button on the custom add-in ribbon for this add-in to simplify this section of code.This code works fine, but I have a feeling that there is a better way to structure the code that captures the Find errors. Any feedback would be appreciated.
Structure for Multiple Potential Find Errors
vba;excel
Just some things that jump out at me:Integer is outdated. It only exists for backwards-compatibility reasons. The compiler will silently convert them into Long anyway, so just use Long.  YNAnswer should be a Boolean If not A then A. Why not just A?Application.ScreenUpdating = False This:If Err.Number = 91 And ErrorNo = 1 Then    MsgBox The ITEM header has been deleted. Please put this header back before continuing.ElseIf Err.Number = 91 And ErrorNo = 2 Then    MsgBox The last row designator has been deleted. Please put LAST ROW back in the row under the last BOM item.ElseIf Err.Number = 91 And ErrorNo = 3 Then    MsgBox The ATTACHMENTS header has been deleted. Please put this header back before continuing.ElseIf Err.Number = 91 And ErrorNo = 4 Then    MsgBox The PO FILES header has been deleted. Please put this header back before continuing.ElseIf Err.Number = 91 And ErrorNo = 5 Then    MsgBox The DOC header has been deleted. Please put this header back before continuing.ElseIf Err.Number = 91 And ErrorNo = 6 Then    MsgBox The CODE header has been deleted. Please put this header back before continuing.ElseIf Err.Number = 91 And ErrorNo = 7 Then    MsgBox The QTY header has been deleted. Please put this header back before continuing.ElseIf Err.Number = 91 And ErrorNo = 8 Then    MsgBox The DESCRIPTION header has been deleted. Please put this header back before continuing.ElseIf Err.Number = 91 And ErrorNo = 9 Then    MsgBox The COST header has been deleted. Please put this header back before continuing.ElseIf Err.Number = 91 And ErrorNo = 10 Then    MsgBox The OPTION 1 header has been deleted at the bottom of the sheet. Please put this header back before continuing.ElseIf Err.Number = 91 And ErrorNo = 11 Then    MsgBox The ADDER/DEDUCT header for OPTION 1 has been deleted at the bottom of the sheet. Please put this header back before continuing.ElseIf Err.Number = 91 And ErrorNo = 12 Then    MsgBox The TOTAL: cell for OPTION 1 has been deleted at the bottom of the sheet. Please put this header back before continuing.ElseIf Err > 0 Then'If the error is something else, run the error handler.    ErrorHandlerEnd IfShould be a Select Case statement:If Err.Number = 91    Select Case ErrorNo        Case is = 1        ...        Case is = 2        ...        Case is = 3        ...        Case Else        ...    End SelectEnd IfRepeated strings (or any hardcoded value) should be put in variables:Dim headerDeletedText as StringheaderDeletedText = header has been deleted. Please put this header back before continuing....ElseIf Err.Number = 91 And ErrorNo = 3 ThenMsgBox The ATTACHMENTS  & headerDeletedText