Command button code being aborted.

I have a userform which has a listbox, a textbox and a commandbutton

Based on the textbox the items in the listbox are narrowed down.

While the required item on the listbox is selected the commandbutton is clicked which is supposed to populate two cells. The problem is that as soon as one of the cells is populated the sub is aborted. If I change the order of the population again after the first population the routine is aborted.

Here is the code for the commandbutton. The code aborts at line 6. If I comment it out the program aborts at line 13

Private Sub CommandButton1_Click()
    Dim prj As Worksheet
    Dim rw As Long
    Dim cel As Range
    If Me.ListBox1.ListCount = 0 Then Exit Sub
'    ActiveCell.Value = Me.ListBox1.List(Me.ListBox1.ListIndex) '.Selected
    Set prj = Workbooks("Timesheet control.xls").Sheets("Projects")
    rw = 0
    For Each cel In prj.Range("D:D")
        If cel = Me.ListBox1 Then rw = cel.Row: Exit For
    Next cel
    ActiveCell.Offset(, -1) = prj.Cells(rw, "C")
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
End Sub

Open in new window

LVL 43
Saqib Husain, SyedEngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi, it doesn't exit after line 6 when I uncomment that line then run it. I haven't set up a second spreadsheet to test it, but inserted a second output line (ActiveCell.Offset(, -1) = "test") after the first and it executes both of them.

Suggest you set a breakpoint on the first line of code and step through it. You should see any errors reported to you as there is no error-handling in the sub.
Saqib Husain, SyedEngineerAuthor Commented:
There is no error handling. I was stepping through which showed me that it aborted immediately after either of those lines. No error, no message. Simply aborted.
Hmm, both those lines involve the prj reference to the other workbook. Is it open in the same instance of Excel as the workbook with your userform in?

Does the sub still abort if you insert some other line after line 6?

I've now tested a bit further. I'm not entirely sure that this will apply in your case, but I have to cast the value from the other spreadsheet to a string to compare with the selected listbox item:

    For Each cel In prj.Range("D:D")
        Debug.Print cel.Address, cel.Value
        If CStr(cel.Value) = Me.ListBox1 Then rw = cel.Row: Exit For
    Next cel

Open in new window

If you don't do this, your code may traverse every row in column D of the other workbook without matching the string value in the listbox. That then results in it trying to set a cell value to equal row zero of prj.Cells
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Rory ArchibaldCommented:
Do you have any change event code in that sheet, or any conditional formatting that uses a UDF?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerAuthor Commented:
The userform is activated by a selectionchange event
Saqib Husain, SyedEngineerAuthor Commented:
And I was too dumb to not notice it.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.