Limit number of elements/values in a dynamic range

[Eric Carolus]

Hi folks

I have a dynamic range called Teachers on sheet 1.
I wish to add teachers' initials to that dynamic range, but I wish to limit the
number of teacher initials to, say 20 teachers only.

I have downloaded the following code (for a button) [thanks to the original author ] and it works well for a button.
I wish to tweak the code so that, if I add directly to the range called Teachers, no
teachers should be accepted to/in that dynamic range.

Private Sub CommandButton1_Click()

If addRng Is Nothing Then
    Set addRng = Range("C5")
Else
    Set addRng = addRng.Offset(1, 0)
    If addRng.Row > 16 Then
        'You may also want to unload the form, or use a message box
        ' to tell the user what is happening here
        Set addRng = Nothing
        Exit Sub
    End If
End If

addRng.Value = Me.TextBox1.Value
End Sub

I will apreciate any and all help.

Thanks
Crow

 

[Micron]

Not sure but you want to run code and count the used rows in a range but only if the edited cell is in a named range? Perhaps

Check whether a changed cell in a named range excel vba

I just need to monitor changes in a worksheet, not in the whole worksheet, just inside a named range. So if any kind of change occurs in any cell it should check the change occurs in that named ran...

stackoverflow.com

 

[Eric Carolus]

Hi Micron

This is not quite what I was looking for.
The idea is to allow on specified number of elements/values to the named range.
Once the specified number of element/values had been added to the named range,
it should not be possible add more values to the named range.

Is this even possible?

Crow

 

[Micron]

IIRC, I meant that as a starting point because you only care about whether or not someone is trying to add/edit within a specific range.

I'm a bit confused though. You said you had code that works from a button click and I provided a link on how to use the update of a range instead so it seems strange that you're asking if what you want to do is possible.
Would you not just need to check if the range is being updated with

If Not Application.Intersect(Target, Range("YourRangeNameHere")) Is Nothing Then

and having inserted the code you say works, let that run?

 

[Eric Carolus]

Hi Micron
I have tried my utmost but I simply cannot make it work.
Thanks you for your help anyway.
Crow

 

[Micron]

If you specify where the range of 20 cells is I'll try to work out something.