Require Double Left Click to Enter Data Into Cell

Hi...I've wrestled with the title of this thread. What I'd like to do is to require the User to double left click on a cell in a given range in order to enter data. If the user TYPES in that cell, they would get a message.

In the attached, if the User were to double left click on a cell in Column B, it'll bring up a Calendar from which the User can select a date. The date will then populate the active cell.

The issue I have is that the user CAN type anything into the active cell. I'd like to prevent this. So, if the User sends ANY keystrokes other than "Left Double Click" they'd get a message such as "Please Double Click To Bring Up Calendar".

I've been at this for some time now and haven't figured out a workable solution.

Sorry, the file seems to be too large to upload in native state.

Hi John,

Why not just do a Validation Rule where the Date needs to be between two ranges. When then enter anything but your date, you can specify the error message using Data Validation. You will be able to double clidk and bring up your calendar and put in values but an entry of anything except a date will give the message.

I couldn't open your file.

Hi,
Find the attached, as my motto is 'One test is worth a lot of opinions' Look at Col A for about 10 rows.

This has Data Validation on the cells in Column A. I've named the range so the Data Validation rules are easier. If you don't enter a date you can put any message you want in the data validation dialog...

Then there is a userform with the Date Time Picker on it... DTPicker from Microsoft.

The UserForm is shown with a double click in the range, see the code behind the worksheet. This shows the Userform with the DTPicker. Click on the OK button and it puts the DTPicker's date in the cell that you just double clicked.

I have no idea how I know this stuff. Quesions?

Hope this helps.

Hi MarvinP

Appreciate your input. However, your suggested approach has similar limitations to other approaches I've tried. It'll allow any numeric entry which is, in tum, interpreted as a date. I'd like the user to not be able to enter ANY data EXCEPT from the Calendar so as to avoid entering incorrect data. If the user choses a bad date from the Calendar, so be it.

Hey John,
In the Data Validation you can enter a date range that will keep most bad data from being entered. I had set it to a date after, but you can set it for BETWEEN two dates. So if you were only allowing invoices from 2010 and 2011 you could set those ranges. It will barf if a date is entered outside that range.

I'm thinking that is about as good as I can do.

Hi Deamo

Well you're "idea" does the job. I don't have an issue with

using a global boolean type variable

That's why God or (Mr. Gates) made 'em.

Had to make a sleight adjustment to my Calendar Click macro

Please Login or Register  to view this content.

but, with that modification, things work as expected.

Thanks.