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

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.

--
Regards,
Tom Ogilvy

"Walshy" wrote in message
...
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

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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Walshy" wrote in message
...
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

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

Is sorted now thanks

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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Walshy" wrote in message
...
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






.

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

Is sorted now thanks

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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Walshy" wrote in message
...
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






.