Private Sub ComboBox1_Change()
    Static Blocked
    If Blocked Then Exit Sub
    Blocked = True
    Select Case ComboBox1.Value
    Case "Current Day"
        For Each sht In ThisWorkbook.Sheets
            If sht.Range("O2").Value = Date Then
                sht.Select
                Exit For
            End If
        Next sht
    Case Else
        Sheets(ComboBox1.Value).Select
    End Select
    Me.ComboBox1.Value = "" ' or "Choose Sheet" or…
    Blocked = False
End Sub