Trying to find Item with in a sheet
 

HI I have a user form which I want the user to enter data into whic
then inputs that data onto a sheet.

What I want to do is the the following.

1. User enters in a record number (Unique) which I would like to ru
an event that as soon as the user exits the textbox field it unhide
the sheet and checks the sheet from range a7 onwards until it finds it


A. if it finds it needs to inform user it exist and then bring up th
data which the user will then be able to amend.

2. or as soon as the user has inputted all the data and says this exis
and shows the existing data and then askes the user if they would lik
to amend it.

I would prefer the 1. however I have no clue where to start

--
funkymonkU
-----------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...fo&userid=1813
View this thread: http://www.excelforum.com/showthread.php?threadid=38610

Trying to find Item with in a sheet
 

No need to unhide the sheet.

with worksheets("Data")
set rng = .Range(.Cells(7,1),.Cells(7,1).End(xldown)
End With
res = Application.Match(Userform1.Textbox1,rng,0)
if not iserror(res) then
MsgBox Userform1.Textbox1 & " exists - you may edit it"
set rng1 = rng(res)
with Userform1
.Textbox2.Value = rng1.offset(0,1)
.Textbox3.Value = rng1.offset(0,2)
End With
End if

--
Regards,
Tom Ogilvy

"funkymonkUK"
wrote in message
...

HI I have a user form which I want the user to enter data into which
then inputs that data onto a sheet.

What I want to do is the the following.

1. User enters in a record number (Unique) which I would like to run
an event that as soon as the user exits the textbox field it unhides
the sheet and checks the sheet from range a7 onwards until it finds it.


A. if it finds it needs to inform user it exist and then bring up the
data which the user will then be able to amend.

2. or as soon as the user has inputted all the data and says this exist
and shows the existing data and then askes the user if they would like
to amend it.

I would prefer the 1. however I have no clue where to start.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=386103

Trying to find Item with in a sheet
 

Just to add:

res should be dimmed as Variant

Dim res as Variant

--
Regards,
Tom Ogilvy

"funkymonkUK"
wrote in message
...

HI I have a user form which I want the user to enter data into which
then inputs that data onto a sheet.

What I want to do is the the following.

1. User enters in a record number (Unique) which I would like to run
an event that as soon as the user exits the textbox field it unhides
the sheet and checks the sheet from range a7 onwards until it finds it.


A. if it finds it needs to inform user it exist and then bring up the
data which the user will then be able to amend.

2. or as soon as the user has inputted all the data and says this exist
and shows the existing data and then askes the user if they would like
to amend it.

I would prefer the 1. however I have no clue where to start.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=386103

Trying to find Item with in a sheet
 

What do you mean "NO need to unhide the sheet" I have hiden the sheet as
I do not want the user to see or have access to the sheet so they cannot
alter it.

Tom Ogilvy Wrote:
No need to unhide the sheet.

with worksheets("Data")
set rng = .Range(.Cells(7,1),.Cells(7,1).End(xldown)
End With
res = Application.Match(Userform1.Textbox1,rng,0)
if not iserror(res) then
MsgBox Userform1.Textbox1 & " exists - you may edit it"
set rng1 = rng(res)
with Userform1
.Textbox2.Value = rng1.offset(0,1)
.Textbox3.Value = rng1.offset(0,2)
End With
End if

--
Regards,
Tom Ogilvy

"funkymonkUK"

wrote in message
...

HI I have a user form which I want the user to enter data into which
then inputs that data onto a sheet.

What I want to do is the the following.

1. User enters in a record number (Unique) which I would like to
run
an event that as soon as the user exits the textbox field it unhides
the sheet and checks the sheet from range a7 onwards until it finds
it.


A. if it finds it needs to inform user it exist and then bring up
the
data which the user will then be able to amend.

2. or as soon as the user has inputted all the data and says this
exist
and shows the existing data and then askes the user if they would
like
to amend it.

I would prefer the 1. however I have no clue where to start.


--
funkymonkUK

------------------------------------------------------------------------
funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
View this thread:
http://www.excelforum.com/showthread...hreadid=386103



--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=386103

Trying to find Item with in a sheet
 

You said
it unhides the sheet and and checks the sheet

You quoted me as saying
"NO need to unhide the sheet"

I am not sure why that requires explanation, but what I mean is to work with
the hidden sheet you do not have to unhide it to accomplish the actions you
described you wanted to do. If you do want to make the sheet visible to the
user, then you would need to unhide it. However, that is not the impression
you gave in your description.

--
Regards,
Tom Ogilvy


"funkymonkUK"
wrote in message
...

What do you mean "NO need to unhide the sheet" I have hiden the sheet as
I do not want the user to see or have access to the sheet so they cannot
alter it.

Tom Ogilvy Wrote:
No need to unhide the sheet.

with worksheets("Data")
set rng = .Range(.Cells(7,1),.Cells(7,1).End(xldown)
End With
res = Application.Match(Userform1.Textbox1,rng,0)
if not iserror(res) then
MsgBox Userform1.Textbox1 & " exists - you may edit it"
set rng1 = rng(res)
with Userform1
.Textbox2.Value = rng1.offset(0,1)
.Textbox3.Value = rng1.offset(0,2)
End With
End if

--
Regards,
Tom Ogilvy

"funkymonkUK"

wrote in message
...

HI I have a user form which I want the user to enter data into which
then inputs that data onto a sheet.

What I want to do is the the following.

1. User enters in a record number (Unique) which I would like to
run
an event that as soon as the user exits the textbox field it unhides
the sheet and checks the sheet from range a7 onwards until it finds
it.


A. if it finds it needs to inform user it exist and then bring up
the
data which the user will then be able to amend.

2. or as soon as the user has inputted all the data and says this
exist
and shows the existing data and then askes the user if they would
like
to amend it.

I would prefer the 1. however I have no clue where to start.


--
funkymonkUK

------------------------------------------------------------------------
funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
View this thread:
http://www.excelforum.com/showthread...hreadid=386103



--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=386103