Time format code not working on first cell in range

bdouglas1011

New Member
Joined
Jul 28, 2014
Messages
38
I have a range B10:B47 that I thought all was working fine but then I started from scratch and found an type mismatch error.

What the code does it allows the user to choose how he enters time (12:30 or 1230)
But when you enter time in the first cell of the range if you choose to use the colon it is ok but if you don't use a colon it gives you the type mismatch error for times entered 0001 thru 0059 after that 0100 is ok)

this only happens in cell B10 all other seems to be OK.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim EndTime As Range
Dim SBlank As String
SBlank = """" & """"
Set EndTime = Range("B10:B47")

If Target.Column <> 2 Or Target.Count > 1 Or Target.row < 10 Or Target.row > 47 Then Exit Sub

If Not Application.Intersect(EndTime, Range(Target.Address)) Is Nothing Then
    If Target.Value = "" Then
        Target.Offset(1, -1).Value = ""
        GoTo 99
    End If
    
     UserInput = Target.Value
    If UserInput > 1 Then
        NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
        Application.EnableEvents = False
        Target = NewInput
        Application.EnableEvents = True
    End If
    
    If TimeValue(Format(Target.Value, "hh:mm")) <> "12:00:00 AM" Then
        Target.Offset(1, -1).Value = "=IF(ISBLANK(B" & Target.row & ")," & SBlank & ",B" & Target.row & ")"
    End If
        
    Application.EnableEvents = True
End If
99:
End Sub
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,848
Glad to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.
 

bdouglas1011

New Member
Joined
Jul 28, 2014
Messages
38
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)



Dim EndTime As Range

Dim SBlank As String

SBlank = """" & """"

Set EndTime = Range("B10:B47")



If Target.Column <> 2 Or Target.Count > 1 Or Target.row < 10 Or Target.row > 47 Then Exit Sub



If Not Application.Intersect(EndTime, Range(Target.Address)) Is Nothing Then

    If Target.Value = "" Then

        Target.Offset(1, -1).Value = ""

        GoTo 99

    End If

 

  Dim flag As Boolean, theEntry As Double

 

    If Not (Application.Intersect(Target, Range("B10:B47")) Is Nothing) Then

        If Target.cells.Count = 1 Then

            If IsNumeric(Target.Value) Then

                theEntry = CDbl(Target.Value)



                If 0 <= theEntry And theEntry <= 2400 Then

                    Application.EnableEvents = False



                    If theEntry < 1 Then

                        flag = True

                        Target.Value = CDate(theEntry)

                    Else

                        theEntry = Int(theEntry)

                        If theEntry Mod 100 < 60 Then

                            flag = True

                            Target.Value = TimeValue(Format(theEntry, "00:00") & ":00")

                        Else

                            ' Target.NumberFormat = "general"

                        End If

                    End If



                    Application.EnableEvents = True

                End If



            End If

            If Not flag Then MsgBox "enter a time"

        End If

    End If

 

    If TimeValue(Format(Target.Value, "hh:mm")) <> "12:00:00 AM" Then

        Target.Offset(1, -1).Value = "=IF(ISBLANK(B" & Target.row & ")," & SBlank & ",B" & Target.row & ")"

    End If

    

    Application.EnableEvents = True

End If

99:
[/CODE]
 
Solution

Forum statistics

Threads
1,176,079
Messages
5,901,274
Members
434,884
Latest member
ares0027

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