We help IT Professionals succeed at work.

Command button code being aborted.

Saqib Husain, Syed
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

Watch Question

SimonPrincipal Analyst

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.
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.
SimonPrincipal Analyst

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
Most Valuable Expert 2011
Top Expert 2011
Do you have any change event code in that sheet, or any conditional formatting that uses a UDF?
The userform is activated by a selectionchange event
And I was too dumb to not notice it.