Checking user input from userform to values in worksheet

[Darmlucht]

Hi Excel-masters,

I am new to this forum, and I read the rules, but please inform me when I'm not abiding to those rules.
Also, I looked into the forum for previous, related questions, but couldn't find one. But if I missed a thread that's already answering my question, please excuse me.

So here it goes,

I have this userform where the user should fill in their UserId(unique), and ItemId(item they want to rent/check out). Then the date of today and the date they have to return this object, are generated automatic if they puch the button. The data is then posted on a protected sheet, to store all the events.

Now, I want to check the UserId the user filled in, with a list of 'registered users'.
But, for the love of god, I can't find a good piece of code for this.

My code looks like this now:

Private Sub Uitleen_Click()
    
    ThisWorkbook.Sheets("Uitleen").Unprotect Password:="Jeroen"

    
     UitleenDate.Value = "" & Date
     DateBack.Value = "" & DateAdd("d", 14, Date)
     
    

    Worksheets("Uitleen").Activate
    eRow = Blad7.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Cells(eRow, 1) = UserID.Text
    Cells(eRow, 2) = ItemID.Text
    Cells(eRow, 3) = UitleenDate.Text
    Cells(eRow, 4) = DateBack.Text
    
       ThisWorkbook.Sheets("Uitleen").Protect Password:="Jeroen"
       
End Sub

I have to check the UserId.Text-input with all the values in the 'User' worksheet, before the data is plotted in the worksheet.
If it's not in the list, there should be an error.
If it is in the list, it should check the ItemId.Text, with the values in the 'Items' worksheet.
The amount of users/items is not fixed, meaning there can be users/items added in the future, but they still have to be included in the checking. So if on day 1 I only have 2 users/items, it should only check from cell A:2 to A:3, but after day2 there are 5 users/items, so it should check from A:2 to A:6.. so the range is not pre-defined.


Is my question clear? If not, please let me know, and I'll try to explain better.
If I have to upload my whole workbook, let me know!

Kind regards, and thanks in advance.

 

[Fluff]

Hi, welcome to the board.
How about

    Dim UsrFnd As Range
    
    With Sheets("[COLOR=#ff0000]Users[/COLOR]")
        Set UsrFnd = .Columns([COLOR=#ff0000]1[/COLOR]).Find(UserID.Text, .Range("[COLOR=#ff0000]A[/COLOR]1"), , xlWhole, xlByRows, xlNext, False, False)
    End With
    If UsrFnd Is Nothing Then
        MsgBox "UserId not found"
        Exit Sub
    End If

Change sheet name & column references to suit

 

[Darmlucht]

My apologies for the delayed reply, I haven't been in office since I posted my question.
I will try this first thing in the morning tomorrow, when I return.
But since you are MVP, I suppose this will work
Thanks again for the fast reply! I'll keep you up to date!
Kind regards!

 

[Darmlucht]

This works excellent!
Thanks again Fluff!

Now I have another question about the same workbook, but a bit different topic.
Do I post it here, or start a new thread?

Kind regards

 

[Fluff]

This works excellent!
Thanks again Fluff!

Glad to help & thanks for the feedback

Now I have another question about the same workbook, but a bit different topic.
Do I post it here, or start a new thread?

As it's a different topic, I would suggest starting a new thread.

 

[Darmlucht]

One small remark and question is,
when the input field is left empty, it adds lines to the worksheet.
But it shouldn't, it should give an error.
I'm guessing it's as easy as adding one extra line, but I can't figure it out..
Now my code looks like this:

Private Sub Uitleen_Click()
    
    ThisWorkbook.Sheets("Uitleen").Unprotect Password:="Jeroen"

    Dim UsrFnd As Range
    Dim UsrFnds As Range
    
    With Sheets("Personen")
        Set UsrFnd = .Columns(1).Find(UserID.Text, .Range("A2"), , xlWhole, xlByRows, xlNext, False, False)
    End With
    If UsrFnd Is Nothing Then
        MsgBox "UserId not found"
        ThisWorkbook.Sheets("Uitleen").Protect Password:="Jeroen"
        Exit Sub
          End If
          
        
    With Sheets("Items")
        Set UsrFnds = .Columns(1).Find(ItemID.Text, .Range("A2"), , xlWhole, xlByRows, xlNext, False, False)
    End With
    If UsrFnds Is Nothing Then
        MsgBox "ItemID not found"
        ThisWorkbook.Sheets("Uitleen").Protect Password:="Jeroen"
        Exit Sub
    End If
    
     UitleenDate.Value = "" & Date
     DateBack.Value = "" & DateAdd("d", 14, Date)
     
    ThisWorkbook.Sheets("Uitleen").Unprotect Password:="Jeroen"


    Worksheets("Uitleen").Activate
    eRow = Blad7.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Cells(eRow, 1) = UserID.Text
    Cells(eRow, 2) = ItemID.Text
    Cells(eRow, 3) = UitleenDate.Text
    Cells(eRow, 4) = DateBack.Text
    
       ThisWorkbook.Sheets("Uitleen").Protect Password:="Jeroen"
       
End Sub

So now, if the input fields for userID and ItemID are left empty, it still adds the lines of the date to the worksheet.
Thanks in advance for the help!
Kind regards.

 

[Darmlucht]

Okay, I think I got it, just a few moments after I posted this
So previously it was like this:

If UsrFnd Is Nothing Then
        MsgBox "UserId not found"
        ThisWorkbook.Sheets("Uitleen").Protect Password:="Jeroen"
        Exit Sub
          End If

But I changed it to

If UsrFnd Is Nothing Or UsrFnd = "" Then
        MsgBox "UserId not found"
        ThisWorkbook.Sheets("Uitleen").Protect Password:="Jeroen"
        Exit Sub
          End If

This seems to do the job. Now, is this the right way to do this?
Or is this like 'unethical' coding or something

Thanks a lot!

 

[Darmlucht]

Okay, problem!
Now when I run this, it says "Object variable or With block variable not set (Error 91)"

My complete code looks like this

Private Sub Uitleen_Click()
    
    ThisWorkbook.Sheets("Uitleen").Unprotect Password:="Jeroen"

    Dim UsrFnd As Range
    Dim UsrFnds As Range
    
    With Sheets("Personen")
        Set UsrFnd = .Columns(1).Find(UserID.Text, .Range("A2"), , xlWhole, xlByRows, xlNext, False, False)
    End With
    If UsrFnd Is Nothing Or UsrFnd = "" Then
        MsgBox "UserId not found"
        ThisWorkbook.Sheets("Uitleen").Protect Password:="Jeroen"
            Exit Sub
          End If
          
        
    With Sheets("Items")
        Set UsrFnds = .Columns(1).Find(ItemID.Text, .Range("A2"), , xlWhole, xlByRows, xlNext, False, False)
    End With
    If UsrFnds Is Nothing Or UsrFnds = "" Then
        MsgBox "ItemID not found"
        ThisWorkbook.Sheets("Uitleen").Protect Password:="Jeroen"
        Exit Sub
    End If
    
     UitleenDate.Value = "" & Date
     DateBack.Value = "" & DateAdd("d", 14, Date)
     
    ThisWorkbook.Sheets("Uitleen").Unprotect Password:="Jeroen"


    Worksheets("Uitleen").Activate
    eRow = Blad7.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Cells(eRow, 1) = UserID.Text
    Cells(eRow, 2) = ItemID.Text
    Cells(eRow, 3) = UitleenDate.Text
    Cells(eRow, 4) = DateBack.Text
    
       ThisWorkbook.Sheets("Uitleen").Protect Password:="Jeroen"
       
End Sub

this line : If UsrFnd Is Nothing Or UsrFnd = "" Then
is marked yellow in 'debug'..

Thanks again for any help!

 

[Fluff]

That's fine, absolutely nothing wrong with it.
Writing code is, in some ways, a personal thing & everyone has their own ways of doing things.
I, personally, would have checked if the text boxes were blank, before doing anything else.
Something like this

If Len(UserID.Text) = 0 Or Len(ItemID.Text) = 0 Then Exit Sub

But as I say, it's a personal thing.

 

[Darmlucht]

Thanks for your answer and opinion!
I'm trying to get this coding right, so it helps to see other peoples solutions!
I will make a copy of my workbook and paste your code and see which one I like the most