Limit Row Acess / selection

[torweb]

I have a worksheet that is normally locked. I have installed a function that enables the user to unlock the sheet so they may enter data into empty rows on a form. The rows span several columns.

My question is, can I limit the number of rows the user can select when the form is unlocked.

I've been tinkering with no luck....thanks!!!

 

[Joe4]

Here are two options using VBA event procedure code. To get this to work properly, right click on the tab name at the bottom of the sheet, select View Code, and paste the code in the resulting window.

Option 1

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    
    If Target.Row > 100 Then
        Target.ClearContents
        MsgBox "You cannot enter anything after row 100"
    End If
    
    Application.EnableEvents = True
    
End Sub

Option 2

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.Row > 100 Then
        Range("A1").Select
    End If

End Sub

What option 1 will do is if anyone tries to enter anything past row 100, it will automatically clear the entry and tell them they cannot enter anything past row 100.

What option 2 will do is send them back to cell A1 before they can type anything anytime the select a cell past row 100.

 

[torweb]

jimiskey

Thanks, that worked great...The problem is I goofed up in my description. Instead of going down (rows), I need to limit across (cols). Is this still a possibility.

Thanks for your help!

 

[Joe4]

Sure, just change all references to Target.Row to Target.Column. In VBA, columns have numeric references, i.e. A=1, B=2, C=3, etc.