I have code that copies border formatting from one cell to a range. Its
been working fine. But I'm now getting a runtime error "Unable to set the
Weight property of the Border class" trying to copy an xlInsideVertical
border. The same error occurs trying to copy the LineStyle property.

I know that a common problem with the xlInsideVertical border is that it can
only be applied to a multi-column range. My code checks for this condition.
However in this case, the target range is B20:E20.

I went back and retested this code under XL97, XL2000, XL2002 and XL2003
(all versions include all of the latest MS updates). It works under XL97
but fails under all of the later releases.

If I manually open the workbook under XL97 and then examine the source
cell's border in a watch window it has a LineStyle of 11 and a Weight
of -4138 (this is the case where the copy works). If I repeat this
procedure under XL2003, LineStyle is 241 and Weight is 225!

I presume this difference is what's causing it to work under XL97 and fail
under later versions.

I've looked at all of the LineStyle constants and neither 11 or 241 is
defined! I don't know what to make of that?

Here's the code in question:

Private Sub CopyBorder(borderType As Integer, Target As Range, source As
Range)
Dim TargetBorder As Border
Dim SourceBorder As Border

If Not (borderType = xlInsideHorizontal And Target.Rows.Count < 2 Or _
borderType = xlInsideVertical And Target.Columns.Count < 2)
Then
Set TargetBorder = Target.Borders(borderType)
Set SourceBorder = source.Borders(borderType)

TargetBorder.Weight = SourceBorder.Weight
TargetBorder.LineStyle = SourceBorder.LineStyle
TargetBorder.color = SourceBorder.color
TargetBorder.ColorIndex = SourceBorder.ColorIndex
End If
End Sub

Again, its the assignment of Weight and LineStyle that fail. The assignment
of Color and ColorIndex work fine.

Any thoughts?

TIA,

josh

Hi Josh,
I can't really help with your problem except that I tried this code and
identified the weight property as the problem...if I comment out the
"Let .Weight =..." line then the borders are reproduced but the
weighting isn't....

Sub CopyBordersAgain()
Dim objBorder As Border, objCell As Range, intInc As Integer, rngTarget
As Range, _
rngSource As Range

Set rngTarget = ThisWorkbook.Sheets(3).Range("b2:E2")
Set rngSource = ThisWorkbook.Sheets(3).Range("B15")


For Each objCell In rngTarget.Cells
For intInc = 5 To 12
With objCell.Borders(intInc)
Let .ColorIndex = rngSource.Borders(intInc).ColorIndex
Let .LineStyle = rngSource.Borders(intInc).LineStyle
Let .Weight = rngSource.Borders(intInc).Weight
End With
Next intInc
Next objCell

End Sub


Hth,
Oli

Thanks Oli ... but your suggestion has a few problems:

First, there is the Weight problem you describe.

Second, performance. My routine can be called quite a bit in my application
and you're solution introduces two loops which I'm not too thrilled about.

Third, your solution can't handle InsideVertical and InsideHortizontal
borders since they are only applicable for multi-row/multi-column ranges
which you've eliminated.

Thanks for trying.

josh



"OJ" wrote in message
oups.com...
Hi Josh,
I can't really help with your problem except that I tried this code and
identified the weight property as the problem...if I comment out the
"Let .Weight =..." line then the borders are reproduced but the
weighting isn't....

Sub CopyBordersAgain()
Dim objBorder As Border, objCell As Range, intInc As Integer, rngTarget
As Range, _
rngSource As Range

Set rngTarget = ThisWorkbook.Sheets(3).Range("b2:E2")
Set rngSource = ThisWorkbook.Sheets(3).Range("B15")


For Each objCell In rngTarget.Cells
For intInc = 5 To 12
With objCell.Borders(intInc)
Let .ColorIndex = rngSource.Borders(intInc).ColorIndex
Let .LineStyle = rngSource.Borders(intInc).LineStyle
Let .Weight = rngSource.Borders(intInc).Weight
End With
Next intInc
Next objCell

End Sub


Hth,
Oli

Hi Josh,

Are you sure you are correctly applying your border properties, ie how is
borderType defined in the calling procedure.

Also are you sure you are reading those constants correctly, AFAIK there is
no value 11 for any of the LineStyles, in any version. In Object browser
search xllinestyle and examine all the corresponding constants My guess is
you are trying to copy the inside vertical from your single cell source,
which does not exist.
XlInsideVertical = 11 !!

You will need to loop properties for each border, unless all the properties
for each border are the same and, you source from a single cell or similar
size range, in which case you could do something like:

[d2:f6].Borders.LineStyle = [b2].Borders.LineStyle 'Weight & ColorIndex

If looping borders and applying same to each you could do:

bDoInsideVert = False
bDoInsideHoriz = False

For i = 7 To 12
If i = 11 And bDoInsideVert = False Then
ElseIf i = 12 And bDoInsideHoriz = False Then
Else
[d2:f6].Borders(i).LineStyle = [b2].Borders.LineStyle
[d2:f6].Borders(i).Weight = [b2].Borders(i).Weight
[d2:f6].Borders(i).ColorIndex = [b2].Borders(i).ColorIndex
End If
Next

To be ultra picky, you should change
borderType As Integer
to
borderType As Long

Regards,
Peter T



"Josh Sale" <jsale@tril dot cod wrote in message
...
I have code that copies border formatting from one cell to a range. Its
been working fine. But I'm now getting a runtime error "Unable to set the
Weight property of the Border class" trying to copy an xlInsideVertical
border. The same error occurs trying to copy the LineStyle property.

I know that a common problem with the xlInsideVertical border is that it
can
only be applied to a multi-column range. My code checks for this
condition.
However in this case, the target range is B20:E20.

I went back and retested this code under XL97, XL2000, XL2002 and XL2003
(all versions include all of the latest MS updates). It works under XL97
but fails under all of the later releases.

If I manually open the workbook under XL97 and then examine the source
cell's border in a watch window it has a LineStyle of 11 and a Weight
of -4138 (this is the case where the copy works). If I repeat this
procedure under XL2003, LineStyle is 241 and Weight is 225!

I presume this difference is what's causing it to work under XL97 and fail
under later versions.

I've looked at all of the LineStyle constants and neither 11 or 241 is
defined! I don't know what to make of that?

Here's the code in question:

Private Sub CopyBorder(borderType As Integer, Target As Range, source As
Range)
Dim TargetBorder As Border
Dim SourceBorder As Border

If Not (borderType = xlInsideHorizontal And Target.Rows.Count < 2 Or _
borderType = xlInsideVertical And Target.Columns.Count < 2)
Then
Set TargetBorder = Target.Borders(borderType)
Set SourceBorder = source.Borders(borderType)

TargetBorder.Weight = SourceBorder.Weight
TargetBorder.LineStyle = SourceBorder.LineStyle
TargetBorder.color = SourceBorder.color
TargetBorder.ColorIndex = SourceBorder.ColorIndex
End If
End Sub

Again, its the assignment of Weight and LineStyle that fail. The
assignment
of Color and ColorIndex work fine.

Any thoughts?

TIA,

josh