Could not set the rowsource property. Invalid property value.

[lattechic]

Hi all,

I am a newbie in VBA and I need help with the dilemma I have.

I have a listbox and a delete button in a frame with named range, lstRates,=OFFSET(ConKiloRates!$A$2,0,0,COUNTA(ConKiloRates!$A:$A) -1,11).


My problem is when I delete the last item in the listbox. It displays Runtime error 380 and my lstRange range then resets to =OFFSET(ConKiloRates!#REF!,0,0,COUNTA(ConKiloRates!$A:$A) -1,11) in the Name Manager.


ConKiloRates is the worksheet where I save the items added from the listbox. Once the user deletes an item in the listbox, I also want to delete that record from the worksheet ConKiloRates.

'Delete button
Private Sub CommandButton2_Click()

Dim strRange As String

        With ListBox1
            strRange = .RowSource
            Range(strRange).Cells(.ListIndex + 1, 1).EntireRow.Delete
            .RowSource = vbNullString
            .RowSource = strRange


        End With

End Sub

Appreciate your help. Thanks.

 

[dmt32]

Hi,
try moving the line in your code that disconnects the RowSource before you perform the delete action & see if this resolves the issue

With ListBox1
            strRange = .RowSource
            .RowSource = vbNullString
            Range(strRange).Cells(.ListIndex + 1, 1).EntireRow.Delete
            .RowSource = strRange
        End With

Dave

 

[lattechic]

Hi Dave,

Still the same error and named range changed to OFFSET(ConKiloRates!#REF!,0,0,COUNTA(ConKiloRates!$A:$A) -1,11).

Is it possible to redefine the rowSource (line .RowSource = strRange) in the code and use the OFFSET(ConKiloRates!$A$20,0,COUNTA(ConKiloRates!$A:$A) -1,11) instead? How do I code it?

Thanks for your help

 

[dmt32]

Hi,

You can try this update that rebuilds your name range

Dim rw As Long
    Dim strRange As String
    With Me.ListBox1
        rw = .ListIndex + 2
        If .ListIndex > -1 Then
            strRange = .RowSource
            .RowSource = vbNullString
            Sheets("ConKiloRates").Rows(rw).EntireRow.Delete
            ThisWorkbook.Names.Add Name:=strRange, RefersToR1C1:= _
                   "=OFFSET(ConKiloRates!R2C1,0,0,COUNTA(ConKiloRates!C1) -1,11)"
            .RowSource = strRange
        End If
    End With

It's a bit messy & you probably would be better to use the ListBoxes List property which avoids connecting it to the worksheet but see if the update works for you.

Dave

 

[lattechic]

I'll try it tomorrow and let you know. Thanks, Dave.