Searchable combobox in excel vba userform with time format

Joined
Sep 16, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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
1632227290635.png


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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Like this:

First, set your MatchEntry property to MatchEntryNone (2)

VBA Code:
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
 
Upvote 0
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:
VBA Code:
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:
VBA Code:
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
VBA Code:
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
 
Upvote 0
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
 
Upvote 0
What about this. This way you dont have to refer to a range

VBA Code:
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
 
Upvote 0
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
 
Upvote 0
In my last post I see that I used : With ComboBox1

It should be ComboBox10
 
Upvote 0
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:
VBA Code:
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:

1632247289285.png
 
Upvote 0
What about this. This way you dont have to refer to a range

VBA Code:
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
 
Upvote 0
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
1632302510052.png

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

but if you type 22 then
1632302814041.png

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

Thanking you
 
Upvote 0

Forum statistics

Threads
1,223,270
Messages
6,171,102
Members
452,379
Latest member
IainTru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top