Data Validation thru Double Input into Field

**bitswit** · 01-08-2009, 05:48 AM

I have to input data into "ANYDATA" field. Since I need this input to be 100% accurate, I would like my operator to input it TWICE. E.g. 1st input - Obama 2nd input- Obama. Only if BOTH the entries match will the data move into ANYDATA field, else the field becomes blank once again, and the operator has to re-key twice again. In example above, if he had input 2nd time as Obame, then he will have to repeat the process. Can anyone pls help me create suitable formula/ field settings...Thx! Preferably, I would NOT like to create two dummy fields "ANYDATA1" and "ANYDATA2" with corresponding entry in "ANYDATA" if both match.

p.s. Need this to be done using MS-Excel only.

**JBeaucaire** · 01-08-2009, 06:13 AM

See next post for updated, better version.

**JBeaucaire** · 01-08-2009, 06:52 AM

Well, I thought you might want the "double-click" thing to be required, so here you go. This version will protect the sheet with the password "Secret" and unprotect it when you double-click a cell.

For this to work, put this code in a regular Module.

Please Login or Register  to view this content.

Note: this next code determines the range of cells that activate the DoubleClick macro.
Put this code into the sheet where you want this behavior.

Please Login or Register  to view this content.

For instance, right-click on Sheet1 and View Code to see code specifically for Sheet1.

NOTE: Since this macro will protect and unprotect the sheet automatically in the background, you must set the Protection to UNLOCKED in all the other cells that you want to be editable by the user when the sheet is locked.

**bitswit** · 01-08-2009, 08:29 AM

Thanks for your quick and insightful response JB...If you permit, some further questions for you pls :
1. What if I dont want the double-click thing.. can you tweak it so that I get the first input prompt the moment I reach the cell etc etc..? E.g. if double-input is reqd in (say) all cells in column B, then the moment I press Enter from A1 to get to B1, I should get the notification window?
2. Better still, why have any notifications at all.. I should just simply HAVE to input twice into B1 in order to update it with data, else re-input again, till two consecutive inputs match.
3. I think this protection stuff is just awesome. However, I am a little confused. After the correct entry is input in cell B1, the cursor does not move rightwards to C1. How to correct that pls?
4. Further, when I do move right into cell C1, the protection does not allow me to enter data there. What I wanted was the double-entry validation only for (say) all cells in Column B. But normal entry & cursor movements for other cells, A1, C2 etc. The way I see it, I should need to use the unprotect password IF AND ONLY IF I need to over-write, erase, delete any cells in my double-input column, i.e. column B.
5. And lastly, one full set of entries (say) is from row A1 - E1. When I input into E1 and press *Enter*, I would like the cursor to go to first column of next row, i.e. A2. And so on....

Looking forward to your suggestions.. regds

**JBeaucaire** · 01-08-2009, 09:27 AM

OK, ok, another simpler approach. Put this code in the Sheet Code, it will watch column B only and popup a double validation. If you error or quit, it will change nothing. If you succeed in double entry of data, the adjacent cell in Column C will be selected next.

Please Login or Register  to view this content.

**bitswit** · 01-08-2009, 09:54 AM

Looks great JB... any suggestion/ amendment to my point 5 ?

**JBeaucaire** · 01-08-2009, 10:00 AM

Put this code directly below the code I already provided in the same Sheet:

Please Login or Register  to view this content.

**JBeaucaire** · 01-08-2009, 10:04 AM

You can also accomplish the same thing without code at all.

Highlight columns A:E, Ctrl-1 to open Format Box > Protection
Uncheck Protect Cells.

Now protect the sheet with a password and UNCHECK the option for Select Locked Cells.

Now, the user can't select anything past column E, so the cursor jumps back to column A on its own.

FYI.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

**bitswit** · 01-09-2009, 12:42 AM

Thanks for your valuable input. Just a few small (related) points I came across:
1. In the validation procedure for cell B1, at present, when it asks me for validation1, and I merely press *Enter*, cursor simply jumps to C1. Even if I input something and the validations don't match, and I press *Enter* on error message, the cursor jumps to C1. However, what I want is, In case of error/wrong input, cursor should remain in B1 for re-input. Only if validation procedure is complete, cursor should move to C1. And by pressing *Cancel* I should be able to come out of column B.

2. How to protect my VB code & worksheet formatting etc, so that USER should not be able to view/ make any changes at all.

3. This last question is a little unrelated : How to set a password to access/open any folder or a directory?

Looking forward to your help and suggestions... regds

**JBeaucaire** · 01-09-2009, 01:54 AM

OK, last time around the block, hopefully.

1. Here you go:

Please Login or Register  to view this content.

2. Open up the VBE, right-click on your workbook name and select VBAProject Properties, Protection, set your passwords in there and lock for viewing.

3. Not sure. New question should be posted in Excel General forum.