Trouble trying to format a cell when the sheet is unlocked

[basstwo]

I am running Excel 2007 on Windows 7.

I have a workbook that I created in Excel 2003. It has extensive VBA code to help run it. When I switched over to Excel 2007 I encountered all sorts of code issues that I had to resolve. I have ironed them all out but ONE. HEre is my issue:

I have some cells that will not allow me to change their format. The worksheets/workbook has no protection (nor has it *ever*). The way I discovered this is my VBA code needs to change the border style on some cells. It throws an error when it cannot complete this step.

I can select the cell and change the contents. But I cannot change the formatting at all.

This issue seems to crop up while I am using the workbook. If I go back to an older version or close without saving, I can sometimes get the code with the formatting to run. But soon after it will crash. It is as if my code were locking some cells but I have *never* used any protection/locking ability in my code.

I tried running Microsoft Office Diagnostics but it ran just fine with no errors.

Erik

 

[bolo]

**** i was hoping for a more elegant solution, and perhaps a reason why it happens. Oh well back to the drawing board.

Regards

Bolo

Good news: I found a solution that works for me.
Bad news: You found it too...

I ended up having to add a line that clears the formats for all the cells in the range I am working with. I then go on in the code to apply the formatting I want. This works for me since I was reformatting each cell in the range anyway. Sorry I couldn't get anything better that this.

 

[bolo]

**** i was hoping for a more elegant solution, and perhaps a reason why it happens. Oh well back to the drawing board.

Regards

Bolo

Now my solutions isn't even working. I have removed all borders, all merges, everything and this error still pops up. Someone must know exactly why is happens? Anyone?

Thanks

 

[bolo]

A gentle bump!

as an update, the error only occurs when i try to format a single row of data. I am only formatting inside verticals, and edges, i have removed inside horizontals and top, bottoms.

Even if i clear the whole sheets, formatting the error still crops up. The most frustrating thing is that it doesn't always crop up. I am creating a lot of these tables. (400+) Some run fine and some die. Within the same macro, if i comment out the offending bit of code, further down the line i format another single row and it works! I insert a few columns before the formatting. Could this affect the macro. But why doesn't it affect all!!!!

Thanks

xl07 user here!

 

[basstwo]

I have been trying adding this before I change any borders:

.Borders.LineStyle = xlNone

It seems to stop the errors without losing all of the formatting

 

[bolo]

I have been trying adding this before I change any borders:

.Borders.LineStyle = xlNone

It seems to stop the errors without losing all of the formatting

I have already added it to my code but no good.... This is really frustrating now!!!

Now even cells that are larger than 1 row or 1 column are dying and i don't know why... So annoyed with excel right now....

 

[bolo]

I have already added it to my code but no good.... This is really frustrating now!!!

Now even cells that are larger than 1 row or 1 column are dying and i don't know why... So annoyed with excel right now....

last bump.... Does anyone know why?????

Thanks!

Bolo

 

[basstwo]

I still am having the issue. By adding the code I mentioned above, though, the error happens much less often. But the only way I have found to get rid of it once it shows up again is to wipe all of the formatting. So I have built into my code a function to wipe all formats and then reapply the ones I want.

The frustration is that I can't send this document out to others with nearly the level of confidence I had back in Excel 2003 when the error NEVER happened.

bolo - Did you write all of your code in Excel 2007? Or did you start in 2003 and then move up? I am wondering if the errors would go away if I started over or at least stripped out all of the formatting code and reentered it based off of Excel 2007 macro code that I capture.

 

[bolo]

The code is written straight in Excel 2007. Like you i think i will have to wipe the whole sheet and apply the formatting i want. I have to do this for over 700 files, and the tables vary in size (both rows and columns so this is a pain)! It is just annoying that is happens for no reason that i can see. But once it happens in one spreadsheet, i stays in that spreadsheet. I have tried deleting the sheet and starting over but still the same error in the same (recreated) sheet. I do a far bit of inserting cells and i am wondering if this is causing a problem....