Two Subroutines, one Worksheet Change event

[xDear]

Is it possible for two different subroutines to inhabit the same Worksheet Change Event?

There are two separate results that I would like to achieve, first is making a second worksheet visible based on which process is selected in cell "A1". Secondly, I would like to establish a tab order for the other cells that will require additional data. The examples that I have found both are triggered by the Worksheet Change Event, but so far I have not found a way to combine them so that they both work. Is anyone able to assist and enlighten me? I am using Excel 2010 with Windows 7. The two subroutines are as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

'This subroutine determines if a change has been made to the LOB Process drop down on cell A1
'and deletes the existing "Scorecard Rules" worksheet, if it exists

'Screen updating and Display Alerts are turned off to speed up process
Application.ScreenUpdating = False
Application.DisplayAlerts = False



'Determine if change was made to cell A1
If Target.Address = "$A$1" Then

'Error handling code is employed if there is no Sheet named "Scorecard Rules" found
On Error GoTo NotFound

'If a Sheet named "Scorecard Rules" already exists it is deleted

Sheets("Scorecard Rules").Select
ActiveWindow.SelectedSheets.Delete

Sheets("Reporting").Select

'Error handling code
NotFound:
Sheets("Reporting").Select

'Once there is no "Scorecard Rules" sheet the IDScrCrd subroutine (Module 1) is called
' to generate the correct sheet which corresponds to the LOB Process chosen in cell A1

IDScrCrd

End If

'Turn the Display Alerts and ScreenUpdating function back on
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub




Private Sub Worksheet_Change(ByVal Target As Range)

Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrd = Array("A5", "B5", "C5", "A10", "B10", "C10")

'Loop through the array of cell address
For i = LBound(aTabOrd) To UBound(aTabOrd)
'If the cell that's changed is in the array
If aTabOrd(i) = Target.Address(0, 0) Then
'If the cell that's changed is the last in the array
If i = UBound(aTabOrd) Then
'Select first cell in the array
Me.Range(aTabOrd(LBound(aTabOrd))).Select
Else
'Select next cell in the array
Me.Range(aTabOrd(i + 1)).Select
End If
End If
Next i

End Sub

Thank you to those of you kind enough to help a VBA newbie!

 

[mjbeam]

Private Sub Worksheet_Change(ByVal Target As Range)

    'This subroutine determines if a change has been made to the LOB Process drop down on cell A1
    'and deletes the existing "Scorecard Rules" worksheet, if it exists
    
    'Screen updating and Display Alerts are turned off to speed up process
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    
    
    'Determine if change was made to cell A1
    If Target.Address = "$A$1" Then
    
        'Error handling code is employed if there is no Sheet named "Scorecard Rules" found
        On Error GoTo NotFound
        
        'If a Sheet named "Scorecard Rules" already exists it is deleted
        
        Sheets("Scorecard Rules").Select
        ActiveWindow.SelectedSheets.Delete
        
        Sheets("Reporting").Select
        
        'Error handling code
NotFound:
        Sheets("Reporting").Select
        
        'Once there is no "Scorecard Rules" sheet the IDScrCrd subroutine (Module 1) is called
        ' to generate the correct sheet which corresponds to the LOB Process chosen in cell A1
        
        IDScrCrd
    
    End If
    
    'Turn the Display Alerts and ScreenUpdating function back on
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    On Error Goto 0

    Dim aTabOrd As Variant
    Dim i As Long
    
    'Set the tab order of input cells
    aTabOrd = Array("A5", "B5", "C5", "A10", "B10", "C10")
    
    'Loop through the array of cell address
    For i = LBound(aTabOrd) To UBound(aTabOrd)
        'If the cell that's changed is in the array
        If aTabOrd(i) = Target.Address(0, 0) Then
            'If the cell that's changed is the last in the array
            If i = UBound(aTabOrd) Then
                'Select first cell in the array
                Me.Range(aTabOrd(LBound(aTabOrd))).Select
            Else
                'Select next cell in the array
                Me.Range(aTabOrd(i + 1)).Select
            End If
        End If
    Next i


End Sub

 

[xDear]

Thank you, mjbeam!
This works great when a selection from the drop down lists is chosen and for cells where text is entered and the Enter key is pressed. I am curious as to why the order does not seem to be preserved if the actual Tab key is pressed. Can anyone explain why that is?

Again, many thanks to all for helping me to learn more about VBA!