And that’s about everything this date picker can do in a nut shell. There are some enhancements I’d like to make, not sure if I’ll get to them though:
* Update it for Excel 2007 & create nice ribbon buttons
* Support for setting time, like 2:30pm
* If a cell has a formula of =A1, and A1 is just a date, then I’d like to set A1 instead of blowing away the formula that refers to A1
* More ‘semi-smart’ detection for when to show the icon in the cell based on the data or objects around it
* Multiple visible months (previous and next to the left and right of the current month)
* Ability to limit the days available to choose from based on a cell reference
* Make a managed code version of it, right now its VBA and User Forms
* International support
I recently had to add a time control to a form. I used the DTPicker, which allows the user to increment the hours and minutes separately using spin buttons. Similar to the windows Date and Time Properties box.
I long for a better way. I want a clock with one hand that I can move around. For 2:30, I’d put it between the 2 and the 3. Whipping the hand around once would switch between AM and PM. There’s no built in control that comes close to that, I think, so I thought I’d try something else.
The scrollbar is set up with these properties:
Small scroll = 1
Large scroll = 15
Min = 1
Max = 1440
Value = 720 (always start at noon)
The code behind the form is:
tbxTime = Format(sbTime / 24 / 60, “hh:mm AM/PM”)
End Sub
Private Sub sbTime_Scroll()
tbxTime = Format(sbTime / 24 / 60, “hh:mm AM/PM”)
End Sub
Private Sub UserForm_Initialize()
tbxTime = “12:00 PM”
End Sub
The scroll bar suffers from a problem that would also plague my one-handed clock. Granularity. I can move that scroll bar and watch the time zip from 4:00 AM to about 10:00 PM with ease. But I can’t stop at exactly 10:00 PM with anything close to ease. I need a control that gets more precise as I slow down the movement, like my mouse does (yes, I do have one). Now, I get close and then click on the ends to small-scroll one minute at a time.
Hey Dick, I thought you were a keyboard guy and hated mouses. If I understand this post, you’re looking for a way to drag and click your mouse rather than make 6-8 keystrokes.
Yeah, yeah, I knew that comment was coming. :) I’m always thinking about others, though. That’s what makes me so special.
Dick wrote, “…I long for a better way. I want a clock with one hand that I can move around. For 2:30, I’d put it between the 2 and the 3. Whipping the hand around once would switch between AM and PM. There’s no built in control that comes close to that, I think, so I thought I’d try something else…
…The scroll bar suffers from a problem that would also plague my one-handed clock. Granularity…”
You could build something from the “low level” building blocks described below. I tested them and they work so refining the core ideas into a working solution shouldn’t be that hard.
Idea 1 that didn’t work: Use an image in an userform. In the image put an arrow. As the user holds down the mouse and drags it calculate the angular displacement (SIN and COS and that kind of stuff that you posted in some other post recently) and rotate the image control.
Problem: Cannot rotate a control in a UF.
Idea 2 that didn’t work: Create an arrow shape in the worksheet and rotate it as the user clicks on it and drags the mouse.
Problem: Cannot detect mouse movements within a worksheet. Also, a shape doesn’t support events.
Idea 3: Fake rotation in a userform by showing and hiding multiple images. This works.
Create 12 (or 60) images — I tested with only 4 though — of an arrow in different positions around the circle. This is easy with the code below.
In a UF, at design time add a “mouse tracker” control (I used an image control). Make the background transparent and don’t add any image to it. Add code in the mouse tracker control events that tracks the mouse position when the mouse is down and compute the angle corresponding to that position. Show the one image corresponding to this angle and hide the others.
The image it shows is one of 12 or 60 image controls (easiest to add them at runtime I think), each with one of the 12 (or 60) arrow images. Set the background for all as transparent and hide all but one. Make sure the mouse tracker control is on top (ZOrder 0).
I tested with 4 image controls and a “mouse tracker” image control that had event code that was sufficient for “proof of concept” work.
The code to take one arrow shape and make 12 equally distributed across 360 degrees.
Sub ArrowsInACircle()
Dim aShape As Shape
Set aShape = ActiveSheet.Shapes(1)
With aShape
.Name = “Arrow0”
.Rotation = 0
.Copy
End With
Dim I As Integer
For I = 1 To 11
ActiveSheet.Paste
With ActiveSheet.Shapes
With .Item(.Count)
.Rotation = I * 30
.Name = “Arrow” & (I * 30)
Debug.Print .Name, .Left, .Top, .Height, .Width
End With
End With
Next I
End Sub
I’m playing with this idea too Tushar. Clock picture and angle to the mouerpointer’s x and y in the mousemove and mousedown event. My idea for arrow is an array of small shapes, a dotted clock hand. No extra controls allowed in my userforms.
Problem is that I haven’t done vector math the last 30 years …g… and keeping track of speed and rotations: Drag left a little from noon and it will be 11 AM, drag clockwise around and stop on the same spot and it should be 11 PM. Think I need to count +- units passed during the rotation to do this (hmm, that should be easy with your shape show/hide idea). It should also detect the speed, big movements sets 1 hour resoultion, full hours, slower 15 minutes, then maybe 5 or even 1. So I’m struggling. But I love puzzles like this.
Just an idea: If you want to go fast and less precise: drag in the vicinity of the circle’s center. Once you want to be more precise, drag closer to the outside.
Yes. Yes of course. Thanks Jan Karel.
Cant you draw a line on the user form directly? Or build an active x control?
Anyway, i can actally do what Dick wants on my MDA,so there is a control out there some where – maybe ones in the compact .Net Frame work – i.e the .Net Frame work.
I work in a pretty large company. Controls, ocx’es, dll’s are easy to buy or make, impossible to deploy and maintain. I’ve also encountered severe stability problems using them on userforms. So I do whatever I can to avoid them.
But you’re right, this is a simple task made complicated. Which is the fun part.
I mocked something up:
http://www.jkp-ads.com/downloadscript.asp?filename=clockmouse.zip
Works well, nice stuff. – Now if i could only think of a use for it!!!! lol! :-)
I have posted my attempt http://www.andypope.info/vba/clock.htm
I went with the technique Harald mentioned. That is constructing the hands out of multiple controls.
Unfortunately I could not come up with a solution to automatically incrementing am/pm so opted for a switch instead.
Very impressive, Andy. You write beautiful code.
I agree with Harald. Two things for the next version: 1) When I move the minute hand past 12, the hour hand goes back to the previous hour. I’d like it to go forward. 2) Package that up into an ActiveX control for me when you get a few minutes. :)
Oh, another thing: I must not have visited your site in a while. It looks great. Particularly those icons down the left side.
Thanks for the comments guys.
And Dick, when I get another spare 5 minutes I will have a stab at rolling the hours ;)
Andy – anyway of making the form modeless? I tried changing the code attached to the commandbutton, but that just crashed Excel!
Hi Neil,
This quick mod will give you a modaless userform.
In the standard code module change the Main routine.
Sub Main()
FTime.Show vbModeless
End Sub
And in the userform change the Close button event.
Private Sub CommandButton1_Click()
Set m_clsClock = Nothing
Unload Me ‘Me.Hide
End Sub
Thank you!!!!!!!!!!!!!