VBA ClearContents problem

[LPittenger]

I've got a VisualBasic routine set to clear one set of cells when a change occurs in another set. Everything works fine until it actually reaches the command to clear the cells. Stepping through in the debugger, I see that it has correctly found the proper range to clear and is looping through the cells.

But then the cells simply don't clear. Doesn't work with ClearContents for cell or range; doesn't work for setting value = ""; doesn't work for anything I try.

I've turned off locking & protection on all affected cells.


The odd thing is that the identical code works fine in a dummy workbook created from scratch. When I first saw this, I figured it was some glitch in my original sheet, so I"ve copied the whole thing into a fresh workbook... but this new workbook is now showing precisely that same behavior.


What is preventing my routine from changing the cells in my workbook?!


thanks
LP

 

[datAdrenaline]

Any chance of attaching the problematic sheet? ...

 

[LPittenger]

Any chance of attaching the problematic sheet? ...

Yup... here it is -- or, rather, a much shorter stripped-down version which preserves the code and problem.

 

[LPittenger]

Update --

I cross-posted over on http://www.excelforum.com/excel-programming/653595-vba-refuses-to-change-cells.html this morning. No solution there either -- but someone did run the sheet/book (which continues to fail when I try it) and reported it working.

Which means there must be some application-level setting (rather than something in the workbook itself) which is at least partially responsible for the odd behavior on my machine.

But even restarting excel and reloading the sheet doesn't solve my problem -- and I've got every cell unlocked, the sheet unprotected, and macro security off. Application.EnableEvents is, of course, on (else the code wouldn't run at all, right?, and anyway, the code itself turns it [back] on before exiting).

Are there some other application-level settings or events which could account for the different behavior of the identical sheet on different systems?

 

[dkinley]

It might be in your references if it is working for someone else and not you.

-dK

 

[LPittenger]

It might be in your references if it is working for someone else and not you.

But it's the same exact sheet. I posted a copy of it and so they're presumably running exactly the same sheet (and VBA code and cell references) that I have.

 

[tranchemontaigne]

You can have references within Personal.XLS and within the open document. If someone has references within Personal.XLS they will be applied to all open documents on that computer, regardless of whether the references are made within a particular file.

Hope this helps
________
Ford model a (1927) history

 

[LPittenger]

You can have references within Personal.XLS and within the open document. If someone has references within Personal.XLS they will be applied to all open documents on that computer, regardless of whether the references are made within a particular file.

Thanks for the reply! I've got no Personal.XLS set up on the machine/user I'm working with, so nothing is being imported when I attempt the sheet. I can't (of course) speak to what might have been happening for the poster who reported that the sheet worked for them...

 

[datAdrenaline]

I have been unable to unzip the file??

 

[LPittenger]

I have been unable to unzip the file??

Let me try a back-compatible zip version... hopefully this will work better?

Thanks!

 

[LPittenger]

Here's an odd thing. With the tweaked code shown below -- using clearRegionRgn as an "intermediate" step between the string-represented address (clearRegion) and the .ClearContents command itself -- the debugger is reporting that clearRegionRgn is empty.

That is to say, with code:

'IF TRIGGERED by a change in a trigger cell, get the associated clear region
clearRegion = clearTableRng(i, 2).Text
Set clearRegionRng = Range(clearRegion) 
' CLEAR IT
Application.EnableEvents = False
Range(clearRegion).Cells.ClearContents
clearRegionRgn.Cells.ClearContents
Application.EnableEvents = True

The string clearRegion gives the correct value, Range(clearRegion) gives the correct range... but the debugger shows clearRegionRng evaluating to Empty.

Thus Range(clearRegion).Cells.ClearContents is called just fine -- though it still fails to have any effect on the sheet's cells -- but clearRegionRgn.Cells.ClearContents (which ought to be the identical call) causes the VBA routine to abort (never getting to the next step) because clearRegionRgn is, despite the Set command above, evaluating to "Empty".

(Same thing when I omit the ".Cells" intermediate on both commands)


The "working" command (Range(clearRegion).ClearContents or Range(clearRegion).Cells.ClearContents etc...) still fails to change/clear the cells in the worksheet... but does the odd behavior of clearRegionRgn give any clue?

Is there something defective about the Range(clearRegion) value which -- though it reports the right address and looks fine when parsed in the VBA debugger -- is responsible both for the failure to update sheet cells and the Set-ting of clearRegionRgn to "Empty"? I don't recall seeing behavior like this before...