Message box/UserForm with lookup

[Dave H9]

Hi all, I am a novice with VBA so any help is much appreciated.

I have attached an example workbook. I am trying to set this up so that when the book is opened a message box appears asking the user if they have a reference number (YES or NO answer), if NO then in the sheet "Input Sheet" cell B2 should read "Not Applicable". If the answer is YES a second message box should request the user type their reference and check this against the sheet "Request Register" column A, if it exists then input into the sheet "Input Sheet" cell B2, if not retry.

I know this is probably easy for you pro's but I am struggling to even make a start.

Thank you

[Richard Buttrey]

Hi Dave,

Rather than try and re-invent the wheel with a userform and command buttons, just use the standard Message and Input boxes.

Call the following procedure from your workbook open event.

Sub GetReference()
    Dim st As String, lRow As Long
    If MsgBox("Do you have a reference", vbYesNo, Title:="Before you begin") = vbNo Then
        Sheet1.Range("B2") = "Not Applicable"
    Else
        st = InputBox("Please enter your reference")
        On Error Resume Next
        lRow = Sheet2.Range("A:A").Find(What:=st).Row
        If lRow = 0 Then
            MsgBox "Please re-enter your reference"
            End
        Else
            Sheet1.Range("B2") = st
        End If
    End If
End Sub

Regards

[Dave H9]

Hi Richard,

Thank you very much, this seems a much simpler approach. I am now trying to include a loop so only a valid reference or N/A is returned in cell B2.

I'll give it a go myself but any pointers would be appreciated.

Thanks again

[snb]

For a simpler approach see this file.

[Richard Buttrey]

Hi,

I like snb's approach of forcing the user to pick from a list, but if you want to use the MsgBox / Input Box route this simpler modified procedure will do what you want.

Sub GetReference()
    Dim st As String, lRow As Long
    st = InputBox("Please enter your reference", Title:="Before you begin")
    On Error Resume Next
    lRow = Sheet2.Range("A:A").Find(What:=st).Row
    If lRow = 0 Then
        Sheet1.Range("B2") = "#N/A"
    Else
        Sheet1.Range("B2") = st
    End If
End Sub

Regards

[Dave H9]

Thanks snb but I don't think this suits my application.

I am trying to get the message to loop if an invalid reference is used, the code I am using is as follows

Sub GetReference()
    Dim st As String, lRow As Long
    If MsgBox("Do you have a reference", vbYesNo, Title:="Before you begin") = vbNo Then
        Sheet1.Range("B2") = "Not Applicable"
    Else
        st = InputBox("Please enter your reference") 'this section can be exited by closing or cancelling
        On Error Resume Next
        lRow = Sheet2.Range("A:A").Find(What:=st).Row
        If lRow = 0 Then
            Do
                st = InputBox("REFERENCE NOT FOUND - Please re-enter")
                On Error Resume Next
                lRow = Sheet2.Range("A:A").Find(What:=st).Row
                Sheet1.Range("B2") = st
            Loop Until lRow > 0    ' Exit outer loop immediately.
            Sheet1.Range("B2") = "Not Applicable"
        Else
            Sheet1.Range("B2") = st
        End If
    End If
       
End Sub

I am probably missing the obvious but I have a few bugs I could do with sorting
1. The first inputbox can be closed or cancelled by the user. I managed to have the input box in the loop return "Not Applicable" if it is cancelled or closed but can't get it to work on the first.
2. Either of the input boxes will accept a value of "0". I'm sure this is due to the "lRow" element but I cannot find a workround.

Any suggestions please?

[Dave H9]

Thanks, I am trying to develop as part of a userform but at the moment I need to address the two bugs. I can then use a modified version to include in a userform.

Thank you