none
Using mscomct2.ocx date picker in VBA RRS feed

  • Question

  • Hello:

    I inserted the date picker (mscomct2.ocx) in a new Excel 2016 VBA form, and it creates this code automatically:

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

    End Sub

    I could not find an example of VBA code to capture and date once the user selects a data on the form's calendar control.  I'm sure there is an example, but all I could locate was how to register the data picker.  In other words, how to reference the date captured by the calendar control so I can use it in my VBA code.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Monday, August 27, 2018 5:21 PM

Answers

  • Hi RichLocus,

    The callback "Private Sub DTPicker1_CallbackKeyDown" is not the function to select a data on the form's calendar control.

    It can only be triggered by pressing the keyboard "up" or "down".

    I set a break point for the callback function, and it could be executed.

    Please look at the gif below:

    Please feel free to ask any questions. Looking forward to hearing from you.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by RichLocus Friday, August 31, 2018 4:36 PM
    Thursday, August 30, 2018 2:27 AM
    Moderator
  • The DP's CallbackKeyDown event is an oddity, from memory first need to set a custom format. But just trap its Change event, and in the event return its Value property.
    Thursday, August 30, 2018 7:55 AM
    Moderator

All replies

  • Hi RichLocus,

    You can use "Select Case KeyCode","KeyCodeConstants.vbKeyUp" or "KeyCodeConstants.vbKeyDown" to reference the date captured by the calendar control. Please try the sample code as below:

    Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
    If CallbackField = "XXXX" Then
        Select Case KeyCode
          Case KeyCodeConstants.vbKeyUp
            DTPicker1.Month = DTPicker1.Month + 1
          Case KeyCodeConstants.vbKeyDown
            DTPicker1.Month = DTPicker1.Month - 1
        End Select
      End If
      x = DTPicker1.Month
    End Sub
    

    Hopefully it helps you. Please feel free to ask any questions. Looking forward to hearing from you.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, August 28, 2018 10:59 AM
    Moderator
  • Yuki:

    Thanks for responding.  For whatever reason, when I select a date from the calendar control, the VBA never activates the "Callback" code.  I set a breakpoint on it and also the new code segment I added.  I have never seen a case where the code would not be activated.  There must be something unique about the callback code.  No matter how I traced it, nothing would happen.

    Try it yourself.  Create a blank form, add a calendar control, and trace it. Strange.

    Option Explicit
    Dim X As Date
    '' THIS NEVER GETS EXECUTED
    Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
    
    If CallbackField = "XXXX" Then
        Select Case KeyCode
          Case KeyCodeConstants.vbKeyUp
            DTPicker1.Month = DTPicker1.Month + 1
          Case KeyCodeConstants.vbKeyDown
            DTPicker1.Month = DTPicker1.Month - 1
        End Select
      End If
      X = DTPicker1.Month
    End Sub
    
    '' THIS IS THE ONLY CODE THAT GETS EXECUTED
    '' X ENDS UP WITH NO VALUE
    
    Private Sub DTPicker1_CloseUp()
        MsgBox ("Date Picker CloseUp - Value is " & DTPicker1.Value)
        MsgBox ("Date X = " & X)
    End Sub
    

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Tuesday, August 28, 2018 4:27 PM
  • Hi RichLocus,

    Just like you said, we need something to trigger the "Callback".

    Base on my test, please try the sample code below:

    Private Sub DTPicker1_KeyDown(KeyCode As Integer, ByVal Shift As Integer)
    Dim X As Date
    Call DTPicker1_CallbackKeyDown(KeyCode, Shift, "XXXX", "2018/8/29")
    End Sub
    Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
    If CallbackField = "XXXX" Then
        Select Case KeyCode
          Case KeyCodeConstants.vbKeyUp
            DTPicker1.Month = DTPicker1.Month + 1
          Case KeyCodeConstants.vbKeyDown
            DTPicker1.Month = DTPicker1.Month - 1
        End Select
      End If
      X = DTPicker1.Month
    End Sub
    

    Hopefully it helps you. Please feel free to ask any questions. Looking forward to hearing from you.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Wednesday, August 29, 2018 3:51 AM
    Moderator
  • Yuki:

    Thanks for your effort.  The callback code you provided was set with a "trap" (red dot) so I could see if either segment was executed.  Neither one was.  I finally found a solution:  Just one simple line of code:

    Private Sub DTPicker1_CloseUp()
        MsgBox ("Date Picker CloseUp - Value is " & DTPicker1.Value)
    End Sub

    Here is a picture of the callback trap that proved neither section was referenced:

    I'm going to close out this issue.  Thanks.


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Thursday, August 30, 2018 12:15 AM
  • Thanks for your quick response. Please remember to mark the replies(Include your solution) as answers if they helped and please help us close the thread.

    Thank you for understanding. If you have any question, or update, please feel free to let us know.
    I wish you a happy life!

    Best Regards,

    Simon



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.



    Thursday, August 30, 2018 1:39 AM
    Moderator
  • Hi RichLocus,

    The callback "Private Sub DTPicker1_CallbackKeyDown" is not the function to select a data on the form's calendar control.

    It can only be triggered by pressing the keyboard "up" or "down".

    I set a break point for the callback function, and it could be executed.

    Please look at the gif below:

    Please feel free to ask any questions. Looking forward to hearing from you.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by RichLocus Friday, August 31, 2018 4:36 PM
    Thursday, August 30, 2018 2:27 AM
    Moderator
  • The DP's CallbackKeyDown event is an oddity, from memory first need to set a custom format. But just trap its Change event, and in the event return its Value property.
    Thursday, August 30, 2018 7:55 AM
    Moderator