ClearContents bug help

[ddub25]

I created a Command Button with the following code.

Private Sub CommandButton1_Click()
Sheets("IB_FB_H_3_Working").Range("$D$7:$D$8,$G$9:$L$34,$M$9,$M$18,$M$27,$N$9,$N$18,$N$27,$O$9:$O$34,$T$9:$T$34,$V$9:$V$34").ClearContents
End Sub

Whenever I use it, it returns the error message, "Code execution has been interrupted". Even so, it does manage to clear the cells that I want cleared. When I click 'Debug',
it brings up the code with the 'End Sub'part highlighted in yellow. Is this the problem part? It hasn't been a problem in the past. Can anyone help?

Dan

 

[Rick Rothstein]

I created a Command Button with the following code.

Private Sub CommandButton1_Click()
Sheets("IB_FB_H_3_Working").Range("$D$7:$D$8,$G$9:$L$34,$M$9,$M$18,$M$27,$N$9,$N$18,$N$27,$O$9:$O$34,$T$9:$T$34,$V$9:$V$34").ClearContents
End Sub

Whenever I use it, it returns the error message, "Code execution has been interrupted". Even so, it does manage to clear the cells that I want cleared. When I click 'Debug',
it brings up the code with the 'End Sub'part highlighted in yellow. Is this the problem part? It hasn't been a problem in the past. Can anyone help?

I do not see anything that should be causing that error in the code you posted; although I would note that you have any Change or Calculate event code that covers any of those cells in your Range call, then those event code procedures would be executed in reaction to your clearing their cells. I do not know if the error message you got is one that would be raised for that particular situation, but a quick test would be to put Application.EnableEvents=False as the first statement the above code and Application.EnableEvents=True as the last line and then run the code and see if it still errors out or not. By the way, you can save yourself a lot of typing (in the future) by omitting all those $ signs... you would only need them if you were inserting a formula that would be copied/filled down (or across)... internal VB code should not need to use absolute referencing.

 

[jasmith4]

Sounds like you need Rob Bovey's Code Cleaner. Sometimes things get corrupt when you're changing a lot of code around...

 

[ddub25]

Rick, this is the only vb code I have on the sheet. There are numerous formulas in cells and conditional formatting, but no other code. I have other command buttons in other sheets in the same workbook. I am wondering whether the problem is to do with copying the command button to these other sheets and adapting them for those sheets. To see if this was the problem I moved the sheet in question to a fresh workbook, but still had the same error when executing the command button.

I tried the code you suggested, setup like this:

Application.EnableEvents = False
Private Sub CommandButton1_Click()
Sheets("IB_FB_H_3_Working").Range("$D$7:$D$8,$G$9:$L$34,$M$9,$M$18,$M$27,$N$9,$N$18,$N$27,$O$9:$O$34,$T$9:$T$34,$V$9:$V$34").ClearContents
End Sub
Application.EnableEvents = True

but the following message is returned, "Compile Error: Invalid outside procedure"

*The reason why I put absolute coordinates in is because in the past when I have edited my table (added new columns or rows etc), the command button would now be referencing the wrong columns/rows.

jasmith4, thanks, I'll try Rob Bovey's Code Cleaner.

 

[Rick Rothstein]

I tried the code you suggested, setup like this:

Application.EnableEvents = False
Private Sub CommandButton1_Click()
Sheets("IB_FB_H_3_Working").Range("$D$7:$D$8,$G$9:$L$34,$M$9,$M$18,$M$27,$N$9,$N$18,$N$27,$O$9:$O$34,$T$9:$T$34,$V$9:$V$34").ClearContents
End Sub
Application.EnableEvents = True

but the following message is returned, "Compile Error: Invalid outside procedure".

The two Application.EnableEvent must be located between the Sub and the End Sub like this...

Private Sub CommandButton1_Click()
  Application.EnableEvents = False
  Sheets("IB_FB_H_3_Working").Range("$D$7:$D$8,$G$9:$L$34,$M$9,$M$18,$M$27,$N$9,$N$18,$N$27,$O$9:$O$34,$T$9:$T$34,$V$9:$V$34").ClearContents
  Application.EnableEvents = True
End Sub

 

[jasmith4]

All code other than declarations has to be inside blocks between Sub/End Sub, Function/End Function or Property/End Property. You can't have that line of code outside the Sub/End Sub block.

As for your original code, everything between the word "Sheets" and the word "ClearContents" is on one line, right? I just copied that line into a new workbook, renamed Sheet1 to "IB_FB_H_3_Working", and it worked fine. Try this too:

    With Sheets("IB_FB_H_3_Working")
        .Range("$D$7:$D$8").ClearContents
        .Range("$G$9:$L$34").ClearContents
        .Range("$M$9").ClearContents
        .Range("$M$18").ClearContents
        .Range("$M$27").ClearContents
        .Range("$N$9").ClearContents
        .Range("$N$18").ClearContents
        .Range("$N$27").ClearContents
        .Range("$O$9:$O$34").ClearContents
        .Range("$T$9:$T$34").ClearContents
        .Range("$V$9:$V$34").ClearContents
    End With

 

[ddub25]

Thanks Rick, much appreciated. I closed down excel, re-booted my computer as both have been running for the past month non-stop and now I don't get the error. Maybe the program had gained lots of errors over the period and just needed a reset.

About the absolute references - I've just realised that even when using absolute references, the references do not adjust if new rows and columns are created. I have copied my table down 10times and created a button for each to delete (reset) the contents of each table. If I want to add a column to each table, lets say, I then have a really tedious task of re-referencing the correct cells for each button to delete. Is there not a way of fixing the code to the original cells so that when rows or columns are added, the references remain in the correct place?

 

[jasmith4]

From your last post it seems you also needs ATF-Cleaner from www.atribune.org. And yes, it's a good idea to shut down both Excel and your computer every now and then.

 

[xenou]

Use Named Ranges:
Excel Names -- Excel Named Ranges

Not sure what your references are but if they are rows or columns in the table I prefer some kind of logical scheme:

Table01_Row01 --> First row of "Table1"
Table01_Row02 --> Second row of "Table1"
Table02_Row01 --> First row of "Table2"
Table01_Row02 --> Second row of "Table2"

This also makes it easier to do things like iterate your tables in a code loop using the sequence of table names since they follow a pattern that can be incremented each time through the loop.


Note: Don't know if code cleaners are a side discussion here but for what it's worth I've probably written tens of thousands of lines of Excel VBA code in the last seven years and never needed to "clean" anything. Not that I think such products are of no value - just that I've never needed such a tool yet (knock on wood). I do however find that an occasional shut down and/or reboot is the answer to obscure bugs that don't make sense. Edit: actually, on second thought I have *rebuilt* workbooks as a precautionary measure and that probably counts as a form of "cleaning" - not sure that I did so out of need so much as from wanting to be 100% sure my project was as good as it good possibly be. I have heard of cases where this kind of thing has helped too.

 

[ddub25]

jasmith4, so if I used your code format, I'd write it like this? How is this code different and more advantages than using the previous code where it was all on one line? I didn't quite get understand, sorry.

Private Sub CommandButton1_Click()
With Sheets("IB_FB_H_3_Working")
.Range("$D$7:$D$8").ClearContents
.Range("$G$9:$L$34").ClearContents
.Range("$M$9").ClearContents
.Range("$M$18").ClearContents
.Range("$M$27").ClearContents
.Range("$N$9").ClearContents
.Range("$N$18").ClearContents
.Range("$N$27").ClearContents
.Range("$O$9:$O$34").ClearContents
.Range("$T$9:$T$34").ClearContents
.Range("$V$9:$V$34").ClearContents
End With
End Sub