Time format on userform

R

rs2k

Well-known Member

Joined

Aug 15, 2004

Messages

1,413

• Nov 4, 2005

• #2

Anyone?
Colin.

 

Upvote 0

R

rs2k

Well-known Member

Joined

Aug 15, 2004

Messages

1,413

• Nov 7, 2005

• #3

Still struggling. Can anyone please tell me why it is displaying 00:05 and not 12:00?
Any help on this would be greatfully appreciated.

Many thanks
Colin.

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Nov 7, 2005

• #4

Colin

How have you 'formatted' the combobox?

How are you populating it from the sheet?

Are you just using the times on the sheet to populate the combobox?

 

Upvote 0

R

rs2k

Well-known Member

Joined

Aug 15, 2004

Messages

1,413

• Nov 7, 2005

• #5

The row source property =Time. The named range 'Time' is formatted with[hh]:mm.

the code for the combo box is;
Private Sub cmbBookingsTimeFrom_Change()
frmBookings.cmbBookingsTimeFrom.Value = Format (cmbBookingsTimeFrom.Value, "hh:mm")
End Sub

As in the initial post, all the other times are displaying correctly, just the 12:00 time isn't

(Please excuse if I take my time, the only PC with a net connection hasn't got Excel on it and the one I am working on is in another room)

Many thanks for your time
Colin.

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Nov 7, 2005

• #6

Colin

Have you considered populating the combobox with the AddItem method?

 

Upvote 0

R

rs2k

Well-known Member

Joined

Aug 15, 2004

Messages

1,413

• Nov 7, 2005

• #7

Not sure how to do that? Could you point the way?

Colin.

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Nov 7, 2005

• #8

Perhaps something like this.

Code:

Private Sub UserForm_Initialize()
Dim H As Long
Dim M As Long
    For H = 7 To 23
        For M = 0 To 45 Step 15
            ComboBox1.AddItem Format(H, "00") & ":" & Format(M, "00")
        Next M
    Next H
End Sub

The only problem here is that the values in the combobox are text, not time values. So if you are using them in calculations you would need to convert them to time values using TimeValue.

 

Upvote 0

R

rs2k

Well-known Member

Joined

Aug 15, 2004

Messages

1,413

• Nov 7, 2005

• #9

Thanks Norie,
I will give that a try.

Colin.

 

Upvote 0

R

rs2k

Well-known Member

Joined

Aug 15, 2004

Messages

1,413

• Nov 8, 2005

• #10

Norie,
I formated the range on the WS as text, so the combo box doesn't display time values, just text, when it is copied over to the sheet where the calculations are, it is converted to time values there. Not the neatest, but it works.

Thanks for your help,

Colin.

 

Upvote 0