Time format in text box combo box [SOLVED]

[Flowerski]

Dear Sirs
Happy New Year. I have a problem with maintaining the proper time format displayed in active X controls. Operator has to pick time from the range("timeinspect") on a spreadsheet. Range("timeisnpect") has numbers representing time values every 30 minutes, 7:30, 8:00, 8:30 etc. All is time formated (option "13:30"; what in code is .NumberFormat"h:mm;@" )). The Combo box property, Row Source = range("timeinspect"). The values in combo box and in text box are working fine for every chosen time excpet 6am (it dispalays as 0.25) and 12 pm (It displays as 0.05). So the 6 am somehow could represent quarter of the day but why 12 pm is 0.05 ? The time 18:00 is shown fine in boxes. All other times are displayed correctly only 6am and 12pm. Any idea what is going on??

 

[chirayu]

Sample file please

 

[Flowerski]

Thanks for respond. Attached is file which hopefully is self explained. The issue is written on the spreadsheet. When choosing 6:00 from drop down list the result in text box and in range named "timecell" = 0.25, when chosen time is 12:00, the result =0.05, all the rest time work fine. I have no idea why. Help please. Thanks
F

 

[NARAYANK991]

Hi ,

Use the following code :

Private Sub ComboBox1_Change()
            Application.EnableEvents = False
            ComboBox1.Text = Application.WorksheetFunction.Text(ComboBox1.Value, "hh:mm")
            TextBox1.Text = Application.WorksheetFunction.Text(ComboBox1.Value, "hh:mm")
            Application.EnableEvents = True
End Sub

Narayan

 

[Debaser]

You could also remove the Rowsource and replace the code with:

Private Sub ComboBox1_Change()

TextBox1.Value = ComboBox1.Value
End Sub
Private Sub UserForm_Initialize()
    Me.ComboBox1.List = Evaluate("IF(ROW(times),text(times,""h:mm""))")
End Sub

Private Sub UserForm_Terminate()
Range("timecell").Value = ComboBox1.Value
Range("timecell").NumberFormat = "h:mm"

End Sub

 

[Flowerski]

Thanks for suggestion. It will take some time till I try it. Extremely busy now. Thanks , I will get back to you. Thanks

 

[chirayu]

attached

 

[Flowerski]

Dear Chirayu, your solution is simply genius. I love it, you are great. So officially case is solved and closed. I will use your solution, but can you explain why it did not work the way I started? I would be very interested. If I would like to enter 24 hour time spread every five minute, then your method would be too complex. Any clue ?

 

[Flowerski]

Hi ,

Use the following code :

Private Sub ComboBox1_Change()
            Application.EnableEvents = False
            ComboBox1.Text = Application.WorksheetFunction.Text(ComboBox1.Value, "hh:mm")
            TextBox1.Text = Application.WorksheetFunction.Text(ComboBox1.Value, "hh:mm")
            Application.EnableEvents = True
End Sub

Narayan

Dear Narayank991, I also received solution from Chirayu, please see the end of my post. His solutions is simply and clear although it did not explain why my method did not work. If you check the code, he entered value for every 30 minuts in ..comobobx1.change. So If I would need 24 h time spread every five minutes it would be too complex to use this method. If you have some time , you are also welcome to have a look inside. Thanks F

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[chirayu]

Change this:

INTERVAL = TimeValue("00:00:05")

to this:

INTERVAL = TimeValue("00:05:00")

In the file Narayank uploaded, to get 5 minute intervals

 

[Flowerski]

Dear Naraynak991 . I love your solutions. It is real great. I have to study it so I can use it in other situations. You guys are good and there is still a lot for me to learn. THanks. This was my first post in Chandoo and now I can officially recommend you to others, you guys are great. Thanks a lot.

 

[Flowerski]

should I mark that case is solved, other forums require notification when case is finished. I am not sure how it is here at Chandoo. Thanks again.