I have a macro assigned to an enter button on a spreadsheet. When the enter
button is pushed, data entered into row 1 (the data entry cell) is moved to
row 2 and so on until row 6. Rows 2 to 6 are locked out to prevent anyone
from accidentally deleting or overwriting data in those rows. I would like a
condition set that when data gets to row 6, the last row, a message box
informs that the list is full. And at that point when the enter button is
clicked the original macro does not run until the list is deleted. I do have
a macro for the deletion part.
Please help.....

Place a line of code in the macro that runs when they click the button before
anything happens:
if cells(6,1).value<empty then
msgbox(prompt:="You may Not enter anything else, the file is full!",
title:="Entry Not Allowed", buttons:=Vbcritical)
goto theend
end if

Then place a marker at the bottom:

TheEnd:
end sub
"Savuti" wrote:

I have a macro assigned to an enter button on a spreadsheet. When the enter
button is pushed, data entered into row 1 (the data entry cell) is moved to
row 2 and so on until row 6. Rows 2 to 6 are locked out to prevent anyone
from accidentally deleting or overwriting data in those rows. I would like a
condition set that when data gets to row 6, the last row, a message box
informs that the list is full. And at that point when the enter button is
clicked the original macro does not run until the list is deleted. I do have
a macro for the deletion part.
Please help.....

I beleive that best thing is to give those protected rows a range name. In
this case you can not retrict with top 6 rows. Instead you can allow the
users to add more rows if they required.

In code where you are using 'Enter functionality' check if the current
cursor is jump into Named range area then give message and place the cursor
back to it's last original positon

--
Malik


"Savuti" wrote:

I have a macro assigned to an enter button on a spreadsheet. When the enter
button is pushed, data entered into row 1 (the data entry cell) is moved to
row 2 and so on until row 6. Rows 2 to 6 are locked out to prevent anyone
from accidentally deleting or overwriting data in those rows. I would like a
condition set that when data gets to row 6, the last row, a message box
informs that the list is full. And at that point when the enter button is
clicked the original macro does not run until the list is deleted. I do have
a macro for the deletion part.
Please help.....

Hi Mike, I appreciate the help. Just so I am on the same page, do I place the
code that you gave before the start of my existing code - which is:

Range("B9:I16").Select
ActiveSheet.Unprotect


Also what does - Then place a marker at the bottom - mean

I am pretty much a beginner at this

Thanks

"Mike H." wrote:

Place a line of code in the macro that runs when they click the button before
anything happens:
if cells(6,1).value<empty then
msgbox(prompt:="You may Not enter anything else, the file is full!",
title:="Entry Not Allowed", buttons:=Vbcritical)
goto theend
end if

Then place a marker at the bottom:

TheEnd:
end sub
"Savuti" wrote:

I have a macro assigned to an enter button on a spreadsheet. When the enter
button is pushed, data entered into row 1 (the data entry cell) is moved to
row 2 and so on until row 6. Rows 2 to 6 are locked out to prevent anyone
from accidentally deleting or overwriting data in those rows. I would like a
condition set that when data gets to row 6, the last row, a message box
informs that the list is full. And at that point when the enter button is
clicked the original macro does not run until the list is deleted. I do have
a macro for the deletion part.
Please help.....