Matching and Looking Up Values from a Userform onto Sheet

Commandolomo

New Member
Joined
Sep 2, 2004
Messages
11
I have searched for some answers to what follows, but with little success!

I have a user form, User_Experience, where a unique ID is first entered - once a "Submit" cmd button is clicked, the values from the userform are transposed onto a worksheet - Sheet 2

Prior to this, I want to ensure that the unique ID that is entered into the userform is valid, by looking up the value that has been entered - in txt1 - against a master list of unique ID's - Sheet 3.

Does anyone have any code or suggestions as to how this could be accomplished?

Cheers
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Re: Matching and Looking Up Values from a Userform onto Shee

Here's an idea that may help.

Slap another CommandButton on your userform and name it ConfirmID.Then give it this code:
Code:
Private Sub ConfirmID_Click()
If Application.WorksheetFunction.CountIf(Sheets("Sheet3").Range("A:A"), User_Experience.txt1.Text) > 1 Then
    MsgBox ("There are " & Application.WorksheetFunction.CountIf(Sheets("Sheet3").Range("A:A"), User_Experience.txt1.Text) & " instances of " & User_Experience.txt1.Text & " in the master list.")
    Exit Sub
End If
If Application.WorksheetFunction.CountIf(Sheets("Sheet3").Range("A:A"), User_Experience.txt1.Text) = 1 Then
    MsgBox "This ID has been confirmed."
    Else: MsgBox "This ID can not be confirmed."
End If
End Sub
Assumes the sheet with the "master list of unique ID's" is actually named Sheet3, and the list is in column A. (Change to suit)

If this isn't exactly what you're after, it should at least spark some ideas...

Hope it helps,
Dan
 

Commandolomo

New Member
Joined
Sep 2, 2004
Messages
11
Re: Matching and Looking Up Values from a Userform onto Shee

Nice one, thanks for your help, I like the idea of the confirm ID cmd button.....but I am getting an error message

Run-Time Error '9':
Subscript Out of Range

I have checked the references, and have tried various amendments to the data - for example, changing A:A to A1:A1000, and xxxx.text to xxxx.Value.

The names and references I provided were genuine, so I was wondering whether if one owuld know the reason for this err message?

Many thanks
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Re: Matching and Looking Up Values from a Userform onto Shee

The Subscript out of range error usually (always?) means that the code is referring to something it can't find. For example a sheet name that is mispelled (and therefore doesn't exist) or something along those lines.
When you get the error, (and hit debug), which line is highlighted in yellow?

Also, as an afterthought I added a bit to the code that will tell you if you ever end up with more than one instance of any ID in your master list...

Dan
 

Commandolomo

New Member
Joined
Sep 2, 2004
Messages
11

ADVERTISEMENT

Re: Matching and Looking Up Values from a Userform onto Shee

Thank you very much for the additional code - will really help when the time comes for the Clerks to data input, as it will avoid duplication.

However, Debug takes me to the first line, which is highlighted in yellow.

I have also tested this by changing the format of the cells of the ID list on Sheet 3, into General, Text and Number - all to no avail!

Any other possibles you may think of?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Re: Matching and Looking Up Values from a Userform onto Shee

Debug highlights this line?
Code:
If Application.WorksheetFunction.CountIf(Sheets("Sheet3").Range("A:A"), User_Experience.txt1.Text) > 1 Then
If the sheet your master list is on is named Sheet3 then I don't know what would be causing the error.
I can't seem to make it error out on me...

:unsure:
Dan
 

Commandolomo

New Member
Joined
Sep 2, 2004
Messages
11

ADVERTISEMENT

Re: Matching and Looking Up Values from a Userform onto Shee

Yep, thats the one - it appears all on one line in my vb editor, but that should'nt make a difference, should it? I thought that with if....then...else statmente the If and the Then needed to be on the same line anyway!

'Tis very bizarre! :(

Thank you for your time Dan
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Re: Matching and Looking Up Values from a Userform onto Shee

Yes, in the VB editor, the If and Then statements need to be on the same line. (Can be sepertaed by an underscore, but that simply tells excel that it's all still the same line of code.)

I'd keep rooting around in the code and try to find the problem as it works "swimmingly" ( :rolleyes: ) for me and seems to be exactly what you're asking for.

I'm using XL 2003 but it should work fine with earlier versions too.

I'd rather not resort to using a loop, but maybe you can try this and see if it still gives you the error.
Code:
Private Sub ConfirmID_Click()
Dim c As Range
For Each c In Sheets("Sheet3").Range("A1:A1000")
    On Error GoTo GiveUp
    If c <> "" Then
        If c.Value = txt1.Text Then
            MsgBox "This ID has been confirmed."
            Exit Sub
        End If
    End If
Next c
GiveUp:
MsgBox "This ID can not be confirmed."
End Sub
If this give you the same error then I think it has to be in the naming of the sheet...

Dan
 

Commandolomo

New Member
Joined
Sep 2, 2004
Messages
11
Re: Matching and Looking Up Values from a Userform onto Shee

:pray: :pray: :pray: :pray: :pray: :pray: :pray: :pray: !!!!!!!!!!

Thank you very much, have used the loop code and this works great - am perplexed by the first set fo code though! At this stage, I must assume that it must be down to those pesky Excel gremlins!

Cheers Dan


Pete
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Re: Matching and Looking Up Values from a Userform onto Shee

You're very welcome. Glad it worked.

Don't understand why that one would if the other one won't. :unsure:
Shame too, because the other one would be so much quicker, and gives you the means to check & make sure all your IDs are actually unique.

But, I suppose a slower routine that works is much faster than a faster routine that doesn't. :LOL:

Dan
 

Forum statistics

Threads
1,147,508
Messages
5,741,570
Members
423,668
Latest member
Audorin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top