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

 

[MrKowz]

Check to make sure that you don't have any Worksheet_Change macros in the workbook that format.

 

[basstwo]

I do! Is that a problem? They worked in Excel 2003. Is there something I need to change to get them to work in Excel 2007?

 

[MrKowz]

Post the VBA code that is erroring for you, and please indicate which line it errors on, as well as what error message you get. These three things really help to speed up the debug process

 

[basstwo]

Here is the first few lines of one spot where the code is erroring. IT will error anytime that the code tries to change the format of the cell:

Private Sub OADPrimeTargetBox_LostFocus()
    Dim CompCol As String
    Application.ScreenUpdating = False
    ASeed_Values
    GV.APrimeBoxSel = OADPrimeTargetBox.ListIndex
    Range(Actos_Col_Let & AStartRow - 1 & ":" & AGeneric_Col_Let & AStartRow - 1).Font.ColorIndex = 1
    Range(Actos_Col_Let & AStartRow - 1 & ":" & AGeneric_Col_Let & AStartRow - 1).Font.FontStyle = "Regular"
    With Range(Actos_Col_Let & AStartRow - 1 & ":" & AGeneric_Col_Let & Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row)
        .Borders(xlEdgeLeft).LineStyle = xlLineStyleNone
        .Borders(xlEdgeTop).Weight = xlThin
        .Borders(xlEdgeBottom).Weight = xlThin
        .Borders(xlInsideHorizontal).Weight = xlThin
        .Borders(xlEdgeRight).LineStyle = xlLineStyleNone
        .Borders(xlInsideVertical).LineStyle = xlLineStyleNone
    End With

Here is the error: Run-time error '1004': Unable to set the LineStyle property of the Border class

It happens on that first line with .Borders (line 9).

 

[MrKowz]

Ahh - I don't know much about 2007, as I still use 2003... but I'm suspecting that the way you reference borders in 2007 has changed. Hopefully someone else here, who has used 2007, can help shed light on this subject.

 

[basstwo]

I will try digging a bit more around borders in Excel. Thanks!

[UPDATE]: Well, I created a new Excel 2007 workbook and created a macro to change borders. This only differences I could see between the 2007 macro-recording and my code were minor ('Borders' wasnt capitalized and 'xlLineStyleNone' became 'xlNone') I tried changing the 2007 macro code to the same syntax and then ran it to see if these changes would throw an error but they ran just fine. So I know these changes are not the problem.

Here is the 2007 macro code for comparison:

Sub borders()
'
' borders Macro
'

'
    Selection.borders(xlDiagonalDown).LineStyle = xlNone
    Selection.borders(xlDiagonalUp).LineStyle = xlNone
    Selection.borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.borders(xlInsideVertical).LineStyle = xlNone
    Selection.borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

 

[bolo]

Hey basstwo I have exactly the same problem! Have you got a solution yet? To add a bit more info my macro calcs area numbers and then formats to create a table. Certain cells then for some strange reason become unformatable, in code or manually.I have tried deleting the cell, copy and pasting but nothing changes with regards to this 'protected' behaviour. The only solution I have found is to clear ALL the cells of formatting. Something I don't want to do because I have other formatted cells on the sheet. There isn't much info regarding this in the net. I too am using excel 2007.

 

[bolo]

Hey basstwo I have exactly the same problem! Have you got a solution yet? To add a bit more info my macro calcs numbers and then formats to create a table. Certain cells then for some strange reason become unformatable, in code or manually.I have tried deleting the cell, copy and pasting but nothing changes with regards to this 'protected' behaviour. The only solution I have found is to clear ALL the cells in the whole of formatting. Something I don't want to do because I have other formatted cells on the sheet. There isn't much info regarding this in the net. I too am using excel 2007.

 

[basstwo]

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.