Time format for userform and spreadsheet

[newbieX]

I have a userform (Userform1) that has a textbox (TextBox2) that is a time field ideally formatted to hh/mm am/pm. It is stored in a sheet (“Schedule”) in ColumnB.

I need to format both the data in the sheet and the userform.

I have a function that when the user clicks a button, the data from the userform will be added to appropriate row (varRow) in the Schedule spreadsheet. I also have a function that loads the selected row data (varRow) from the spreadsheet into the form. These need the time data to be formatted as a time hh mm am/pm for both loading into the userform from the spreadsheet and exporting from the userform into the spreadsheet.

Oh and just to complicated matters more, I also load data from a different spreadsheet into a listbox on UserForm1. The time would need to be formatted here too. I included code here because I suspect I just need to tweek the following line (ListBox1.List(x, 1) = Cells(lr, "B") a bit

deg2 = TextBox1.value
Sheets("Classes").Activate
For lr = 2 To Cells(65536, "A").End(xlUp).Row
    Set deg1 = Cells(lr, "A")
    If deg1 = deg2 Then
        ListBox1.AddItem
        ListBox1.List(x, 0) = Cells(lr, "A")
        ListBox1.List(x, 1) = Cells(lr, "B")
        ListBox1.List(x, 2) = Cells(lr, "C")
        ListBox1.List(x, 3) = Cells(lr, "D")
        ListBox1.List(x, 4) = Cells(lr, "E")
        ListBox1.List(x, 5) = Cells(lr, "F")
        ListBox1.List(x, 6) = lr
        x = x + 1
    End If: Next

I just can’t seem to get anything I have tried to work. Ideas anyone?

I hate date/times. They are a constant headache.

As usual, many thanks to all the helpful Excel VBA gurus.

 

[mikerickson]

Try ListBox1.List(x, 1) = Format(Cells(lr,"b"), "hh:mm AM/PM")

 

[newbieX]

That took care of the listbox issue (thank you very much) but still struggling with other issues cited in post (basically the first 3 paragraphs). Too bad there isn't a format property for the userform textboxes.

 

[mikerickson]

If the cell is formatted hh:mm am/pm then just putting the .List value into the cell might trigger Excel's automatic type conversation.

Otherwise, (but probably should start with) code like this should be used. (The cell should be formatted beforehand for time with the format you desire)

ActiveCell.Value = TimeValue(ListBox1.List(2, 1))

 

[newbieX]

I think I may be able to figure the rest out given the above example. If not, I will repost. Thanks again.

 

[mikerickson]

The thing to remember is that userform control have strings. When putting strings that represent date/times or numbers into cells, one has to explicitly convert those strings into date/times or numbers.

Such conversion is not always strictly necessary, since Excel's automatic type conversion often kicks in. But making conversion your habit will prevent your code from bad surprises when that auto-coversion doesn't kick in.