Matching and Looking Up Values from a Userform onto Sheet

[Commandolomo]

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

 

[HalfAce]

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:

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]

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]

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]

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]

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

Debug highlights this line?

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...


Dan

 

[Commandolomo]

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]

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" ( ) 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.

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]

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

ray: ray: ray: ray: ray: ray: ray: ray: !!!!!!!!!!

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]

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.
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.

Dan