Error when target text box on vba userform is not populated

Question

I have no need to populate the search box when submitting guest check in details, the code i am using is below, is it possible to ad additional code to avoid this error. Thanks in advance!!!!! lblrow.Caption = .Row 'error line

Private Sub Txtforename_Change()
    Dim rng As Range
    lblrow.Visible = False

    With Sheets("Report")
        Set rng = Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
    End With

    With rng.Find(Txtguestsearch, lookat:=xlWhole)
        lblrow.Caption = .Row
    End With
End Sub

Which error in which line? Please edit your original question and add the missing information. — Pᴇʜ
– Pᴇʜ, Commented Nov 19, 2018 at 15:07
That FIND statement will throw errors if a value in Txtguessearch isn't found. If you expect it to be blank you could test If Trim(Txtguestsearch) = "" Then. Rng may be better set as Set Rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)). — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Nov 19, 2018 at 15:14
I want to try that tool, just don't have a personal windows (stupid millennial with a mac) and I can't put rubberduck on my work station in case you try to hack my screen and report that I spend 30% of my workday on Stack Overflow @MathieuGuindon (I'm on to you) — urdearboy
– urdearboy, Commented Nov 19, 2018 at 16:11
@urdearboy - "stupid millennial with a mac" is one of the greatest quotes I've seen all year! Thanks for the laugh. Also, you won't be the only one in trouble for spending too much time at SO/SE! — FreeMan
– FreeMan, Commented Nov 19, 2018 at 16:13

urdearboy · Accepted Answer · 2018-11-19 15:27:32Z

3

You need to code for possibility of your value not being found with the Range.Find method. I also updated your LRow calculation to use the with block and more standard calc

Private Sub Txtforename_Change()

Dim rng As Range, Found as Range
lblrow.Visible = False

With Sheets("Report")
    Set rng = .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

Set Found = rng.Find(Txtguestsearch, lookat:=xlWhole)

If Not Found is Nothing Then
    With Found
        lblrow.Caption = .Row
    End With
Else                          '<--Optional
    MsgBox "Not Found"        '<--Optional
End If                      

End Sub

edited Nov 19, 2018 at 15:27

answered Nov 19, 2018 at 15:21

urdearboy

14.6k6 gold badges35 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3126785 Over a year ago

Sweeet, many thanks problem solved, removed the msgbox as not required. Simply needed to allow guests check-in without experiencing the error. Cheers!!!!

Mathieu Guindon Over a year ago

@user3126785 notice the . dot operator in the Set rng = .Range(...) assignment - that's another bug this answer fixed in your code. Try running the original code with any other sheet than "Report" active - the unqualified Range call would fail, because it's implicitly referring to the ActiveSheet.

Collectives™ on Stack Overflow

Error when target text box on vba userform is not populated

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related