Limit Row Acess / selection

torweb

Board Regular
Joined
Dec 1, 2003
Messages
136
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!!!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,909
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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
Code:
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

Board Regular
Joined
Dec 1, 2003
Messages
136
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,909
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,323
Messages
5,769,454
Members
425,549
Latest member
Bartekelese

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