Stopping Macro After Certain Cell

  • Thread starter Thread starter PRINCE21
  • Start date Start date
P

PRINCE21

I have created a form that staff members have to fill in, the data o
this form is submitted to a database. So basically when ever someon
fills it in he/she clicks submit and a new row of data is created i
the next sheet.

How can i disable the macro that does this say after 10 new rows hav
been filled in. So when all ten members have filled in there data n
more can. The macro will not work.

Is there a way to do something like this!!

Please help than
 
Maybe instead of disabling your macro, you could just check to see if the limit
had been reached.


Option explicit
sub testme01()
dim myRng as range
set myrng = worksheets("Input").range("a7:a16") 'or what ever

if application.counta(myrng) = myrng.cells.count then
exit sub
end if

'else continue with your regular macro.

end sub
 
You might include this type of code at the end of your macro and have it
tell the user that the limit has been reached.
 
The Option Explicit line must be before and outside of any
procedure in the module. Generally, it is the very first line in
the module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
And just to add to Chip's reply.

I have "Option Explicit" added automatically to every module that gets added.

Inside the VBE
Tools|Options|Editor tab|check "require variable declaration"

This way, I must declare my variables before I use them. And if I make a typo,
it's not seen as a new variable that's implicitly declared as a Variant. It's
seen as an undeclared variable. So either I fix the misspelling or I declare
the variable.

By making me do a little extra work dimming my variables, it saves me lots of
extra work on the debugging side.
 
Back
Top