Macro code to show userform bringing runtime error 1004

[ExcelFailure]

Hello,

I have the following code in a standard module:

Sub OpenCName()
ChooseCName.Show
End Sub

The userform code is as follows:

Private Sub CommandButton1_Click()
    Selection.Value = ListBox1.Value
    Unload Me
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim rRng As Range, r As Range, rSortCr As String

Set rRng = Sheets("LiveCustomers").Range("Table1[Name]")
rSortCr = UCase(ActiveCell.Value)

For Each r In rRng
    If InStr(UCase(r), rSortCr) Then Me.ListBox1.AddItem r.Value
Next r

If Me.ListBox1.ListCount = 1 Then
    Me.ListBox1.ListIndex = 0
    Selection.Value = Me.ListBox1.Value
    Unload Me
End If

End Sub

However I am getting a runtime error 1004 which says application-defined or object-defined error.

Can anyone help please?

[BadlySpelledBuoy]

Open the VBE (Alt + F11) and click on the line of code "Private Sub UserForm_Initialize"
Then by tapping the F8 button to step through the code line by line, tell us which one it errors out on.

Or, attach the workbook so we can see.

BSB

[ExcelFailure]

Thank you - it errors when I try to move on from the Set rRng line

Any ideas?

[bakerman2]

Have you checked (and double checked) all names used ?(Sheetname, tablename, headername)
Because I just setup a table and it works just fine as you wrote it.

[ExcelFailure]

Sorry, it was a very stupid mistake on my part. Thanks for pointing me in the right direction

[ExcelFailure]

Just further to this, my code is now:

Private Sub UserForm_Initialize()
TextBox1.SetFocus
End Sub

Private Sub CommandButton3_Click()

Dim rRng As Range, r As Range, rSortCr As String

Set rRng = Sheets("LiveCustomers").Range("Table7[name]")
rSortCr = TextBox1.Value

For Each r In rRng
    If InStr(UCase(r), rSortCr) Then Me.ListBox1.AddItem r.Value
Next r

If Me.ListBox1.ListCount = 1 Then
    Me.ListBox1.ListIndex = 0
    Selection.Value = Me.ListBox1.Value
    Unload Me
End If

End Sub

When I press the CommandButton3 it doesn't search in the table for the value of textbox1 and put the results in the listbox as it should. If I press the button with nothing in the textbox it comes up with all the entries in the table. If I put any text string in the textbox it comes up with nothing in the listbox?

Can anyone help?

[bakerman2]

Please post sample workbook which represents actual situation so we have some testing material.