Search value the prompt for user form with three options, place in cell afterwards

[Caveman1964]

Hi,
I want to click macro.
it searches sheet3 A:A for a value. If it doesnt find it, message just says value not present. If it finds it, it pulls up a userform named "goahead", it consists of three choices yes, no, n/a.
whichever one is selected, it places that in sheet3 column z for that particular row. I have a mess with it and starting over, couldn't get to work.
any help is appreciated.
Thanks!

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Caveman1964]

Thank you for the response!....unfortunately my work computer has access blocked to the file sharing. I'll try to explain better here.
I click a button that has a text "Return Product"
Prompt asks for Lot number.
It searches A:A for lot number
If it doesn't find it says it doesnt exists and exits sub
if it does, an input box of options shows up with YES, NO, or N/A.
User clicks one of those options and it places it in row of column D.
Then exits or prompts that the users values was placed.
The button will be on sheet 2 that user clicks, the table below that it searches is sheet 3 and places value in sheet 3 without user going to sheet 3
Sorry I couldn't get file on there and if this example doesn't help i definitely appreciate you trying.
Thanks!!!!

[TABLE="width: 619"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Lot Number[/TD]
[TD]Product[/TD]
[TD]Problem Code[/TD]
[TD]Will we cancel delivery?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]a[/TD]
[TD]S47[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]b[/TD]
[TD]P68[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]c[/TD]
[TD]S45[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]d[/TD]
[TD]S45[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD]e[/TD]
[TD]P68[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6[/TD]
[TD]f[/TD]
[TD]P91[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

By "input box" do you mean the userform named "goahead"? If so, are the options YES, NO and N/A assigned to option buttons on the userform? Is the prompt that asks for a Lot Number in the form of an inputbox or is it a textbox on the userform?

 

[Caveman1964]

yes I did mean that but I don't have to use that userform if a simple excel prompt can have those options. Excel prompt would be easier for me.
The userform is basic right now with no code, only the radio buttons with the Yes, No, N/A
no the prompt for asking for lot number is not in that form.
I am a novice with not much sense about this stuff....lol.....as you see. I think I confused the whole thing by trying to create a userform. excel prompt may be simpler.
Thanks!

 

[Fluff]

Would a Yes, No, Cancel prompt be acceptable, or do you want the prompt to have N/A

 

[Caveman1964]

yes I think I could make that work!

 

[mumps]

Place this macro in a standard module and assign it to the button on Sheet2.

Sub SearchVal()
    UserForm1.Show
End Sub

You will need a userform with 3 option buttons (YES, NO and N/A) and a commandbutton. Place this macro in the commandbutton code module:

Private Sub CommandButton1_Click()
    Dim i As Long, response As String, val As Range, oCtl As MSForms.Control
    response = InputBox("Please enter a value to search.")
    If response = "" Then
        Unload UserForm1
        Exit Sub
    End If
    Set val = Sheets("Sheet3").Range("A:A").Find(response, LookIn:=xlValues, lookat:=xlWhole)
    For Each oCtl In Me.Controls
        If oCtl.Value = True Then val.Offset(0, 3) = oCtl.Caption
    Next oCtl
    Unload UserForm1
End Sub

Click a button and then click the commanbutton.

 

[Caveman1964]

Works great!
Thanks!!!!!!!

 

[mumps]

You are very welcome.