Date Picker VBA

[Robandemmy]

I wanted to ease the input of dates for some employees in a shop. There are 4 columns that require date inputs: G, H, J and K. I added one date picker for columns G:H and another one for J:K with the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Sheet18.DTPicker1
.Height = 20
.Width = 20
If Not Intersect(Target, Range("G4:H9999")) Is Nothing Then
.Visible = True
.Top = Target.Top
.Left = Target.Offset(0, 1).Left
.LinkedCell = Target.Address
Else
.Visible = False
End If
End With

With Sheet18.DTPicker2
.Height = 20
.Width = 20
If Not Intersect(Target, Range("J4:K9999")) Is Nothing Then
.Visible = True
.Top = Target.Top
.Left = Target.Offset(0, 1).Left
.LinkedCell = Target.Address
Else
.Visible = False
End If
End With

End Sub

I cannot find how to turn off the time stamp display, I would like just DD/MM/YYYY. Cell formatting does not do anything. Strangely enough, as I am testing this as I write this post, J:K are displayed as I wish, G:H are not.

Also of note, my knowledge is < a newbie when it comes to VBAs, I think I got this code from a youtube clip. The code sometimes fails and causes an error that I am trying to recreate.

Any help is appreciated.

 

[Robandemmy]

Edit:

So the error I am getting is when I highlight an area of cells to change the formatting (borders or fill color) and comes up:

Run-time error '440':
Invalid property value

When I hit debug, this line of the code is highlighted:

.LinkedCell = Target.Address

 

[John_w]

To get rid of the time part (it is a fixed time for me, not a timestamp) of the cell value returned by the Date and Time Picker Control, in the Developer tab click Design Mode and right-click the DT Picker control and click DTPicker Object -> Properties and change the Time setting to 00:00:00.

I'm unable to reproduce the error when selecting an area of cells, but try changing the first If statement to:

        If Not Intersect(Target, Range("G4:H9999")) Is Nothing And Target.Columns.Count = 1 Then

and similar for the second If.

 

[Robandemmy]

Awesome! Both those edits work like a charm, much appreciated!!