interrupting manual autofill

[lokiluke]

I am using Worksheet_SelectionChange to monitor how many lines in my spreadsheet have been entered since the last cell contained the value "BLANK" or "STD" and if it is greater than 39 lines it will prompt the user to put in a BLANK and STD. this all works great when the users put in every line individually but doesnt work when the use autofill by dragging with the mouse. is there a way i can monitor how many lines they are autofilling and cut them off if they go too far?

i was thinking of just 'hijacking' the autofill but can seem to see a way to do it.

the code for the Worksheet_SelectionChange is below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim objFindBlank As Object
Dim lngFirstRowBlank As Long
Dim lngLastRowBlank As Long
Dim objFindStd As Object
Dim lngFirstRowStd As Long
Dim lngLastRowStd As Long
    If Target.Row > 10 And Target.Column = 3 Then
    
        With ActiveSheet.Range("E11:E" & Target.Row)
            Set objFindBlank = .Find("BLANK", LookIn:=xlValues, lookat:=xlWhole)
            
            If Not objFindBlank Is Nothing Then
                lngFirstRowBlank = objFindBlank.Row
                
                Do
                    Set objFindBlank = .FindNext(objFindBlank)
                    If objFindBlank.Row > lngFirstRowBlank And objFindBlank.Row > lngLastRowBlank Then
                        lngLastRowBlank = objFindBlank.Row
                    End If
                Loop While Not objFindBlank Is Nothing And objFindBlank.Row <> lngFirstRowBlank
            End If
                
            Set objFindStd = .Find("STD", LookIn:=xlValues, lookat:=xlWhole)
            
            If Not objFindStd Is Nothing Then
                lngFirstRowStd = objFindStd.Row
                
                Do
                    Set objFindStd = .FindNext(objFindStd)
                    If objFindStd.Row > lngFirstRowStd And objFindStd.Row > lngLastRowStd Then
                        lngLastRowStd = objFindStd.Row
                    End If
                Loop While Not objFindStd Is Nothing And objFindStd.Row <> lngFirstRowStd
            End If
        End With
        
        If lngLastRowBlank < lngFirstRowBlank Then
            lngLastRowBlank = lngFirstRowBlank
        End If
            
        If lngLastRowBlank < 11 Then
            lngLastRowBlank = 10
        End If
            
        If Target.Row - lngLastRowBlank > 39 Then
            MsgBox "you need a blank"
        End If
        
        If lngLastRowStd < lngFirstRowStd Then
            lngLastRowStd = lngFirstRowStd
        End If
            
        If lngLastRowStd < 11 Then
            lngLastRowStd = 10
        End If
            
        If Target.Row - lngLastRowStd > 39 Then
            MsgBox "you need a STD"
        End If
        
    End If
    
End Sub

any help would be awesome

 

[lokiluke]

i may have found a solution with

For Each rng In Range(Target.Address)
    If (rng.Row - lastrow) > 9 Then
        rng.Value = ""
        blwarn = True
    End If

just tested it in test book, will try it when i get to work tomorrow and see, if anyone can see a better way i am more that willing to take suggestions :D