User Form Time Picker

I've tweaked some code for a pop up time picker I found here:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_24868820.html

I'd like to have the time picker "remember" the value in the cell if I double click on it. Currently, I can change the time and it stays in the cell but if double click the cell again, it defaults back to 8:00 AM. In other words, if I change the time to 7:00 AM and double click the same cell, I want it to say 7:00 AM.

I've attached an example file. Thank you.
UserForm-Time-Question.xls
LVL 1
tracymsAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try

Private Sub UserForm_Initialize()
     FrmTime.txtFollowupTime1.Locked = True
     FrmTime.txtFollowupTime1.Value = Format(TimeValue(ActiveCell), "hh:mmAM/PM")
     init_disp = Trim(FrmTime.txtFollowupTime1.Value)
End Sub

Open in new window

Regards
0
 
Rory ArchibaldConnect With a Mentor Commented:
You could use:
Private Sub UserForm_Initialize()
        
     With Me.txtFollowupTime1
        .Locked = True
        If Len(ActiveCell.Value) > 0 And IsDate(ActiveCell.Value) Then
            .Value = ActiveCell.Text
        Else
            .Value = Format(TimeSerial(8, 0, 0), "hh:mmAM/PM")
        End If
            init_disp = Trim(.Value)
    End With

End Sub

Open in new window


BTW, I think you want the Cancel = True in your BeforeDoubleClick code inside the If part rather than the Else part.
0
 
tracymsAuthor Commented:
Thanks! Both worked so I will divide the points. On another note - I'd love to have the same thing for dates. I've attached a sample I found on the Internet and will open another question if you're interested in solving it.
DateCombos.xlsm
0
 
tracymsAuthor Commented:
I gave the first posted solution extra points since he was first. I hope this seems fair to you both. Thanks again!
0
 
tracymsAuthor Commented:
0
All Courses

From novice to tech pro — start learning today.