Searchable combobox in excel vba userform with time format

[Vishwanath Korgaonkar]

Hi-
I have a combobox with rowsource in time format for example 10:00 AM .It has data with time at 5 minutes interval. I am looking for code with searchable combo box where if I type 10 it should drop down list starting from 10:00 AM

I have tried all the codes from different sources available on net but not able to get solution.
Kindly help to get the code
thanking you in anticipation

 

[JEC]

Like this:

First, set your MatchEntry property to MatchEntryNone (2)

Private Sub ComboBox2_Change()
  With ComboBox2
     For i = TimeValue("10:00 AM") To TimeValue("11:45 PM") Step TimeSerial(0, 5, 0)
       c00 = c00 & Format(i, "hh:mm AM/PM") & "|"
     Next
      .List = Filter(Application.Transpose(Application.Transpose(Split(c00, "|"))), .Value, -1)
      .DropDown
  End With
End Sub

 

[DanteAmor]

Here another solution, take into account the following considerations:
1. You no longer use rowsource to load the combo.
2. Adjust the sheet name and cell range where you have the times, in this line:

Set rng = Sheets("data").Range("A1:A288")   'set sheet name and time range

3. We need whatever textbox you have in your userform. Change the name on this line:

TextBox1.SetFocus       'Use any textbox in your userform

4. Because of the format you have AM / PM; If you capture 1 you will get all the hours that start with 1: 10am, 11am, 12am, 13pm, 14pm, etc.
If you enter 10, you will get 10:05 am, 10:10 am 10:15 am, etc.
If you capture 03 you will get the times of 03am and if you capture 15 you will get the times of 03pm.

5. Put all the code in your userform

Option Explicit

Dim rng As Range
Dim charging As Boolean

Private Sub ComboBox1_Change()
  Dim dato As Variant
  Dim cell As Range, c As Range
  
  If charging = True Then Exit Sub
  charging = True
  
  With ComboBox1
    dato = .Value
    .Clear
    For Each c In rng
      If Format(c.Value, "hh:mm:ss") Like dato & "*" Then
        .AddItem Format(c.Value, "hh:mm:ss AM/PM")
      End If
    Next
    .Value = dato
    TextBox1.SetFocus       'Use any textbox in your userform
    .SetFocus
    .DropDown
  End With
  
  charging = False
End Sub

Private Sub UserForm_Activate()
  Dim c As Range
  Set rng = Sheets("data").Range("A1:A288")   'set sheet name and time range
  
  charging = True
  
  With ComboBox1
    .MatchEntry = fmMatchEntryNone
    .ListRows = 12
    For Each c In rng
      .AddItem Format(c.Value, "hh:mm:ss AM/PM")
    Next
  End With
  
  charging = False
End Sub

 

[Vishwanath Korgaonkar]

I am new to excel VBA and modified the above code to suit my requirement. Time range is in work sheet "Other_Details" in column J from J4:J290.It has range from 08:30 AM to 08:30AM (24hrs) with 5 minutes interval

Dim i As Integer
Dim c00 As String
Dim sh As Worksheet
With ActiveWorkbook.Sheets("Other_Details")
Set sh = ActiveWorkbook.Sheets("Other_Details")
With ComboBox10
For i = TimeValue(sh.range("J4")) To TimeValue(sh.range("J290")) Step TimeSerial(0, 5, 0)
c00 = c00 & Format(i, "hh:mm AM/PM") & "|"
Next
.List = Filter(Application.Transpose(Application.Transpose(Split(c00, "|"))), .Value, -1)
.DropDown
End With

the code in giving error .It will be extremely usefull if you can put the full code.Thanks in advance

 

[JEC]

What about this. This way you dont have to refer to a range

Private Sub ComboBox10_Change()
  With ComboBox1
     For i = TimeValue("08:30 AM") To (TimeValue("08:30 AM") + TimeSerial(24, 5, 0)) Step TimeSerial(0, 5, 0)
       c00 = c00 & Format(i, "hh:mm AM/PM") & "|"
     Next
      .List = Filter(Application.Transpose(Application.Transpose(Split(c00, "|"))), .Value, -1)
      .DropDown
  End With
End Sub

 

[Vishwanath Korgaonkar]

Option Explicit

Dim rng As Range
Dim charging As Boolean

Private Sub ComboBox1_Change()
Dim dato As Variant
Dim cell As Range, c As Range

If charging = True Then Exit Sub
charging = True

With ComboBox1
dato = .Value
.Clear
For Each c In rng
If Format(c.Value, "hh:mm:ss") Like dato & "*" Then
.AddItem Format(c.Value, "hh:mm:ss AM/PM")
End If
Next
.Value = dato
TextBox1.SetFocus 'Use any textbox in your userform
.SetFocus
.DropDown
End With

charging = False
End Sub

Private Sub UserForm_Activate()
Dim c As Range
Set rng = Sheets("data").Range("A1:A288") 'set sheet name and time range

charging = True

With ComboBox1
.MatchEntry = fmMatchEntryNone
.ListRows = 12
For Each c In rng
.AddItem Format(c.Value, "hh:mm:ss AM/PM")
Next
End With

charging = False
End Sub



Used the above code but does not given solution with auto search feature in combobox

 

[JEC]

In my last post I see that I used : With ComboBox1

It should be ComboBox10

 

[DanteAmor]

Used the above code but does not given solution with auto search feature in combobox

Did you follow all the directions?


You must update this line
Set rng = Sheets("data").Range("A1:A288") 'set sheet name and time range

With this:
"Other_Details" in column J from J4:J290.

In this way
Set rng = Sheets("Other_Details").Range("J4:J290") 'set sheet name and time range


With ComboBox1<---You must put the name of your combobox

3. We need whatever textbox you have in your userform. Change the name on this line:

TextBox1.SetFocus 'Use any textbox in your userform

If you follow all the instructions and write 10 in the combobox1, you will see the schedules filtered:

 

[Vishwanath Korgaonkar]

What about this. This way you dont have to refer to a range

Private Sub ComboBox10_Change()
  With ComboBox1
     For i = TimeValue("08:30 AM") To (TimeValue("08:30 AM") + TimeSerial(24, 5, 0)) Step TimeSerial(0, 5, 0)
       c00 = c00 & Format(i, "hh:mm AM/PM") & "|"
     Next
      .List = Filter(Application.Transpose(Application.Transpose(Split(c00, "|"))), .Value, -1)
      .DropDown
  End With
End Sub

Private Sub ComboBox11_Change()
Dim i As Integer
Dim c00 As String
With ComboBox11
For i = TimeValue("08:30 AM") To (TimeValue("08:30 AM") + TimeSerial(24, 5, 0)) Step TimeSerial(0, 5, 0)
c00 = c00 & Format(i, "hh:mm AM/PM") & "|"
Next
.List = Filter(Application.Transpose(Application.Transpose(Split(c00, "|"))), .Value, -1)
.DropDown
End With

I tried the above code but it goes in loop and I have to exit excel

 

[Vishwanath Korgaonkar]

Did you follow all the directions?


You must update this line
Set rng = Sheets("data").Range("A1:A288") 'set sheet name and time range

With this:
"Other_Details" in column J from J4:J290.

In this way
Set rng = Sheets("Other_Details").Range("J4:J290") 'set sheet name and time range


With ComboBox1<---You must put the name of your combobox




If you follow all the instructions and write 10 in the combobox1, you will see the schedules filtered:

View attachment 47395

the code works correctly as you have stated but does not gives "PM" values

but if you type 22 then

It display "PM" codes
Is there codes which displays AM and PM when I type 10

Thanking you