Trouble trying to format a cell when the sheet is unlocked

B

basstwo

New Member
Joined
Sep 21, 2010
Messages
10
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
 
Sort by date Sort by votes
**** i was hoping for a more elegant solution, and perhaps a reason why it happens. Oh well back to the drawing board.

Regards

Bolo

basstwo said:
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.
Click to expand...
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
bolo said:
**** i was hoping for a more elegant solution, and perhaps a reason why it happens. Oh well back to the drawing board.

Regards

Bolo
Click to expand...

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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
basstwo said:
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
Click to expand...

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....
 
Upvote 0
bolo said:
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....
Click to expand...

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

Thanks!

Bolo
 
Upvote 0
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.
 
Upvote 0
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....
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA help - Locking & unlocking on certain times of day
2
Replies
17
Views
460
dmt32
D
GMC The Macro Man
  • Question Question
Sorting a table in a protected sheet when some cells are locked -MACRO needed
Replies
3
Views
430
RoryA
RoryA
A
  • Solved
excel protected sheet - some cells are unlocked for user input getting cleared on click
Replies
3
Views
136
AgentBaadal
A
J
  • Question Question
Formatting error when switching from Mac to PC
Replies
6
Views
95
RoryA
RoryA
M
  • Solved
VBA to clear cell contents and conditional formatting but retain the formula
Replies
5
Views
479
MLC1980
M

Forum statistics

Threads
1,224,616
Messages
6,179,911
Members
452,949
Latest member
beartooth91

Share this page

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