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

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" wrote in message
...
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

Hi Bob

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

Shane

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

That is still 1440, so two spinners or listboxes?

--
HTH

Bob Phillips

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

"Shane Nation" wrote in message
...
Hi Bob

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

Shane

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
That is still 1440, so two spinners or listboxes?

--
HTH

Bob Phillips

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

"Shane Nation" wrote in message
...
Hi Bob

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

Shane

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
That is still 1440, so two spinners or listboxes?

--
HTH

Bob Phillips

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

"Shane Nation" wrote in message
...
Hi Bob

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

Shane

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
That is still 1440, so two spinners or listboxes?

--
HTH

Bob Phillips

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

"Shane Nation" wrote in message
...
Hi Bob

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

Shane

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

"Shane Nation" wrote in message
...
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?

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

"Bob Phillips" wrote in message
...


"Shane Nation" wrote in message
...
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?

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

Shane

"Bob Phillips" wrote in message
...


"Shane Nation" wrote in message
...
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?

Sorry Bob I have not gone away yet, me being thick, I can't seem to combine
both the Calendar and the time select code in the same work book. I have
attached the two.

Any ideas?

Shane
"Bob Phillips" wrote in message
...


"Shane Nation" wrote in message
...
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?

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" wrote in message
...
Sorry Bob I have not gone away yet, me being thick, I can't seem to
combine
both the Calendar and the time select code in the same work book. I have
attached the two.

Any ideas?

Shane
"Bob Phillips" wrote in message
...


"Shane Nation" wrote in message
...
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?

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

Thanks

Shane
"Bob Phillips" wrote in message
...
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" wrote in message
...
Sorry Bob I have not gone away yet, me being thick, I can't seem to
combine
both the Calendar and the time select code in the same work book. I have
attached the two.

Any ideas?

Shane
"Bob Phillips" wrote in message
...


"Shane Nation" wrote in message
...
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?