Limit amount of records

[ObiJuan]

Hi Everyone,

I have a shared sign up sheet in excel that I want to limit the amount of entries allowed. For example, I only want to allow 20 people to sign up under a certain category (column). Can I somehow hide/delete the cells or rows that I don't want to allow?


Thanks in advance,
Omar

 

[MickG]

Hi, This code will only allow you to fill in 20 rows in column "A".
Right Click sheet Tab, Select "View Code",:- Vb window appears.
Paste Code into Blank Window.
Close VB Window
The code will run each time you change or add a value to column "A".

Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Ray
[COLOR=navy]If[/COLOR] Target.Column = 1 [COLOR=navy]Then[/COLOR]
    [COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
        [COLOR=navy]If[/COLOR] Rng.Count > 20 [COLOR=navy]Then[/COLOR]
            Ray = Range("A1:A20")
            Rng.ClearContents
            Range("A1:A20") = Ray
        [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[ObiJuan]

Thanks for the speedy reply! That worked like a charm!

 

[ObiJuan]

I'm using a shared spreadsheet that users can sign up. However, i'm finding that people are over-writing other persons names. I forgot that the 'honor system' does not apply to humans. It's very easy to ignore the warning screen and overwrite cells with your own data.

Is there an easy way I can prevent data from being modified after it has been entered? Maybe restrict entry to the current user?

Is this possible with excel?

Thanks in advance,
Omar

 

[lenze]

You could just use Data validation.
Custom
=ROW(A1)<21

As far as preventing an edit, you can use code to lock the cells when data is 1st entered.

lenze