Error if cancel or close input box. I want to exit sub.

I am a completed vba newbie so any help or advice is appreciated!

I have some code that goes; users double click cell > input box appears asking for serial number > serial number is found on database sheet and cell that was double clicked is copied over. However, if the user clicks cancel or [x] I get a runtime error (I guess because there is no serial number to find in the database). I want to Exit Sub if the user clicks cancel or [x] or if the user leaves the input box blank and presses okay I want a message box to say "invalid entry." But I'm not sure how to incorporate this into my existing code;

PHP Code: 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("I11:I2010")) Is Nothing Then
        If ActiveCell.Value = ChrW(&H2713) Then
            ActiveCell.ClearContents
            Dim SerialNo As Integer
            SerialNo = InputBox("Please enter Serial #:")
            With Sheets("Lost Property Log")
            .Unprotect
            fRow = Application.Match(SerialNo, .Columns(3), 0)
            If Not IsError(fRow) Then
                .Cells(fRow, 9).Value = Cells(Target.Row, 9).Value
            End If
            .Protect
        End With
        Else
        SerialNo = InputBox("Please enter Serial #:")
        ActiveCell.Value = ChrW(&H2713)
        End If
        Cancel = True
        With Sheets("Lost Property Log")
            .Unprotect
            fRow = Application.Match(SerialNo, .Columns(3), 0)
            If Not IsError(fRow) Then
                .Cells(fRow, 9).Value = Cells(Target.Row, 9).Value
            End If
            .Protect
        End With
    End If
End Sub 


Anyone have any ideas?

I have attached my workbook so you can see what I'm referring to.

Thank you!

So the variable SerialNo must be defined as Variant then you could use If SerialNo = "" Then Exit Sub …

Originally Posted by Marc L

So the variable SerialNo must be defined as Variant then you could use If SerialNo = "" Then Exit Sub …

Thanks for that! I managed to corporate that in and it works. Although, I've realised it might be better if the code said something like if blank or inputted number is not found then message box and then end sub. Any ideas on how I'd do that?

If you use the application input box then you could test for a cancel or X click as follows:

Sub Test()
i = Application.InputBox("Enter number")
if i = False then
   MsgBox "User cancelled"
elseif len(i) = 0 then
   Msgbox "Nothing entered"
else
   MsgBox "Something entered"
End If
End Sub

If you don't want to use the application inputbox you can achieve the same as follows:

Sub Test2()
i = InputBox("Enter number")
if (StrPtr(i) = 0&) then
   MsgBox "User cancelled"
elseif len(i) = 0 then
   Msgbox "Nothing entered"
else
   MsgBox "Something entered"
End If
End Sub

Thanks ByteMarks! Definitely what has to be done but I've managed to make a complete mess of it when incorporating it into my existing code;

PHP Code: 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("I11:I2010")) Is Nothing Then
        
        
        'When item is not marked as collected
        If ActiveCell.Value = ChrW(&H2713) Then
            
           SerialNo = Application.InputBox("Please enter Serial #:")
           If SerialNo = False Then
           MsgBox "Please enter Serial # to mark this item as not collected"
           ElseIf Len(i) = 0 Then
           MsgBox "Invalid Serial #"
           Else
           With Sheets("Lost Property Log")
           .Unprotect
           fRow = Application.Match(SerialNo, .Columns(3), 0)
           If Not IsError(fRow) Then
           .Cells(fRow, 9).Value = Cells(Target.Row, 9).Value
            ActiveCell.ClearContents
            .Protect
            End With
            End If
            
            
            

         'When item is marked as collected
           Else
           SerialNo = Application.InputBox("Please enter Serial #:")
           If SerialNo = False Then
           MsgBox "Please enter Serial # to mark this item as collected"
           ElseIf Len(i) = 0 Then
           MsgBox "Invalid Serial #"
           Else
           With Sheets("Lost Property Log")
            .Unprotect
            fRow = Application.Match(SerialNo, .Columns(3), 0)
            If Not IsError(fRow) Then
            .Cells(fRow, 9).Value = Cells(Target.Row, 9).Value
            ActiveCell.Value = ChrW(&H2713)
            .Protect
            End With
            End If
        
        
        
        
    End If 


So what I was hoping the code says is:

User double clicks cell >

1. If the cell is currently blank then serial number box appears and user enters serial number (if user clicks cancel or x or leaves blank then appropriate message appears and sub is ended) > serial number is found on LostPropertyLog and cell on LostPropertyLog and active cell get ticked.

2. If the cell has a tick already then serial number box appears and user enters serial number (if user clicks cancel or x or leaves blank then appropriate message appears and sub is ended) > serial number is found on LostPropertyLog and cell on LostPropertyLog and active cell get cleared.

But I'm getting 'End if without Block If' errors and I can't work out where I'm missing my Ifs/End Ifs.


I also need to add a bit that says if the serial number entered is not found on the LostPropertyLog then it is invalid, a tick is neither added or cleared and a message box appears.

Can you help? Thank you!

Hi I'm a bit confused as to which cells should be ticked/unticked.

For instance, in the log sheet, row 11 has a serial number of 006 and row 15 has a serial number of 0064.
If i double-click row 11 and enter 0064 which row should have the tick be assessed on?

You say the active cell so, based on that, row 11 would be ticked/unticked as a result of finding 0064 or row 15?

Hi think this should help. You can change which cell gets ticked

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim SerialNo As String 'declare as string
If Not Intersect(Target, Range("I11:I2010")) Is Nothing Then
    'with application inputbox, use could use a type of 1 to ensure a number
    'but this would overflow if a number over 32768 was entered.
    SerialNo = Application.InputBox("Please enter Serial")
    If SerialNo = False Then
        'user cancelled
    ElseIf Not IsNumeric(SerialNo) Or SerialNo > 32768 Then
        'invaid number
        MsgBox "Enter a valid number"
    Else
        With Sheets("Lost Property Log")
            'search log
            .Unprotect
            'change the serialNo to integer for the match
            fRow = Application.Match(CInt(SerialNo), .Columns(3), 0)
            If Not IsError(fRow) Then
                'add the value
                .Cells(fRow, 9).Value = Cells(Target.Row, 9).Value
                'set the tick
                If ActiveCell.Value = ChrW(&H2713) Then
                    ActiveCell = ""
                Else
                    ActiveCell.Value = ChrW(&H2713)
                End If
            Else
                'not found in log
                MsgBox "Serial # not found in log"
            End If
            .Protect
        End With
    End If
End If
End Sub

Sorry it's confusing! When the user clicks an item on the 'Wanted Items' sheet a link needs to be made with an item on the Lost Property Log.

So. The user double clicks whatever row in the 'Wanted Items' sheet (in the 'Collected' column) to add a tick and mark it as collected.
Screen Shot 2018-07-23 at 12.07.46.png
Then, the input box appears asking the user to attach the row to a pre-existing serial number. In this image I've typed in the serial no '0006'.
Screen Shot 2018-07-23 at 12.12.47.png
This will then find the row with the serial no '0006' on the 'Lost Property Log' sheet and mark it's own 'Collected' column with a tick.
Screen Shot 2018-07-23 at 12.15.59.png
AS WELL AS marking the row back on the 'Wanted Items' sheet with a tick.
Screen Shot 2018-07-23 at 12.16.55.png

And the same process happens when it comes to unticking an item.

Does that make more sense?

I've managed to fix all the If/End If errors in the following code but now the find serial no and mark it feature isn't working;

PHP Code: 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("I11:I2010")) Is Nothing Then
    
    
        If ActiveCell.Value = ChrW(&H2713) Then
            
            SerialNo = InputBox("Please enter Serial #:")
            If (StrPtr(SerialNo) = 0&) Then
            MsgBox "Please enter Serial # to unmark this item as collected"
            ElseIf Len(SerialNo) = 0 Then
            MsgBox "Invalid Serial #"
            Else
            ActiveCell.ClearContents
            Cancel = True
            With Sheets("Lost Property Log")
            .Unprotect
            fRow = Application.Match(SerialNo, .Columns(3), 0)
            If Not IsError(fRow) Then
            .Cells(fRow, 9).Value = Cells(Target.Row, 9).Value
            End If
            .Protect
            End With
            End If
        
        
        Else
        
            SerialNo = InputBox("Please enter Serial #:")
            If (StrPtr(SerialNo) = 0&) Then
            MsgBox "Please enter Serial # to unmark this item as collected"
            ElseIf Len(SerialNo) = 0 Then
            MsgBox "Invalid Serial #"
            Else
            ActiveCell.Value = ChrW(&H2713)
            With Sheets("Lost Property Log")
            .Unprotect
            fRow = Application.Match(SerialNo, .Columns(3), 0)
            If Not IsError(fRow) Then
            .Cells(fRow, 9).Value = Cells(Target.Row, 9).Value
            End If
            .Protect
            End With
            End If
        
        End If
        End If

End Sub 


Thank you!! I've tweaked it (needed to add/remove the ticks before copying the row) and it does exactly what I want. However, when you leave the input box blank and press OK I get a type mismatch error for the line 'If SerialNo = False Then.' Can you help please? Should be a simple fix but I can't work it out.

PHP Code: 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim SerialNo As String 'declare as string
If Not Intersect(Target, Range("I11:I2010")) Is Nothing Then
    'with application inputbox, use could use a type of 1 to ensure a number
    'but this would overflow if a number over 32768 was entered.
    SerialNo = Application.InputBox("Please enter Serial #:", "Please enter Serial #:")
    If SerialNo = False Then
        'user cancelled
        MsgBox "Please enter a valid Serial #"
    ElseIf Not IsNumeric(SerialNo) Or SerialNo > 9999 Then
        'invaid number
        MsgBox "Please enter a valid Serial #"
    Else
        With Sheets("Lost Property Log")
            'search log
            .Unprotect
            'change the serialNo to integer for the match
            fRow = Application.Match(CInt(SerialNo), .Columns(3), 0)
            If Not IsError(fRow) Then
                'add the value
                If ActiveCell.Value = ChrW(&H2713) Then
                    ActiveCell = ""
                    .Cells(fRow, 9).Value = Cells(Target.Row, 9).Value
                Else
                    ActiveCell.Value = ChrW(&H2713)
                .Cells(fRow, 9).Value = Cells(Target.Row, 9).Value
                End If
            Else
                'not found in log
                MsgBox "Please enter a valid Serial #"
            End If
            .Protect
        End With
    End If
End If
End Sub 


Since

Dim SerialNo As String 'declare as string

It should be

    If SerialNo = "False" Then

Thanks! That solves that line. But then I get a mismatch error for the line 'ElseIf Not IsNumeric(SerialNo) Or SerialNo > 9999 Then'

You can not compare string vs numerical

    ElseIf IsNumeric(SerialNo) Then
        If SerialNo > 9999 Then
            'invaid number
            MsgBox "Please enter a valid Serial #"
        End If
    ElseIf Not IsNumeric(SerialNo) Then
        MsgBox "Please enter a valid Serial #"
    Else

Originally Posted by jindon

You can not compare string vs numerical

    ElseIf IsNumeric(SerialNo) Then
        If SerialNo > 9999 Then
            'invaid number
            MsgBox "Please enter a valid Serial #"
        End If
    ElseIf Not IsNumeric(SerialNo) Then
        MsgBox "Please enter a valid Serial #"
    Else

Thanks! That makes sense to me and fixes all those errors but now the rest of the sub doesn’t complete as it should if the number is valid

Other way around

    ElseIf Not IsNumeric(SerialNo) Then
        MsgBox "Please enter a valid Serial #"
    ElseIf IsNumeric(SerialNo) Then
        If SerialNo > 9999 Then
            'invaid number
            MsgBox "Please enter a valid Serial #"
        Else
            With Sheets("Lost Property Log")
            '
            '
            End With
        End If
    End If

Thank you so much! All working perfectly now!