Like wlad, I pine for an alternative to the DTPicker that comes in mscomctl2.ocx. I can’t move workbooks between my WinXP and Win7 machines without problems with this file, not to mention distributing it outside of my house.
A few weeks ago I set about making an alternative using built-in controls. I didn’t go well, but I recently decided to give it another shot. I still failed, but less miserably than before, so I thought I’d post what I had. Maybe you can help me make it work.
This is a time picker only, for now. Hey, you have to start somewhere. The first problem is that it’s a ton of code for ‘one’ control. But if it worked, I might be able to look past that. The real problem comes with the .SelStart and .SelLength properties of the textbox. Whenever I use these properties, I want to pull my hair out. This time was no exception.
The control consists of a combination of a textbox and a spinbutton. The textbox displays the time and the spinbutton allows you to change the active element of the time, either up or down. I think I got the navigating part down. My goal was to make sure that one time element (hour, minute, second, and AM/PM) is always selected. In addition to locking the textbox, I captured two events: KeyUp and MouseUp.
Here’s KeyUp
If KeyCode = miRIGHTARROW Then
If Me.IsHour Then
SelectMinute
ElseIf Me.IsMinute Then
SelectSecond
Else
SelectAMPM
End If
ElseIf KeyCode = miLEFTARROW Then
If Me.IsAMPM Then
SelectSecond
ElseIf Me.IsSecond Then
SelectMinute
Else
SelectHour
End If
Else
If Me.IsHour Then
SelectHour
ElseIf Me.IsMinute Then
SelectMinute
ElseIf Me.IsSecond Then
SelectSecond
Else
SelectAMPM
End If
End If
End Sub
If the user presses the right arrow, select the element to the right. Similar for the left arrow. Any other key (up and down arrows were problem) keeps the selection the same. The MouseUp was a little shorter.
If Me.tbxTimePicker.SelStart < 3 Then
SelectHour
ElseIf Me.tbxTimePicker.SelStart < 6 Then
SelectMinute
ElseIf Me.tbxTimePicker.SelStart < 9 Then
SelectSecond
Else
SelectAMPM
End If
End Sub
This check where the cursor is and selects appropriately. It all seems to be working well. Changing the time with the spinbutton proved more difficult. Here’s the SpinDown event.
If Me.IsHour Then
Me.tbxTimePicker.Text = Format(1 + TimeValue(Me.tbxTimePicker.Text) – mdHOUR, msFMTTIME)
SelectHour
ElseIf Me.IsMinute Then
Me.tbxTimePicker.Text = Format(1 + TimeValue(Me.tbxTimePicker.Text) – mdMINUTE, msFMTTIME)
SelectMinute
ElseIf Me.IsSecond Then
Me.tbxTimePicker.Text = Format(1 + TimeValue(Me.tbxTimePicker.Text) – mdSECOND, msFMTTIME)
SelectSecond
Else
Me.tbxTimePicker.Text = Format(1 + TimeValue(Me.tbxTimePicker.Text) – mdTWELVE, msFMTTIME)
SelectAMPM
End If
End Sub
The SpinUp event is almost identical except there are plus signs instead of minus signs. Here’s are the two problems I’ve identified so far:
- Click the spinbutton and focus stays on the spinbutton rather than going back to the textbox. Click it again, and the textbox gets focus as I would expect it to. I can’t figure out why it works on alternate times.
- Click the spinbutton so that it has the focus. Then press the right arrow. The textbox gets the focus back and moves to the minute element (if you’re on hour), which all seems fine. Except the textbox text reverts back to the pre-click value. WTF?
If you want to check out the code and ferret out the bugs, please let me know what you find.
You can download AlternateTimePicker.zip
I’ve encountered the focus issue with spin buttons and scroll bars. I don’t know how to avoid it.
With regard to the focus problem with the SpinButton, I think a reasonable alternative would be to use two CommandButtons, placed one on top of the other, so they look sort of like a SpinButton and use the letters “t” and “u” from the Marlett font as the captions for those CommandButtons. I just tried it and it seems to work well.
I created a datetime picker some time ago, which is in active use in many of the excel applications i’ve written:
http://vangelder.orconhosting.net.nz/excel/datetimepicker.html
I dont recall if I had focus problems. Maybe by examining another approach, the solution will present itself?