Unable to set some Border Weights after upgrading to Vista & Excel 2007

[MikeRDar]

Aloha... our office recently "upgraded" to Vista, including moving from MS Office 2003 to 2007. This code, which simply formats two ranges with various border and font settings, stopped working.

Specifically, when trying to set the border weights for the range "rngHoriz", they all give the '1004' error "Unable to set the Weight property of the Border class."

Things I've already determined...
The rngVert section works normally, no problems, with the same code.
It doesn't matter whether I run rngVert or rngHoriz first... rngHoriz always has the error.
The error occurs regardless of which border constant I use first (top, left, etc).
The rngHoriz is being created properly as far as I can tell, the formulas in the definition are correct and functioned normally until the upgrade to 2007.

***********************************************************
'Add all appropriate borders and adjust font sizes
'*****************************************
Dim rngVert As Range 'the vertical portion of the chart
Dim rngHoriz As Range 'the horizontal axis of the chart

Set rngVert = Worksheets("Monthly").Range(Cells(intC130ACstart - 2, intFirstDay), Cells(intCOMMSend + 2, intLastDay))
Set rngHoriz = Worksheets("Monthly").Range(Cells(intC130ACstart, 1), Cells(intCOMMSend, intLastDay + 4))

'set font sizes
rngHoriz.Font.Size = 9
rngVert.Font.Size = 6.5

'set alignment
rngHoriz.HorizontalAlignment = xlLeft
rngVert.HorizontalAlignment = xlCenter

'set the small weight grid on all boxes
rngVert.Borders.LineStyle = xlContinuous
rngHoriz.Borders.LineStyle = xlContinuous

'put the med weight grid around the borders
With rngVert
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
End With

With rngHoriz
.Borders(xlEdgeTop).Weight = xlMedium     <------ERROR OCCURS HERE, BUT WILL OCCUR ON WHICHEVER EDGE IS LISTED FIRST
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
End With

Thanks in advance for any and all help. FYI, I am not a professional programmer, I just took enough programming and VB in particular during college to help with stuff like this around my office. So use small words.

 

[MikeRDar]

BUMP...

Man this board moves fast, this was on page 4 by the end of the day!

Can anyone lend a hand? It's very simple code, seems to be a new glitch with VBA 2007...

 

[Norie]

I've only just had a quick look at the code and I don't think this is a version issue.

The first problem I see is that in this section of code Cells is unqualified.

***********************************************************
'Add all appropriate borders and adjust font sizes
'*****************************************
Dim rngVert As Range 'the vertical portion of the chart
Dim rngHoriz As Range 'the horizontal axis of the chart

Set rngVert = Worksheets("Monthly").Range(Cells(intC130ACstart - 2, intFirstDay), Cells(intCOMMSend + 2, intLastDay))
Set rngHoriz = Worksheets("Monthly").Range(Cells(intC130ACstart, 1), Cells(intCOMMSend, intLastDay + 4))

Also I don't think that's all the code - for example where/when are is intC130ACstart being declared/given a value.

 

[MikeRDar]

Norie, thanks for the response
Only reason I blamed the version is that it worked fine for the past year under MS Office 2003...

When you say Cells is unqualified, do you mean I need to point to the worksheet first? I'll try running it with ActiveSheet.Cells(..., but I think ActiveSheet is the default when nothing is given, and because this routine is triggered by a button on the correct sheet, I'm not too worried about it being run from another sheet accidentally. Also, the rngVert section is working without any problems.

I'll copy more of the code and paste here shortly, but because the original version of this was written by a non-programmer, it's basically one LONG routine, pretty messy, no subs. I'll try to select the sections pertinent to the range variables.

I can tell you that I put a watch on all of the cell variables and stepped through, confirming that they were calculating properly and the rngHoriz is about 40x40.

Thanks again...

 

[Norie]

Do not use ActiveSheet, don't use Activeanything.

If you want to refer to an object, eg worksheet, workbook, range etc, refer to it directly.

If you don't quite understand what I mean I'll post something back later - it could just involve a simple With End With structure.