Input Box prompts Message Box

**cfherd** · 05-04-2012, 07:07 AM

Hi,

I am writing some (fairly basic) code where a user inputs a number to an input box which is then copied to a cell but I am trying to add a little extra functionality whereby if the value input results in an error i.e. the details related to that number are 'not found' (by a vlookup with an if in a different cell) then an message box is displayed with "Not Found". I've had a bash with little success but I think it's mainly because I don't know where to place the lines in my code.

Hope you can help.

Thanks

Sub New_Search()

    Dim strSearch As String
    
    strSearch = InputBox("Enter Agreement No.", "New Search")
      
    If strSearch <> "" Then Sheets("Sold Lookup").Range("E5").Value = strSearch
    If strSearch = "" Then Sheets("Sold Lookup").Range("E5").Value = ""
    
End Sub

**arlu1201** · 05-04-2012, 08:06 AM

Do you have to compare your inputbox value to just 1 cell or multiple cells?

**cfherd** · 05-04-2012, 08:15 AM

Just 1 cell, in this case If E17 = "Not Found" then I want to display the message box but only where cell E5 is populated. I Think I need an Else If but could be wrong!

**arlu1201** · 05-04-2012, 08:20 AM

So you mean that if the value provided by the user in the inputbox does not match A17, a message box should be displayed?

**cfherd** · 05-04-2012, 08:33 AM

I am probably not explaining it well, sorry!

A user inputs a number via the Input Box which is then put into cell E5. There is then a VLookup from that cell value to display related data in other cells. What I want to do is where the Vlookup returns a #N/A (though I've used IFERROR so it would return "Not Found") a message box is displayed stating "Not Found". Hope that makes sense.

Only other situation is that where the user enters nothing by just clicking ok (with out type in a number) or clicks cancel, I want cell E5 to be blank, hence the IF strSearch = "".

Thanks for your help so far!

**cfherd** · 05-04-2012, 09:46 AM

Think I've cracked it, probably better ways of coding it but here's what I've done and it seems to work.

Sub New_Search()

    Sheets("Sold Lookup").Range("E5").Value = ""
    
    Dim strSearch As String
    Dim strAgreement As String
    Dim strPurchaser As String
    
    strSearch = InputBox("Enter Agreement No.", "New Search")
      
    If strSearch <> "" Then
        Sheets("Sold Lookup").Range("E5").Value = strSearch
    End If
    
    If strSearch = "" Then
        Sheets("Sold Lookup").Range("E5").Value = ""
    End If
    
    strAgreement = Sheets("Sold Lookup").Range("E5").Value
    strPurchaser = Sheets("Sold Lookup").Range("E17").Value
    
    If strAgreement <> "" And strPurchaser = "Not Found" Then
        MsgBox ("Not Found")
        Sheets("Sold Lookup").Range("E5").Value = ""
    End If
    
End Sub