Filter the microsoft date and time picker control output

[Jord57]

Hi

Firstly I am a complete novice at VBA. I have used the Microsoft date and time picker control output to input dates into a column. I want to be able to filter that column using the filter function at the top. However every time I try to filter I get a pop-up with "runtime error 440 Invalid code value". I assume its because I have set specific cell values in the VBA code and filtering changes these values. This is the code I have used:

Private Sub DTPicker21_CallbackKeyDown(ByVal  KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As  String, CallbackDate As Date)

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  With Sheet1.DTPicker21
    .Height = 20
    .Width = 20
    If Not Intersect(Target, Range("G5:G3000,H5:H3000,I5:I3000")) 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

Any Help with this would be much appreciated.

Thanks

[p45cal]

Can't reproduce that error here, but you could try adding the line:

If Target.Cells.Count <> 1 Then Exit Sub

straight after the 1st line (Private Sub Worksheet_SelectionChange(ByVal Target As Range)) to prevent a multiple-cell selection activating the DTPicker.

[SamT]

You can try, as the first lines of the Procedure

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("G5:I3000")) Is Nothing Then exit sub

Edit to add: Use the Worksheet_BeforeDoubleClick Event sub to eliminate the issue

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    if Intersect(Target, Range("G5:I3000")) Is Nothing then Exit Sub

    Cancel = True
    with Sheet1.DTPicker21
        .Height = 20
        .Width = 20
        .Visible = True
        .Top = Target.Top
        .Left = Target.Offset(0, 1).Left
        .LinkedCell = Target.Address
    end with
End Sub

[Jord57]

Thanks for your quick reply. P45cal that worked perfectly. Thanks you so much.