Excel crashes after numerous range updates with run-time error 1004

[whitehawk81]

Hi there!
I'm currently getting run-time error 1004: "Unable to get the CurrentRegion property of the Range class" within following subroutine:

Sub clearData()
Dim modDataRng As Range

Set modDataRng = Sheet2.Range("B4:B7")

If WorksheetFunction.CountA(modDataRng) > 0 Then
    modDataRng.ClearContents
End If

If WorksheetFunction.CountA(Sheet3.Range("A:A")) > 1 Then
    Sheet3.Range("A2").CurrentRegion.Offset(1).ClearContents   'I get the error at this line
End If

End Sub

The strange thing is, I only get this error after I selected a few entries in the upper userform listbox.
This issue started, when I changed the ranges to tables on the "Servers" sheet.
I also uploaded an example file to demonstrate the issue better.
You can open the userform with the "Manage Plan" button on "Planlist" sheet.
I suspect, that it has to do something with the tablerange updates.
Do you have an idea, what could cause this issue?

 

[whitehawk81]

Is this the only code you are running when it crashes, or is there any automated code which may also be running?

This code runs every time the listbox gets updated. So when I select another item or change an entry.

 

[Joe4]

I have downloaded your sheet. There does not appear to be any data on Sheet3 to delete currently.
If I manually add a row of data to that sheet, and run the "clearData" procedure, it seems to delete it without issue.
Can you walk me through recreating this problem you are having?

 

[whitehawk81]

I have downloaded your sheet. There does not appear to be any data on Sheet3 to delete currently.
If I manually add a row of data to that sheet, and run the "clearData" procedure, it seems to delete it without issue.
Can you walk me through recreating this problem you are having?

I use Sheet3 as a temporary storage for "Change" list items. Normally I get the error, when I open the userform "manPlanForm" and just select a few list items in the upper listbox. The code populates the other fields and the second listbox below with the selected entry data. The issue seems to be with clearing and updating the table "chServerTbl" on "Sheet4". I assume this only, because the code worked fine without crashes, when I was using simple ranges on "Sheet4".

 

[Joe4]

It looks like your range is not selectable at that time. I think it may be because your form is still open and has the focus.
Try adding the line in red to your "UserForm_Terminate" code and see if that works:

Private Sub UserForm_Terminate()
Dim lockRng As Range

Set lockRng = Sheet2.Range("Locked_sel")

lockRng.Value = "n/a"

Unload Me
clearData

End Sub

 

[whitehawk81]

It looks like your range is not selectable at that time. I think it may be because your form is still open and has the focus.
Try adding the line in red to your "UserForm_Terminate" code and see if that works:

Private Sub UserForm_Terminate()
Dim lockRng As Range

Set lockRng = Sheet2.Range("Locked_sel")

lockRng.Value = "n/a"

Unload Me
clearData

End Sub

I added "Unload Me" before the "clearData" is called, but if I'm using table on "Sheet4", I get the same error.
I implemented option buttons on the userform to be able to switch between table and range usage.
Here is the updated file.
When the range option is selected, the macro works fine, but when I change it to use table, I get the same error after a few entries were selected.

 

[Joe4]

Your project is a bit complex with a lot of moving parts, making it hard to debug.
And to be honest, I have not used Excel User Forms in many years, and I rarely used tables (once I learned Microsoft Access about 20 years ago, I pretty much stopped using Excel User Forms, as Access's forms work much better). So I think I have gone about as far as I can on this.

One thing to think about, it appears that you may actually have a relational database, in case a relational database program like Microsoft Access may be better for a project like this. As it is a relational database program, it handles these type of tasks much more elegantly than Excel (as that is not really what Excel was desinged for).

 

[whitehawk81]

Your project is a bit complex with a lot of moving parts, making it hard to debug.
And to be honest, I have not used Excel User Forms in many years, and I rarely used tables (once I learned Microsoft Access about 20 years ago, I pretty much stopped using Excel User Forms, as Access's forms work much better). So I think I have gone about as far as I can on this.

One thing to think about, it appears that you may actually have a relational database, in case a relational database program like Microsoft Access may be better for a project like this. As it is a relational database program, it handles these type of tasks much more elegantly than Excel (as that is not really what Excel was desinged for).

Thanks for the advice! I agree, it would be more practical to implement the database in Access. I will consider it.

 

[Joe4]

You are welcome.
Best of luck on your project!