Omit past timing of combo box 2 from combo box 1

xboon_95

Board Regular
Joined
Jun 18, 2014
Messages
53
Hi all, I'm trying to omit past timings of combo box 2 from combo box 1 and display it. My codes are shown below;
Code:
Private Sub ComboBox1_Change()
ComboBox1.TextAlign = fmTextAlignCenter
 Dim TheSheet As Worksheet
    Dim SearchRange As Range
    Dim FindRow As Range
    Set SearchRange = Range("B1", Range("B65536").End(xlUp))
    
    Set TheSheet = ActiveSheet
     
    On Error Resume Next
    With TheSheet
    
         Set FindRow = SearchRange.Find(Me.ComboBox1, LookIn:=xlValues, LookAt:=xlWhole)
         row_review1 = FindRow.Row
        
        If row_review1 > 0 Then
            .Cells(1, 24) = row_review1
                         row_review1 = vbNullString
        Else
            MsgBox Me.ComboBox1 & " cannot be found"
        End If
    End With
    On Error GoTo 0
   
End Sub


Private Sub ComboBox2_Change()
ComboBox2.TextAlign = fmTextAlignCenter
Dim TheSheet As Worksheet
    Dim SearchRange As Range
    Dim FindRow As Range
    Set SearchRange = Range("B1", Range("B65536").End(xlUp))
    
   Set TheSheet = ActiveSheet
     
    On Error Resume Next
    With TheSheet


        Set FindRow = SearchRange.Find(Me.ComboBox2, LookIn:=xlValues, LookAt:=xlWhole)
         row_review2 = FindRow.Row
         
        If row_review2 > 0 Then
         
          .Cells(2, 24) = row_review2
             row_review2 = vbNullString
        Else
            MsgBox Me.ComboBox2 & " cannot be found"
        End If
    End With
    On Error GoTo 0


End Sub

I'm trying to get the timing and display it onto the combo boxes but however, the timing of combo box 2 will minus off from combo box 1. For instance, the timing are from 00:00:01 to 00:00:10. If the user selects 00:00:03 from combo box 1, combo box 2 will only display 00:00:04 to 00:00:10. Is this possible? Any help is appreciated, thank you.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

xboon_95

Board Regular
Joined
Jun 18, 2014
Messages
53
How are you filling your ComboBoxes?

Sir, there will be a command button to load the timings first before having the user to choose the desired time from the combo box. The command button code is as follows;
Code:
Private Sub CommandButton8_Click()
On Error GoTo Errhandler
Dim SearchRange As Range
    Dim FindRow As Range
    Set SearchRange = Range("B1", Range("B65536").End(xlUp))
    Set FindRow = SearchRange.Find(" 00:00:01", LookIn:=xlValues, LookAt:=xlWhole)


row_review = FindRow.Row


    Dim TheSheet As Worksheet
    Set TheSheet = ActiveSheet
    
    Do
    DoEvents
    row_review = row_review + 1
    item_in_review = TheSheet.Range("B" & row_review)
    
    If Len(item_in_review) > 0 Then ComboBox1.AddItem (item_in_review)
    If Len(item_in_review) > 0 Then ComboBox2.AddItem (item_in_review)
    
    Loop Until item_in_review = ""
    
    MsgBox "Complete"
    
    Exit Sub
    
Errhandler:


MsgBox "Please kindly load your text file."
End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I knocked up this short example that seems to work and that you may be able to adapt to your scenario:

Code:
Private Sub UserForm_Initialize()
    With ComboBox1
        .AddItem "00:00:01"
        .AddItem "00:00:02"
        .AddItem "00:00:03"
        .AddItem "00:00:04"
        .AddItem "00:00:05"
        .AddItem "00:00:06"
        .AddItem "00:00:07"
        .AddItem "00:00:08"
        .AddItem "00:00:09"
        .AddItem "00:00:10"
        ComboBox2.List = .List
    End With
End Sub

Private Sub ComboBox1_Change()
    Dim r As Long
    With ComboBox2
        For r = .ListCount - 1 To 0 Step -1
            If TimeValue(.List(r)) <= TimeValue(ComboBox1.Value) Then .RemoveItem (r)
        Next r
    End With
End Sub
 

xboon_95

Board Regular
Joined
Jun 18, 2014
Messages
53
I knocked up this short example that seems to work and that you may be able to adapt to your scenario:

Code:
Private Sub UserForm_Initialize()
    With ComboBox1
        .AddItem "00:00:01"
        .AddItem "00:00:02"
        .AddItem "00:00:03"
        .AddItem "00:00:04"
        .AddItem "00:00:05"
        .AddItem "00:00:06"
        .AddItem "00:00:07"
        .AddItem "00:00:08"
        .AddItem "00:00:09"
        .AddItem "00:00:10"
        ComboBox2.List = .List
    End With
End Sub

Private Sub ComboBox1_Change()
    Dim r As Long
    With ComboBox2
        For r = .ListCount - 1 To 0 Step -1
            If TimeValue(.List(r)) <= TimeValue(ComboBox1.Value) Then .RemoveItem (r)
        Next r
    End With
End Sub

Sir, the codes are able to work but the timing is not from 00:00:01 to 00:00:10 only. My data is usually 2-3 hours long and it might reach up to 7 hours. Are there any other solutions to this? Thank you.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
It doesn't matter how may items are in the list. Did you try the ComboBox code I posted?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,837
Messages
5,598,378
Members
414,234
Latest member
grlevesq

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
Top