Could not set the rowsource property. Invalid property value.

lattechic

New Member
Joined
Jun 24, 2018
Messages
14
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.

Code:
'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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hi,
try moving the line in your code that disconnects the RowSource before you perform the delete action & see if this resolves the issue


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

Dave
 
Upvote 0
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 :)
 
Upvote 0
Hi,

You can try this update that rebuilds your name range

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top