Need help with pop-up calendar (Date & Time Picker)

[JStreck]

Okay, in need of more help.

I would like to create a drop down menu within a cell that brings up a calendar, from which the user can click a date and that date is populated into the cell w/the drop down.

So far, I've not been successful, other than using the calendar control, which is a full-time displayed calendar that is bound to a single cell. I have a column of cells that each will potentially have a different date. I would like each cell to have independent drop downs, from which dates can be selected and populated accordingly.

All I've been able to find that is remotely close is this: http://danielcurran.com/instructions/how-to-install-microsoft-date-time-picker-control-60-sp4/ Unfortunately, his recommended files that I downloaded did absolutely nothing. Any other ideas?

Thanks for looking.

 

[drsarao]

This should do it:

Private Sub Calendar1_Click()
    ActiveCell = Calendar1.Value [COLOR=Red]+ TimeValue("23:59:00[/COLOR][COLOR=Red]")[/COLOR]
    Calendar1.Visible = False
    ActiveCell.Select
End Sub

You may have to change the cell format in the column to actually see the added time.

 

[drsarao]

Okay, back again.

The calendar works perfectly, but I've run into another small issue (besides being picky ). Is there a way to get the date to populate with time? I have it referenced in a formula that subtracts it from another cell with date and time. I tried to alter the code above, but keep getting run time errors.

This should not be a problem.
Excel stores Date as a whole number (integer) and stores Time as fraction.

So if there is only date (no fractional/decimal part) then the time is taken to be 00:00:00. You can change the cell format to general to see it in action.

Hence calculations should work as expected. Can you post the formula and the error you get?

In fact if you put default as 23:59:00, you may start getting error of a day depending upon the context.

 

[JStreck]

This should do it:

Private Sub Calendar1_Click()
    ActiveCell = Calendar1.Value [COLOR=Red]+ TimeValue("23:59:00[/COLOR][COLOR=Red]")[/COLOR]
    Calendar1.Visible = False
    ActiveCell.Select
End Sub

You may have to change the cell format in the column to actually see the added time.

Once again, this is exactly what I needed. Thank you so much for your time and efforts. It is very much appreciated, as I'm a complete VB noob!

 

[JStreck]

This should not be a problem.
Excel stores Date as a whole number (integer) and stores Time as fraction.

So if there is only date (no fractional/decimal part) then the time is taken to be 00:00:00. You can change the cell format to general to see it in action.

Hence calculations should work as expected. Can you post the formula and the error you get?

In fact if you put default as 23:59:00, you may start getting error of a day depending upon the context.

So far, it seems to be doing exactly what I need it to, but I understand what you are saying. I'll make note of it and adjust if necessary.

For reference, here's the formula I'm using (Credit goes to AlphaFrog for this one )

=IF(OR(ISBLANK(D10), AND(D10>TODAY(), G10="")),"",IF(AND(D10>=G10, NOT(ISBLANK(G10))),"On Time", "Late"))

I wanted to make sure that when capturing the date value in cells in column D, and subtracting the date and time from cells in column G, that it wasn't cutting short the date in column D. Because column G is populated with a date stamp macro that runs from a check box, I was running into some results from the formula displaying a "Late" value when both dates were the same day. Adding the time value to the calendar was a quick work around that I thought might work. I admit, it's probably not the best way to go about it, but it seems to be working so far. We'll see how it goes. I hope that makes at least a little bit of sense. I don't know how to add my worksheet to this post, so I can't really demonstrate via example.

 

[drsarao]

Finally if you do want to enter the time yourself:

AFAIK, Excel does not have a Time control.

Following mod should allow you to enter the time in addition to the date through an InputBox:
1. Default is current time. (You can change it in code by changing the 3rd argument in InputBox)
2. If InputBox is cancelled or an invalid time is entered then 00:00:00 time is entered in the cell.

Private Sub Calendar1_Click()
    t = InputBox("Enter the time (default is current system time)", _
        "Time Picker", Time) 'ask time
    If (Not InStr(t, ":") > 1) Or Right(t, 1) = ":" Then t = "00:00" 'if invalid time
    ActiveCell = Calendar1.Value + TimeValue(t)
    Calendar1.Visible = False
    ActiveCell.Select
End Sub

You may have to change the cell format to view the whole of the time.

 

[VISH9821]

hello everyone...how can i do this to more than just 1 column?

 

[eguiyab]

I finally got this to work. But I too would like to use this in multiple columns if at all possible.

 

[drsarao]

I finally got this to work. But I too would like to use this in multiple columns if at all possible.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Calendar1.Visible Then Calendar1.Visible = False
    If [B][COLOR=Red]Target.Column <> 3 [/COLOR][/B]Or Target.Count > 1 Then Exit Sub
    Calendar1.Left = Target.Left + Target.Width
    Calendar1.Top = Target.Top + Target.Height
    Calendar1.Visible = True
    Calendar1.Value = Date
End Sub

Change the highlighted above to include whatever column(s) need activation for the Calendar. eg:
If Target.Column > 5 (will work for columns A thru D)
If Target.Column <> 3 Or Target.Column <> 14 (Columns C and N)
etc..