Date Picker for Date Entries in a Userform

[sspatriots]

Good evening,

I have a file with a Userform in it that has multiple date fields in it that the user can enter and send back to the spreadsheet where there are too many columns to scroll across and fill in that way. I've used Ron de Bruin's Date Picker in the past to fill in dates on the spreadsheet with the right click of the mouse accessing the pop up calendar. I'm looking for a way to use it on the Userform that I have or maybe an alternative. Just looking for suggestions right now. Thanks, Steve

​

 

[Micron]

Lock the controls and open the date picker with double click event? Not familiar with R dB's date picker so I'm assuming it would allow you to pass the chosen date to the control(s). Double click works on locked textbox and will accept vba generated input. You could also trap button and shift values for mouse up event if you'd rather right click on the textbox.

 

[sspatriots]

Lock the controls and open the date picker with double click event? Not familiar with R dB's date picker so I'm assuming it would allow you to pass the chosen date to the control(s). Double click works on locked textbox and will accept vba generated input. You could also trap button and shift values for mouse up event if you'd rather right click on the textbox.

The datepicker that comes from Ron De Bruin is a .xlam addon. I'm not sure how to reference that using control buttons.

 

[Micron]

I guess the method would be the same as how it was done by rdb? Maybe this will get you somewhere if the addin is context menu drivenhttps://stackoverflow.com/questions/27968369/run-a-3rd-party-excel-add-in-from-vba

 

[sspatriots]

I followed that link you sent. It looks like the original poster had to use something like the command line below. My question is, if putting in a userform, how do I reference a "Cell" in that command line? I'm guessing you would use a command button click event to trigger the date picker. Still a bit confused on how to make this work.

Application.CommandBars("Cell").Controls("date picker").Execute

 

[Micron]

I'm afraid I don't know but I guess if you executed that line (or some variation of it) in a button click event you could try it and see what happens. Or do what I did to find that thread, I guess - Google the issue. Note that in that thread it was stated that cell had to be a number but I don't recall "cell" being explained at all.

 

[GWteB]

@sspatriots, documentation on this date picker (by @Ron de Bruin) can be found over here:

Home | Ron de Bruin Excel Automation

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

 

[sspatriots]

Ok, I'm back to this DatePicker issue for use inside of a UserForm. I am very close to having it working. Just getting an error message that says "Compile error: Variable not defined" and stops at the line "ctrlName_SetDate". The DatePicker I found is in the link below:

I'm not sure why I'm getting that because I use the same code that was in the video. If I need to define the variable, what would I need to add to this code to define it?

Private Sub Image3_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

ctrlName_SetDate = "txtGR1ESTCOMPLDate"
FormName_SetDate = Me.Name
PopDatePickerX.Show
    
End Sub

Thanks, SS

 

[sspatriots]

I managed to get the datepicker in the last post to work by basically putting my userform inside the userform in the linked video.