locked
Date Picker- having issues adding multiple date pickers to a spreadsheet RRS feed

  • Question

  • I am using Excel 2016 and I'm having issues with the date picker function.

    I'm running Windows 10 on 64-bit.

    I need to add multiple date pickers to non consequent columns. Sometimes the drop downs disappear when I close and reopen the file and another time I had two working and when I attempted to add a third one they disappeared.

    This is the first time I'm using this feature and I don't have any VB experience. 

    I need some direction on how to get this feature to work consistently. Are there any known issues?

    Also, are there any other options besides date picker? I need to provide this spreadsheet to a client and want to make it as easy as possible for them to complete the multiple date fields.

    This is the coding I found online. 

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)   

     

      With Sheet1.DTPicker1   

        .Height = 20            

        .Width = 20            

        If Not Intersect(Target, Range("A:A")) 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 Sheet1.DTPicker2   

        .Height = 20            

        .Width = 20            

        If Not Intersect(Target, Range("E:E")) 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 Sheet1.DTPicker3   

        .Height = 20            

        .Width = 20            

        If Not Intersect(Target, Range("H:H")) 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


    Wednesday, October 4, 2017 10:30 PM

All replies

  • Hi CarlaMVZ,

    Thanks for visiting our forum.

    Then here we mainly focus general issues about Office client. Since your query is related to using VBA code in Excel, I'll move your thread to the following dedicated MSDN forum for Excel:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best regards,
    Yuki Sun



    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Thursday, October 5, 2017 5:50 AM
  • Hello,

    I don't find any function in Excel 2016 to add date picker. After researching, Excel 2016 does not provide any built-in feature to add date pickers. How do you add the date picker? Do you install some add-ins?

    Please visit the similar thread: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother-mso_2016/date-picker-in-excel-2016/5591d33f-09f6-4ff9-987e-abfa7e984e0f

    Regards,

    Celeste


    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 6, 2017 6:09 AM
  • Hi CarlaMVZ,

    I try to test the code on my side and I find that it is working fine for me.

    you can see the testing result below.

    you had mentioned that,"I need to add multiple date pickers to non consequent columns. Sometimes the drop downs disappear when I close and reopen the file and another time I had two working and when I attempted to add a third one they disappeared."

    here, I suggest you to again check your code and try to correct it as per your requirement.

    maybe you had implement the incorrect logic and because of that you are getting wrong result.

    you can see that you had wrote the logic to make the date picker visible true and false according to the condition for each date picker.

    so try to put the break point on the first line of "Worksheet_SelectionChange" event. so whenever event get occur you can try to step in to the code to understand the execution of code.

    this way you can try to check , verify and correct your logic.

    let us know the current status of your issue.

    we will try to provide further suggestions to solve the issue.

    Regards

    Deepak


    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 9, 2017 8:40 AM