DoubleClick event to enter date

[FryGirl]

I have a spreadsheet where column H:I will receive a date. All of this is done thru a DoubleClick event with calls the calendar function. All of this works well. Now I need to ensure the user does not simply enter something into the cell that is not a date. I've added a SheetChange event and it seems to be working well, but just curios if this is the proper way to do it? This DoubleClick event is happening on multiple sheets therefore in the ThisWorkbook module.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.CountLarge > 1 Or Target.Row < 3 Then Exit Sub
If Sh.Range("A1").Value = "Unit" Then
If Not Intersect(Target, Sh.Range("H:I")) Is Nothing Then
Call AskFor_A_Date
End If
End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.CountLarge > 1 Or Target.Row < 3 Then Exit Sub
Dim msg As String: msg = "Please double click in the cell and use the calendar to enter a date"
If Sh.Range("A1").Value = "Unit" Then
If Not Intersect(Target, Sh.Range("H:I")) Is Nothing Then
If Not IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target.ClearContents
CreateObject("WScript.Shell").Popup msg, 1, "Enter Date"
Target.Select
Application.EnableEvents = True
End If
End If
End If
End Sub

 

[My Aswer Is This]

Show me this script:
AskFor_A_Date

 

[FryGirl]

It's way too much code to produce here, but it comes from here

Use This Free Excel Date Picker to Enter Dates on Worksheet

Use this Excel Date Picker to select dates for a worksheet cell, or create a quick month or year calendar to sheet. Code is unlocked, to see how it works.

www.contextures.com

 

[My Aswer Is This]

It's way too much code to produce here, but it comes from here

Use This Free Excel Date Picker to Enter Dates on Worksheet

Use this Excel Date Picker to select dates for a worksheet cell, or create a quick month or year calendar to sheet. Code is unlocked, to see how it works.

www.contextures.com

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[FryGirl]

I'm not looking for any help with the code for the calendar, I was just asking about the Workbook change event. I'm asking the user to doubleclick on a cell to enter a date; however, there could be a user that tries to enter something into the cells versus doubleclicking. So say the user simple enters "April". We know that is just text and not a date. I want to tell the user that is not a date and instead to use the doubleclick event. The code about does exactly that. Erases what the user entered and shows a msgbox saying to use the doubleclick. I was just seeking some guidance on if there could be a better way to do this or is this sufficient.

 

[My Aswer Is This]

If hards to know if your doing this the best way when I have no ideal what the code is doing.
What is the Date Picker code doing?

 

[My Aswer Is This]

I'm not looking for any help with the code for the calendar, I was just asking about the Workbook change event. I'm asking the user to doubleclick on a cell to enter a date; however, there could be a user that tries to enter something into the cells versus doubleclicking. So say the user simple enters "April". We know that is just text and not a date. I want to tell the user that is not a date and instead to use the doubleclick event. The code about does exactly that. Erases what the user entered and shows a msgbox saying to use the doubleclick. I was just seeking some guidance on if there could be a better way to do this or is this sufficient.

OK. Understand

 

[Fluff]

The main problem I can see is if the user enters any old number your code will be happy.
You could lock the sheet to prevent users from manually entering info into those cells

 

[FryGirl]

Hi Fluff,

How about if I just check the manually entered number to make sure it falls between today -/+ 365?

 

[Fluff]

Yes you could do that.