Time choice

[Shane Nation]

Someone on this news group kindly let me have this code which when you
click in one of the cells A2 - A100 brings up a calendar to choose a date
from, which when you click on the date required enters it into the cell.



Does anyone know a similar code which would all the choice of time in the
hh:mm format?



Thanks



Shane



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Application.Intersect(Range("A2:A100"), Target) Is Nothing Then

Calendar1.Left = Target.Left + Target.Width - Calendar1.Width

Calendar1.Top = Target.Top + Target.Height

Calendar1.Visible = True

' select Today's date in the Calendar

Calendar1.Value = Date

ElseIf Calendar1.Visible Then Calendar1.Visible = False

End If

End Sub

 

[Bob Phillips]

Problem there is that there are 86,400 seconds in a day, that's a big form.
You could either just stick to say quarter-hours, then only (ONLY!) 96 to
choose from, hours, only 24, using a form, or have hours minutes and seconds
dropdowns to pick from.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Shane Nation]

Hi Bob

Yes you are right alot of secs to choose from, however I only need Hours and
Mins

Shane

 

[Bob Phillips]

That is still 1440, so two spinners or listboxes?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Shane Nation]

I understand what you are sayng about the number of choices. So I am not
sure if it would work or not. Even if it would I wouldn't know how to code
"spinners or listboxes"

 

[Bob Phillips]

I have posted an example at http://cjoint.com/?imrxoONWt1

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Shane Nation]

Thanks Bob that would be just right if I could get the chosen time into the
cell I click in. And I could hold of the code so I can place it in the sheet
I am working with?

 

[Bob Phillips]

Thanks Bob that would be just right if I could get the chosen time into the
cell I click in.

Just replace

MsgBox Format(frmTime.SelectedTime, "hh:mm")


with

.value = Format(frmTime.SelectedTime, "hh:mm")

And I could hold of the code so I can place it in the sheet
I am working with?

What do you mean by that?

 

[Shane Nation]

Sorry I am ausing confusion.
I need the VBA code that you are using to generate the user form that you
use to select the hours and Mins, or have I missed something.

I would then paste it into the excel spreadsheet I am using.

Shane

 

[Shane Nation]

Sorry been a fool, have now saved the spreadsheet, thank you so much it's
great

Shane

 

[Bob Phillips]

With this solution, you get the calendar and the time form at the same time,
but you do time first then date.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A2:A100"

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Calendar1.Left = .Left + .Width - Calendar1.Width
Calendar1.Top = .Top + .Height
Calendar1.Visible = True

' select Today's date in the Calendar
Calendar1.Value = Date

frmTime.Show
If frmTime.fTimeOK Then
.Value = Format(frmTime.SelectedTime, "hh:mm")
End If
End With

ElseIf Calendar1.Visible Then
Calendar1.Visible = False
End If

End Sub


You also need to change the Calendar Click procedure.


Private Sub Calendar1_Click()
With ActiveCell
.Value = CDbl(Calendar1.Value) + .Value
.NumberFormat = "dd-mmm-yyyy hh:mm:ss"
.Select
Calendar1.Visible = False
End With
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Shane Nation]

That is great - thank you so much. I wish I knew half of what you can put
together.

Thanks

Shane