Keeping the cursor in a cell to ensure a valid entry is made

**ReportBuilder2011** · 05-31-2011, 05:43 AM

Hi folks,

I am trying to implement some functionality that will keep a cursor in a cell until a valid entry is made.

I had used the Workbook_SheetChange sub (using VBA) to detect changes in the cell and then called a routine to check for invalid characters. this all works and i can raise the MsgBox to indicate the error then select the cell and go back to it but then it doesnt get triggered again. when i clikc another cell or hit CR I was hoping the checkl would be made again but becuase the cell value hadnt changed from the previous time then the workbook change routine rightly isnt triggered.

So my problem is I need to know how to keep the cursor in the cell until a valid entry is made.

any help greatly appreciated.

**Richard Buttrey** · 05-31-2011, 06:05 AM

Hi,

One way would be to declare a Public variable at Module level, say 'bError As Boolean' and set this to TRUE when the error MsgBox condition is met, and set it to FALSE when valid characters are entered.

Then at the start of your Workbook Sheet Change event use

Please Login or Register  to view this content.

Regards

**ReportBuilder2011** · 05-31-2011, 06:52 AM

Hi Richard.

I tried this but no joy. Using the debugger to stop at the Workbook_SheetChange sub for the sheet in question. I have noticed that if i attempt in any way to change the content of the cell or even remove an invalid charracter and then type the invalid character back in the sub proess is triggered.

My problem is that after an initial attempt to type in a value which is wrong, the cursor correctly puts me back there but if i then hit the CR button or click to another cell, the sub isnt called as nothing has actually changed.

I am trying to work out where I can put the code to actually put me back in the cell if the value is wrong even though the initial check was performed.