Input box

[Walshy]

What I wanna do is have a box pop up doing the following 3
things:

1 -Saying please enter id (this will enter an id into a
selected cell, type in number then click button ok)
2 -Button saying Dont know id which wil leave the cell
blank
or 3 -Button saying Cancel (this will cancel the macro and
I will assign code to undo the original macro.)

basically what I am doing is I have a table. and when the
user wants to add a new record they click a button and a
macro inserts a row at the first blank copies the formulas
etc from the row above and pastes them into the new row..
The formulas are all vlookups from the first cell so in
the macro I want a box to pop up (as your macro done fine)
and then the user will enter the number into this and then
click ok then this will return the value into the
worksheet and the lookups will all pick up from that. If
they dont know the code the have a button saying that to
leave the row blank or finally cancel which would then
delete the new row and cancel the macro so it goes back to
how it was.

Hope you can help... thanks

 

[Tom Ogilvy]

Dim res as Variant, rng as long
Dim rng1 as Long, res1 as variant
res = Inputbox("Enter id")
if res = "" then
' user clicked cancel so quit
exit sub
End if
With Worksheets("Data")
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with
if isnumeric(res) then res = cdbl(res)
res1 = application.Match(res,rng,0)
if iserror(res1) then
msgbox "Id was not found")
else
set rng1 = rng(res)
msgbox "found at row " & rng1.row
End if

The inputbox has a cancel button, so don't see why you would need a separate
cancel.

 

[Tom Ogilvy]

Why did you post this again after you already acknowledged an answer to an
earlier posting of the question?

 

[Guest]

Because I didnt put much detail on the original message so
I elaborated.

Is sorted now thanks

 

[Guest]

Because I didnt put much detail on the original message so
I elaborated.

Is sorted now thanks