View Full Version : [SOLVED] Setting date picker to certain columns only

02-26-2019, 06:54 AM
I use a date picker on most of my workbooks so users enterthe date in a correct format but at the moment it works by double clicking onany cell on a work sheet.

Can anyone show me how to restrict this to ranges in certaincolumns on please?
In the attached example it would be A2:A100 and D2:D100 only

Thanks as always

02-26-2019, 07:19 AM
Probably something like this

Option Explicit

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim myDate As Date
Dim r As Range

Set r = Target.Cells(1, 1)

If Intersect(r, Range("A2:A100")) Is Nothing And Intersect(r, Range("D2:D100")) Is Nothing Then Exit Sub

Set clsCal = New ClsCalendar
myDate = clsCal.SelectedDate

If myDate > 0 Then 'Check to see if it was cancelled
Application.EnableEvents = False
Target.Value = clsCal.SelectedDate

Application.EnableEvents = True
End If

Set clsCal = Nothing
Cancel = True
End Sub

02-26-2019, 07:31 AM
Hi Paul
This worked perfectly and much easier to restrict it tocertain ranges rather than the whole sheet. I have played a few times but couldn’t figureit out.!
Thank you very much for your help and quick reply, alwaysappreciate any help you guys give.