Link to home
Start Free TrialLog in
Avatar of Saqib Husain
Saqib HusainFlag for Pakistan

asked on

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
    ActiveSheet.Unprotect
    ActiveCell.Offset(, -1) = prj.Cells(rw, "C")
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
    UserForm1.Hide
End Sub

Open in new window

Avatar of Simon
Simon
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Saqib Husain

ASKER

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?

Edit:
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
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The userform is activated by a selectionchange event
And I was too dumb to not notice it.

Thanks