Message for invalid entry

[LilyLove]

I would like a message to pop up on my spreadsheet if an entry is either blank or greater than 20. Currently an error message will pop up but it will allow the user to navigate to the next cell without correcting the information. If the user inputs "25", I would like the message to pop up and then the original cell to be selected in order for the user to be forced to put a value between 1 and 20.

My current code:

Please Login or Register  to view this content.

[amotto11]

What subroutine have you placed this code in?

[LilyLove]

I placed it in a private sub under the worksheet .

Please Login or Register  to view this content.

[amotto11]

Try to put it into the exit event

[LilyLove]

I'm sorry, I don't quite understand what you mean by that.

[amotto11]

oh sorry i thought for some reason you were in a module or userform, not sure what i was thinking. Try this

Please Login or Register  to view this content.

[amotto11]

if that doesn't work could you post your entire code so that i may play with it on my end.

[LilyLove]

It didn't work on my end. I am honestly very new with VBA so my entire spreadsheet needs a lot of work. That is one component of the spreadsheet and it is located at the top of the sheet. The purpose of the sheet is for the end user to input number of customers and painting ID and the worksheet will automatically generate the remaining inventory. If the inventory is below a minimum level, the user will be notified through a message. The entire spreadsheet is not working but I figured I would take the errors step by step in order to complete the project. The painting ID's are treated similarly to number of customers as the ID's range between 100-110. The code is posted below:

Please Login or Register  to view this content.

[amotto11]

it looks like you are setting sngcustomers as single but you have not specified what it is, for example sngcustomers = Range("A2").value If the code doesn't know what sngcustomers it will not know what you are trying to do

Are you setting this as a range in your workbook like i am guessing your are setting Customers

[LilyLove]

All of the ranges are defined in the workbook. Range B3 = Customers within my working spreadsheet page.

[amotto11]

i am just testing right now and this works for me.

Please Login or Register  to view this content.

This tells me that you are probably not taking in the value of the the range or something

[LilyLove]

I copied your code into Excel and it is still not working correctly. The message displays just the same way as mine had originally, but it does not force the user to remain on the original cell.

[amotto11]

Do you have this code in sheet 1 or do you have it somewhere else. What the code does for me if the user chooses a cell it check b3 to make sure it is not blank and <20 if so no error, and the user can move on else it gives a message and the user will never be able to get out of the loop unless B3 is <20. It will continue to give the error when the user moves out of the cell.