Array Storage Problem: Array Elements Deleted on 'End' Command

Tough to say, but if the data was entered into cells, then an array grabs them and clears the cells THEN checks to see if anything is missing, I'd say the logic process is in the wrong order.

1) User fills in data
2) data is checked for missing values and user prompted to complete them
3) data is pulled from sheet into array
4) ....etc.

Hi, thanks for the fast reply!

The current logic process for non-safety ticket inputs is as you have stated: 1) user inputs data, 2) a validation check is performed 3) data is pulled from a sheet into an array. This process appears to be working fine.

The issue is with the Safety Ticket Array's. I suppose I should have posted the logic process for them. Here it is: 1) User selects a cell in the 'Safety Ticket' column. 2) Code in worksheet identifies this, then checks the spreadsheet to find out how many employee records have been created so far. 3) validation checks: If no records started, end sub. If the cell selected has no adjacent data to it, then End sub. 4) Safety Ticket array's re-dimed/preserved for the number of employees. 5) code runs to find out which employee record (basically selection row number minus the first 6 rows that contain other data) has been selected, (variable selectedEmp is set). 6) Userform shown. 7) On clicking 'Confirm Inputs,' the Safety Ticket Arrays element dictated by the variable 'selectedEmp' is created/filled.

Below is the worksheet code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'First a criteria is given to see if the entire row has been selected. We want
'this operation to not trigger the form, since the user's intent is likely to
'either insert or delete rows, not do safety tickets.

'This if statement checks to see if the entire row has been selected, as
'opposed to just a single cell. If just a single cell has been selected, we move
'on to the next if statement:
If Not Selection.Address = ActiveCell.EntireRow.Address Then

    'Check to see if a cell in column 13, (or 'M') has been selected: - >I.e., location of the 'Safety Ticket Column'
    If Not Application.Intersect(Target, Columns(13)) Is Nothing Then
        
        'If one of these cells have been selected, then the next step is to
        'make sure that the cells row has employee data entered already, (i.e.,
        'an employees record has been started). It would be a waste of time doing
        'safety tickets for an undefined employee!:
        
        Dim k As Integer ' a counter variable
        
        Set orginRange = ActiveSheet.Range("b7")
        
        'We will use the first data entry column, 'Employee Role' which is column 'B' to determine
        'whether a record has been started.
        RowLast = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    
        'One thing we will check for is gaps in data. These make data storage
        'difficult, and are best avoided:
        With orginRange
            
            'A For loop is an easy way to check the column for missing data:
            'The - 6 is used to account for the first 6 rows not being record entry
            'cells
             For k = 1 To RowLast - 6
             
                 If IsEmpty(.Offset(k - 1, 0).Value) Then
                 
                    MsgBox "There are row gaps in the data entries. Fix this so that data can be stored properly." _
                        , vbInformation, "Error"
                    .Offset(k - 1, 0).Select
                    End
                
                End If
                
            Next
            
        End With
        
        'If no data gaps are found, then it is safe to assume that the number of
        'employee records created is equal to the last row of data minus the
        '6 non-employee data entering rows at the top:
        
        NEmployee = RowLast - 6
        
        'The above formula will result in -1 if no employees have been entered,
        'since if all data entry rows are empty, cell 'B5' will be the last one contains text, and therefore will be the last row.
        'So 5 - 6 = -1:
        
        If NEmployee = -1 Then
             
            MsgBox "There are no employees to do safety tickets for.", vbInformation, "Error"
            End
        
        End If
        
        'Refers to a subroutine that will re-adjust the arrays size to the current number of employees records have been started for,
        'while preserving the arrays contents.

        Call preserveSafetyTicketData -> basically just redim preserves all Safety Ticket Arrays
        
        'Since we know that there are no data gaps in our data set, we can check to
        'see if the cell the user has selected in column 'M' is adjacent to data filled cells
        'SelectedEmp will be the row number of the selected cell (Activecell) minus 6.
        selectedEmp = ActiveCell.Row - 6
        
        'If the selected cell reference is greater than the number of employee
        'records, then the user has selected a cell without adjacent employee data,
        'meaning no record has been started. If this is the case, we want to let
        'them know:
        
        If selectedEmp > NEmployee Then
        
            MsgBox "Safety Tickets can only be done once a record has been started for them." _
                , vbInformation, "Error"
        
        Else
        
            frmSafetyTickets.Show
        
        End If
        
    End If
    
End If
    
End Sub

Don't use END.

Note
The End statement stops code execution abruptly, without invoking the Unload, QueryUnload, or Terminate event, or any other Visual Basic code. Code you have placed in the Unload, QueryUnload, and Terminate events of forms and class modules is not executed. Objects created from class modules are destroyed, files opened using the Open statement are closed, and memory used by your program is freed. Object references held by other programs are invalidated.

Use Exit Sub if that is the action requried.

Why are you using End?

To exit a sub use Exit Sub.