interrupting manual autofill

lokiluke

New Member
Joined
Aug 12, 2011
Messages
46
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:

Code:
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

lokiluke

New Member
Joined
Aug 12, 2011
Messages
46
i may have found a solution with
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,841
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top