UserForm Dropdown (time) not showing time, but shows numerical values instead

[sdoppke]

Hi everyone, did some searching to see if i could find what i am doing wrong here, to no avail.

I have a userform, the dropdowns show time (from a list), but when a time is chosen, what is left (the choice) is this long numberical value? Does anyone know of its a properties setting or I need to format the list in another way?

Thanks in advance for any help.

sd

 

[kpark91]

Hi, the reason is because Excel treats time as a number (very precise one)
So, 1 represents one day (24 hours)
so in order to get the value of one hour, it would just be = 1 / 24

Anyways, you can represent the numbers by formatting (VBA):

Format(Time, "[h]:mm:ss;@")

Replace Time with the time value.

 

[sdoppke]

Hi, the reason is because Excel treats time as a number (very precise one)
So, 1 represents one day (24 hours)
so in order to get the value of one hour, it would just be = 1 / 24

Anyways, you can represent the numbers by formatting (VBA):

Format(Time, "[h]:mm:ss;@")

Replace Time with the time value.

Got it so i insert that into my userform? Here it is:

Private Sub CommandButton1_Click()
Dim wbThis As Workbook
Dim wbThat As Workbook
Dim ws As Worksheet
Dim x As Integer
Dim y As Integer
Dim Response As VbMsgBoxResult
Dim LR As Long, o As Long
Dim rngFind As Range, rngRow As Range
  
    If Not AllComboBoxesFilled() Then
      MsgBox "Please complete the form, all boxes are required.", vbExclamation, "Update Store Hours"
    Else
      Application.ScreenUpdating = False
      Set ws = ThisWorkbook.Sheets("StoreHours")
      StoreNumber1 = Worksheets("MyStoreInfo").Range("C2")
      Set rngFind = ws.Range("C:C").Find(What:=StoreNumber1, MatchCase:=True)
      If Not rngFind Is Nothing Then
        rngFind.Offset(0, 3).Value = Me.SundayOpen.Value
        rngFind.Offset(0, 4).Value = Me.SundayClose.Value
        rngFind.Offset(0, 5).Value = Me.MondayOpen.Value
        rngFind.Offset(0, 6).Value = Me.MondayClose.Value
        rngFind.Offset(0, 7).Value = Me.TuesdayOpen.Value
        rngFind.Offset(0, 8).Value = Me.TuesdayClose.Value
        rngFind.Offset(0, 9).Value = Me.WednesdayOpen.Value
        rngFind.Offset(0, 10).Value = Me.WednesdayClose.Value
        rngFind.Offset(0, 11).Value = Me.ThursdayOpen.Value
        rngFind.Offset(0, 12).Value = Me.ThursdayClose.Value
        rngFind.Offset(0, 13).Value = Me.FridayOpen.Value
        rngFind.Offset(0, 14).Value = Me.FridayClose.Value
        rngFind.Offset(0, 15).Value = Me.SaturdayOpen.Value
        rngFind.Offset(0, 16).Value = Me.SaturdayClose.Value
        rngFind.Offset(0, 17).Value = Me.Month.Value
        rngFind.Offset(0, 18).Value = Me.Year.Value
        MsgBox "Store Hours have been updated", vbInformation, "Update Store Hours"
        Application.ScreenUpdating = True
        StoreHours.Hide

I trimmed the bottom off, because it is very long.

sd

 

[kpark91]

Yup.
So, I think it'll be something like this (not too sure since I don't see your userform.)

rngFind.Offset(0, 3).Value = Format$(Me.SundayOpen.Value, "[h]:mm:ss;@")

If this doesn't work, it's likely that excel is not converting the string to numbers for us so use

rngFind.Offset(0, 3).Value = Format$(CDbl(Me.SundayOpen.Value), "[h]:mm:ss;@")

If this doesn't work, you would need to use string manipulation instead, which is a pain in the ****.

 

[sdoppke]

Yup.
So, I think it'll be something like this (not too sure since I don't see your userform.)

rngFind.Offset(0, 3).Value = Format$(Me.SundayOpen.Value, "[h]:mm:ss;@")

If this doesn't work, it's likely that excel is not converting the string to numbers for us so use

rngFind.Offset(0, 3).Value = Format$(CDbl(Me.SundayOpen.Value), "[h]:mm:ss;@")

If this doesn't work, you would need to use string manipulation instead, which is a pain in the ****.

Unfortunatley neither worked. i will try to learn about "string manipulation"

sd

 

[sdoppke]

Yup.
So, I think it'll be something like this (not too sure since I don't see your userform.)

rngFind.Offset(0, 3).Value = Format$(Me.SundayOpen.Value, "[h]:mm:ss;@")

If this doesn't work, it's likely that excel is not converting the string to numbers for us so use

rngFind.Offset(0, 3).Value = Format$(CDbl(Me.SundayOpen.Value), "[h]:mm:ss;@")

If this doesn't work, you would need to use string manipulation instead, which is a pain in the ****.

Ya know after thinking about it,i dont think i explained well enough. The value that is being input to the sheet i want it to, is correct the problem is the dropdown itself (its a picky visual thing) when i choose 10:00 (which looks fine at the time) it then displays the long numerical value. When i complete the userform and have it do its job, all works out.

sd

 

[Weaver]

I thought that might be the problem.

Say your list source is sheet10!A1:A24, then in B1, put the formula

=text(A1,"hh:mm")

and copy down.

then make the source range sheet10!A1:B24, set column count to 2 and column widths to 0,10

Then the choice will display correctly and so long as you keep 'bound column' as 1 then the correct decimal value is stored in the combobox.

 

[Norie]

How are you populating the listbox/combobox?

There are various ways you can get it to display times, but it depends on that really.

For example if you have values formatted as time in A2:A10 you can try this:

For I = 2 To 10
       Combobox1.AddItem Range("A" & I).Text
Next I

Using Text ensures the format is taken from the worksheet.

 

[sdoppke]

I thought that might be the problem.

Say your list source is sheet10!A1:A24, then in B1, put the formula

=text(A1,"hh:mm")

and copy down.

then make the source range sheet10!A1:B24, set column count to 2 and column widths to 0,10

Then the choice will display correctly and so long as you keep 'bound column' as 1 then the correct decimal value is stored in the combobox.

Weaver you rock!! Does it have to be military time?

 

[sdoppke]

Weaver you rock!! Does it have to be military time?

I figured it out hh:mm Am/PM