issue with userform activate or table

[billyshears]

I have a userform setup that pulls data from a table on another worksheet in the workbook and populates the userform upon click of a button. I have a code that works great.

When I try to duplicate the code on a second button, it cannot seem to find the table and data comes up blank. Below is the code. Other than changing the name of the userform, is there something I am doing wrong that would cause it work differently? It is driving me crazy that it works for one and not the other. I dont see any differences in how its setup

Private Sub UserForm_Activate()

TextBox1.Value = Worksheets("Sheet2").Range("A1").Value

Dim RecordRow As Long
    Dim RecordRange As Range

    ' Turn off default error handling so Excel does not display
    ' an error if the record number is not found
    On Error Resume Next
        
        ' Find the row in the table that the record is in
        RecordRow = Application.Match(CLng(UserForm1.TextBox1.Value), Worksheets("Sheet1").Range("Table13[Record]"), 0)
        
        ' Set RecordRange to the first cell in the found record
        Set RecordRange = Worksheets("Sheet1").Range("Table13").Cells(1, 1).Offset(RecordRow - 1, 0)

        ' If an erro has occured i.e the record number was not found
        If Err.Number <> 0 Then
    
            ErrorLabel.Visible = True
            On Error GoTo 0
            Exit Sub
        
        End If
    
    ' Turn default error handling back on (Let Excel handle errors from now on)
    On Error GoTo 0
    
    ' If the code gets to here the record number was found
    ' Hide the error message 'Not Found'
    ErrorLabel.Visible = False
    ' and populate the form fields with the record's data
    UserForm1.TextBox2.Value = RecordRange(1, 1).Offset(0, 1).Value
    UserForm1.TextBox3.Value = RecordRange(1, 1).Offset(0, 2).Value
    UserForm1.TextBox4.Value = RecordRange(1, 1).Offset(0, 3).Value
    UserForm1.TextBox5.Value = RecordRange(1, 1).Offset(0, 4).Value
End Sub

 

[Anthony47]

You say that the macro works "upon click of a button", but the code runs on theUserForm_Activate event...
Also, did you put the code into the "userform code module" (the module you access when, working with the vba editor, you display the concerned userform and then press F7)? And which is the name of the userform where the code doesn't work?

However if you remove the "On Error Resume Next" maybe you will get an error message that will explain what you miss
Also, for testing, ad a Beep just before that line, so you will hear a beep when the code is executed

Bye