Border Mystery?
 

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

Border Mystery?
 

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

Border Mystery?
 

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

Border Mystery?
 

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

Border Mystery?
 

Josh - On the basis of what you added you've reinforced my first guess "you
are trying to copy the inside vertical from your single cell source, which
does not exist " and I'll add a second:

In the versions in which your code works I "guess" your source is NOT a
single cell whereas in XL2003, where it fails, source IS a single cell.

Effectively you are doing this

Dim SourceBorder As Border
Dim source as range
Set source = mySingleCell
Set SourceBorder = source.borders(xlInsideVertical)

But a single cell doesn't have an inside vertical border, so when you try
and copy to your multi column target it will fail.

Think you need to apply same check that "source" has inside borders as you
have with "target"

Not sure why you are even using the Borders object variable. Having done
your checks for Insides in both source and target, simply:

target.Borders(borderType).Weight = source.Borders(borderType).Weight

Afraid my OE has taken upon itself to remove all attachments from posts in
ng's deeming them unsafe, so I can't see your screenshot of those strange
constants!

Regards,
Peter T

"Josh Sale" <jsale@tril dot cod wrote in message
...
Peter,

Please see my replied in context:

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

The calling procedure uses the Excel constants. For example:
CopyBorder xlInsideVertical, target, source
so they should be valid.

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 !!

I agree neither 11 or 241 correspond to any of the defined LineStyle
constants. However, as you (hopefully the NG can handle attachments) can
see from the attached screen shot (Excel 2003) the LineStyle is 241 (I got
the 11 under Excel 97 ... where the code works fine!). I can only assume
its just a conincidence that xlInsideVertical resolves to the same value
as
I get under Excel 97.


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

The routine is always called with the Source range set to a single cell.
So
I don't think looping should be necessary.


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

I suppose. However the range of all defined BorderIndex constants (5 to
12)
are easily contained in an integer. I think if this were the problem, the
code would fail 100% of the time. However, the caller of this routine
successively calls it (xlEdgeLeft, xlEdgeRight, etc) and all of those
calls
work. So far its only having trouble on xlInsideVertical.

Any other thoughts? Thanks.

josh


"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

Border Mystery?
 

Peter thanks for your followup. Let me try to respond ...

" Josh - On the basis of what you added you've reinforced my first guess
"you
are trying to copy the inside vertical from your single cell source, which
does not exist " and I'll add a second:

In the versions in which your code works I "guess" your source is NOT a
single cell whereas in XL2003, where it fails, source IS a single cell.

Both versions are definitely running against the identical data. The source
range is a single cell when testing with XL97 and XL2003.


Effectively you are doing this

Dim SourceBorder As Border
Dim source as range
Set source = mySingleCell
Set SourceBorder = source.borders(xlInsideVertical)

But a single cell doesn't have an inside vertical border, so when you try
and copy to your multi column target it will fail.

Think you need to apply same check that "source" has inside borders as you
have with "target"

I think you've put your finger on it here. I think I have an incorrect
mental model on how these inside borders work. I assumed that if the user
selects A1:C1 and formats those cells with inside vertical borders, that if
you then selected just cell B1 it would have an inside vertical border.

Experimenting right now I see that's not the case. B1 in fact has left and
right vertical borders and no inside border.

So given that my source will always be a single cell, I may as well just
skip trying to copy the two inside borders because by defintion they can
never be there.

Is that right?



Not sure why you are even using the Borders object variable. Having done
your checks for Insides in both source and target, simply:

target.Borders(borderType).Weight = source.Borders(borderType).Weight

The reason for the two Border object variables was (hopefully) improved
performance. Since I expect this routine to be called quite a bit, I was
trying to (in effect) have two With's going at once. I have to admit to not
timing the code so I don't know if it is in fact any faster.



Afraid my OE has taken upon itself to remove all attachments from posts in
ng's deeming them unsafe, so I can't see your screenshot of those strange
constants!

Regards,
Peter T

"Josh Sale" <jsale@tril dot cod wrote in message
...
Peter,

Please see my replied in context:

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

The calling procedure uses the Excel constants. For example:
CopyBorder xlInsideVertical, target, source
so they should be valid.

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 !!

I agree neither 11 or 241 correspond to any of the defined LineStyle
constants. However, as you (hopefully the NG can handle attachments) can
see from the attached screen shot (Excel 2003) the LineStyle is 241 (I
got
the 11 under Excel 97 ... where the code works fine!). I can only assume
its just a conincidence that xlInsideVertical resolves to the same value
as
I get under Excel 97.


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

The routine is always called with the Source range set to a single cell.
So
I don't think looping should be necessary.


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

I suppose. However the range of all defined BorderIndex constants (5 to
12)
are easily contained in an integer. I think if this were the problem,
the
code would fail 100% of the time. However, the caller of this routine
successively calls it (xlEdgeLeft, xlEdgeRight, etc) and all of those
calls
work. So far its only having trouble on xlInsideVertical.

Any other thoughts? Thanks.

josh


"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

Border Mystery?
 

This is second attempt to reply, what I posted a few hours ago seems to have
got lost in the stratosphere - annoying! If it turns up you want want to
read both. Following from memory of what I previously wrote:

Josh - this doesn't add up. Sending both single cell and xlInsideVertical to
your original function will fail in any version of XL.

Both versions are definitely running against the identical data. The
source
range is a single cell when testing with XL97 and XL2003.

Near the top of your function add
Debug.print borderType, source.address(0,0)
And compare failing and working versions

So given that my source will always be a single cell, I may as well just
skip trying to copy the two inside borders because by defintion they can
never be there.

Is that right?

Certainly re copying inside borders of a single cell. But what do you want
to do about the inside borders of target multicell range - apply attributes
from one of source's edges to insides, perhaps. Also what if user selects a
multicell source with inside borders - then do you want to copy those.

The reason for the two Border object variables was (hopefully) improved
performance. Since I expect this routine to be called quite a bit, I was
trying to (in effect) have two With's going at once. I have to admit to
not
timing the code so I don't know if it is in fact any faster.

When you say "called quite a bit" I assume you mean many times by the user
when he wants to copy borders. Wouldn't think even inefficient code would be
noticeably slow. FWIW reading cells is significantly faster than writing, so
I wouldn't worry too much about source. Possibly setting a reference to
target borders might speed up - I haven't tested.

If you want to copy individual borders, each with their own attributes, to
many areas at the same time you could do something like this:

Sub test()
Dim source As Range, target As Range, ra As Range
Dim arrBorders(7 To 10, 0 To 2)
Dim i As Long
Set source = Range("B3")
Set target = Range("C4:F6, H4:J10")

With source.Borders
For i = 7 To 10
arrBorders(i, 0) = .Item(i).LineStyle
arrBorders(i, 1) = .Item(i).Weight
arrBorders(i, 2) = .Item(i).ColorIndex
Next
End With

For Each ra In target.Areas
With ra
.Borders.LineStyle = arrBorders(7, 0)
.Borders.Weight = arrBorders(7, 1)
.Borders.ColorIndex = arrBorders(7, 2)
.Borders.LineStyle = xlNone
For i = 7 To 10
.Borders(i).LineStyle = arrBorders(i, 0)
.Borders(i).Weight = arrBorders(i, 1)
.Borders(i).ColorIndex = arrBorders(i, 2)
Next
If .Columns.Count 1 Then
'copy source Left to inside verticals ?
.Borders(11&).LineStyle = arrBorders(7, 0)
.Borders(11&).Weight = arrBorders(7, 1)
.Borders(11&).ColorIndex = arrBorders(7, 2)
End If

If .Rows.Count 1 Then
.Borders(12&).LineStyle = arrBorders(8, 0)
.Borders(12&).Weight = arrBorders(8, 1)
.Borders(12&).ColorIndex = arrBorders(8, 2)
End If
End With
Next
End Sub

As I said before - involves a loop unless you want to code individually for
each border, which amounts the to the same thing but with more code.

If you want to copy the same attributes from say source left border, to all
borders in target, then maybe:

with target
..borders.linestyle = source.borders(xledgeleft).linestyle
ditto weight & colorindex
end with

Another way might be to brute force your way through all borders, whether or
not they exist in either source or target

on error resume next
for i = 7 to 12
target.borders(i).linestyle = source.borders(xledgeleft).linestyle
etc
next
on error goto 0

Forcing like this is probably frowned on around here but short code to cater
for all scenarios, should be OK

Regards,
Peter T

"Josh Sale" <jsale@tril dot cod wrote in message
...
Peter thanks for your followup. Let me try to respond ...

" Josh - On the basis of what you added you've reinforced my first guess
"you
are trying to copy the inside vertical from your single cell source,
which
does not exist " and I'll add a second:

In the versions in which your code works I "guess" your source is NOT a
single cell whereas in XL2003, where it fails, source IS a single cell.

Both versions are definitely running against the identical data. The
source
range is a single cell when testing with XL97 and XL2003.


Effectively you are doing this

Dim SourceBorder As Border
Dim source as range
Set source = mySingleCell
Set SourceBorder = source.borders(xlInsideVertical)

But a single cell doesn't have an inside vertical border, so when you
try
and copy to your multi column target it will fail.

Think you need to apply same check that "source" has inside borders as
you
have with "target"

I think you've put your finger on it here. I think I have an incorrect
mental model on how these inside borders work. I assumed that if the user
selects A1:C1 and formats those cells with inside vertical borders, that
if
you then selected just cell B1 it would have an inside vertical border.

Experimenting right now I see that's not the case. B1 in fact has left
and
right vertical borders and no inside border.

So given that my source will always be a single cell, I may as well just
skip trying to copy the two inside borders because by defintion they can
never be there.

Is that right?



Not sure why you are even using the Borders object variable. Having done
your checks for Insides in both source and target, simply:

target.Borders(borderType).Weight = source.Borders(borderType).Weight

The reason for the two Border object variables was (hopefully) improved
performance. Since I expect this routine to be called quite a bit, I was
trying to (in effect) have two With's going at once. I have to admit to
not
timing the code so I don't know if it is in fact any faster.



Afraid my OE has taken upon itself to remove all attachments from posts
in
ng's deeming them unsafe, so I can't see your screenshot of those
strange
constants!

Regards,
Peter T

"Josh Sale" <jsale@tril dot cod wrote in message
...
Peter,

Please see my replied in context:

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

The calling procedure uses the Excel constants. For example:
CopyBorder xlInsideVertical, target, source
so they should be valid.

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 !!

I agree neither 11 or 241 correspond to any of the defined LineStyle
constants. However, as you (hopefully the NG can handle attachments)
can
see from the attached screen shot (Excel 2003) the LineStyle is 241 (I
got
the 11 under Excel 97 ... where the code works fine!). I can only
assume
its just a conincidence that xlInsideVertical resolves to the same
value
as
I get under Excel 97.


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

The routine is always called with the Source range set to a single
cell.
So
I don't think looping should be necessary.


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

I suppose. However the range of all defined BorderIndex constants (5
to
12)
are easily contained in an integer. I think if this were the problem,
the
code would fail 100% of the time. However, the caller of this routine
successively calls it (xlEdgeLeft, xlEdgeRight, etc) and all of those
calls
work. So far its only having trouble on xlInsideVertical.

Any other thoughts? Thanks.

josh


"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

Border Mystery?
 

Peter thanks for your persistence! The lost response remains lost!

I added the suggested Debug.Print to the code and ran it under XL97 and
XL2003 and the Debug.Print results were identical. I think XL97 is just
more forgiving in this case.

So I would modify your assertion to: Sending both single cell and
xlInsideVertical to your original function will fail in any version of XL
after XL2000.


The bottom line is I need to rethink how I'm going to handle the two inside
borders. I haven't bored you with the whole scope of my application but its
pretty big and complicated. I think you've given me the background on
borders I need to sort this out now.

Much appreciation!!!

josh




"Peter T" <peter_t@discussions wrote in message
...
This is second attempt to reply, what I posted a few hours ago seems to
have
got lost in the stratosphere - annoying! If it turns up you want want to
read both. Following from memory of what I previously wrote:

Josh - this doesn't add up. Sending both single cell and xlInsideVertical
to
your original function will fail in any version of XL.

Both versions are definitely running against the identical data. The
source
range is a single cell when testing with XL97 and XL2003.

Near the top of your function add
Debug.print borderType, source.address(0,0)
And compare failing and working versions

So given that my source will always be a single cell, I may as well just
skip trying to copy the two inside borders because by defintion they can
never be there.

Is that right?

Certainly re copying inside borders of a single cell. But what do you want
to do about the inside borders of target multicell range - apply
attributes
from one of source's edges to insides, perhaps. Also what if user selects
a
multicell source with inside borders - then do you want to copy those.

The reason for the two Border object variables was (hopefully) improved
performance. Since I expect this routine to be called quite a bit, I was
trying to (in effect) have two With's going at once. I have to admit to
not
timing the code so I don't know if it is in fact any faster.

When you say "called quite a bit" I assume you mean many times by the user
when he wants to copy borders. Wouldn't think even inefficient code would
be
noticeably slow. FWIW reading cells is significantly faster than writing,
so
I wouldn't worry too much about source. Possibly setting a reference to
target borders might speed up - I haven't tested.

If you want to copy individual borders, each with their own attributes, to
many areas at the same time you could do something like this:

Sub test()
Dim source As Range, target As Range, ra As Range
Dim arrBorders(7 To 10, 0 To 2)
Dim i As Long
Set source = Range("B3")
Set target = Range("C4:F6, H4:J10")

With source.Borders
For i = 7 To 10
arrBorders(i, 0) = .Item(i).LineStyle
arrBorders(i, 1) = .Item(i).Weight
arrBorders(i, 2) = .Item(i).ColorIndex
Next
End With

For Each ra In target.Areas
With ra
.Borders.LineStyle = arrBorders(7, 0)
.Borders.Weight = arrBorders(7, 1)
.Borders.ColorIndex = arrBorders(7, 2)
.Borders.LineStyle = xlNone
For i = 7 To 10
.Borders(i).LineStyle = arrBorders(i, 0)
.Borders(i).Weight = arrBorders(i, 1)
.Borders(i).ColorIndex = arrBorders(i, 2)
Next
If .Columns.Count 1 Then
'copy source Left to inside verticals ?
.Borders(11&).LineStyle = arrBorders(7, 0)
.Borders(11&).Weight = arrBorders(7, 1)
.Borders(11&).ColorIndex = arrBorders(7, 2)
End If

If .Rows.Count 1 Then
.Borders(12&).LineStyle = arrBorders(8, 0)
.Borders(12&).Weight = arrBorders(8, 1)
.Borders(12&).ColorIndex = arrBorders(8, 2)
End If
End With
Next
End Sub

As I said before - involves a loop unless you want to code individually
for
each border, which amounts the to the same thing but with more code.

If you want to copy the same attributes from say source left border, to
all
borders in target, then maybe:

with target
.borders.linestyle = source.borders(xledgeleft).linestyle
ditto weight & colorindex
end with

Another way might be to brute force your way through all borders, whether
or
not they exist in either source or target

on error resume next
for i = 7 to 12
target.borders(i).linestyle = source.borders(xledgeleft).linestyle
etc
next
on error goto 0

Forcing like this is probably frowned on around here but short code to
cater
for all scenarios, should be OK

Regards,
Peter T

"Josh Sale" <jsale@tril dot cod wrote in message
...
Peter thanks for your followup. Let me try to respond ...

" Josh - On the basis of what you added you've reinforced my first guess
"you
are trying to copy the inside vertical from your single cell source,
which
does not exist " and I'll add a second:

In the versions in which your code works I "guess" your source is NOT a
single cell whereas in XL2003, where it fails, source IS a single cell.

Both versions are definitely running against the identical data. The
source
range is a single cell when testing with XL97 and XL2003.


Effectively you are doing this

Dim SourceBorder As Border
Dim source as range
Set source = mySingleCell
Set SourceBorder = source.borders(xlInsideVertical)

But a single cell doesn't have an inside vertical border, so when you
try
and copy to your multi column target it will fail.

Think you need to apply same check that "source" has inside borders as
you
have with "target"

I think you've put your finger on it here. I think I have an incorrect
mental model on how these inside borders work. I assumed that if the
user
selects A1:C1 and formats those cells with inside vertical borders, that
if
you then selected just cell B1 it would have an inside vertical border.

Experimenting right now I see that's not the case. B1 in fact has left
and
right vertical borders and no inside border.

So given that my source will always be a single cell, I may as well just
skip trying to copy the two inside borders because by defintion they can
never be there.

Is that right?



Not sure why you are even using the Borders object variable. Having
done
your checks for Insides in both source and target, simply:

target.Borders(borderType).Weight = source.Borders(borderType).Weight

The reason for the two Border object variables was (hopefully) improved
performance. Since I expect this routine to be called quite a bit, I was
trying to (in effect) have two With's going at once. I have to admit to
not
timing the code so I don't know if it is in fact any faster.



Afraid my OE has taken upon itself to remove all attachments from posts
in
ng's deeming them unsafe, so I can't see your screenshot of those
strange
constants!

Regards,
Peter T

"Josh Sale" <jsale@tril dot cod wrote in message
...
Peter,

Please see my replied in context:

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

The calling procedure uses the Excel constants. For example:
CopyBorder xlInsideVertical, target, source
so they should be valid.

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 !!

I agree neither 11 or 241 correspond to any of the defined LineStyle
constants. However, as you (hopefully the NG can handle attachments)
can
see from the attached screen shot (Excel 2003) the LineStyle is 241 (I
got
the 11 under Excel 97 ... where the code works fine!). I can only
assume
its just a conincidence that xlInsideVertical resolves to the same
value
as
I get under Excel 97.


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

The routine is always called with the Source range set to a single
cell.
So
I don't think looping should be necessary.


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

I suppose. However the range of all defined BorderIndex constants (5
to
12)
are easily contained in an integer. I think if this were the problem,
the
code would fail 100% of the time. However, the caller of this routine
successively calls it (xlEdgeLeft, xlEdgeRight, etc) and all of those
calls
work. So far its only having trouble on xlInsideVertical.

Any other thoughts? Thanks.

josh


"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

Border Mystery?
 

Josh - Indeed I do have to modify my assertion (actually it wasn't quite as
strong as that). I really thought I had tested your code in both xl97 & xl2k
and it had failed in both. Just had another look, it still fails in xl2k but
strangely not in xl97. Also as you said, the inside vertical property for
linestyle of the single cell shows as 11 in xl97 and 10 in xl2k, despite
there being no such documented values for linestyle.

I'm surprised, normally with such things xl97 is more likely to fail than
later versions, I'm also surprised that it "can" work in xl97 (ie doesn't
fail) - logically it should fail. Apologies for casting doubt on what you
said. But it doesn't change my advice don't do it that way!

Regards,
Peter T

"Josh Sale" <jsale@tril dot cod wrote in message
...
Peter thanks for your persistence! The lost response remains lost!

I added the suggested Debug.Print to the code and ran it under XL97 and
XL2003 and the Debug.Print results were identical. I think XL97 is just
more forgiving in this case.

So I would modify your assertion to: Sending both single cell and
xlInsideVertical to your original function will fail in any version of XL
after XL2000.


The bottom line is I need to rethink how I'm going to handle the two
inside
borders. I haven't bored you with the whole scope of my application but
its
pretty big and complicated. I think you've given me the background on
borders I need to sort this out now.

Much appreciation!!!

josh




"Peter T" <peter_t@discussions wrote in message
...
This is second attempt to reply, what I posted a few hours ago seems to
have
got lost in the stratosphere - annoying! If it turns up you want want to
read both. Following from memory of what I previously wrote:

Josh - this doesn't add up. Sending both single cell and
xlInsideVertical
to
your original function will fail in any version of XL.

Both versions are definitely running against the identical data. The
source
range is a single cell when testing with XL97 and XL2003.

Near the top of your function add
Debug.print borderType, source.address(0,0)
And compare failing and working versions

So given that my source will always be a single cell, I may as well
just
skip trying to copy the two inside borders because by defintion they
can
never be there.

Is that right?

Certainly re copying inside borders of a single cell. But what do you
want
to do about the inside borders of target multicell range - apply
attributes
from one of source's edges to insides, perhaps. Also what if user
selects
a
multicell source with inside borders - then do you want to copy those.

The reason for the two Border object variables was (hopefully) improved
performance. Since I expect this routine to be called quite a bit, I
was
trying to (in effect) have two With's going at once. I have to admit
to
not
timing the code so I don't know if it is in fact any faster.

When you say "called quite a bit" I assume you mean many times by the
user
when he wants to copy borders. Wouldn't think even inefficient code
would
be
noticeably slow. FWIW reading cells is significantly faster than
writing,
so
I wouldn't worry too much about source. Possibly setting a reference to
target borders might speed up - I haven't tested.

If you want to copy individual borders, each with their own attributes,
to
many areas at the same time you could do something like this:

Sub test()
Dim source As Range, target As Range, ra As Range
Dim arrBorders(7 To 10, 0 To 2)
Dim i As Long
Set source = Range("B3")
Set target = Range("C4:F6, H4:J10")

With source.Borders
For i = 7 To 10
arrBorders(i, 0) = .Item(i).LineStyle
arrBorders(i, 1) = .Item(i).Weight
arrBorders(i, 2) = .Item(i).ColorIndex
Next
End With

For Each ra In target.Areas
With ra
.Borders.LineStyle = arrBorders(7, 0)
.Borders.Weight = arrBorders(7, 1)
.Borders.ColorIndex = arrBorders(7, 2)
.Borders.LineStyle = xlNone
For i = 7 To 10
.Borders(i).LineStyle = arrBorders(i, 0)
.Borders(i).Weight = arrBorders(i, 1)
.Borders(i).ColorIndex = arrBorders(i, 2)
Next
If .Columns.Count 1 Then
'copy source Left to inside verticals ?
.Borders(11&).LineStyle = arrBorders(7, 0)
.Borders(11&).Weight = arrBorders(7, 1)
.Borders(11&).ColorIndex = arrBorders(7, 2)
End If

If .Rows.Count 1 Then
.Borders(12&).LineStyle = arrBorders(8, 0)
.Borders(12&).Weight = arrBorders(8, 1)
.Borders(12&).ColorIndex = arrBorders(8, 2)
End If
End With
Next
End Sub

As I said before - involves a loop unless you want to code individually
for
each border, which amounts the to the same thing but with more code.

If you want to copy the same attributes from say source left border, to
all
borders in target, then maybe:

with target
.borders.linestyle = source.borders(xledgeleft).linestyle
ditto weight & colorindex
end with

Another way might be to brute force your way through all borders,
whether
or
not they exist in either source or target

on error resume next
for i = 7 to 12
target.borders(i).linestyle = source.borders(xledgeleft).linestyle
etc
next
on error goto 0

Forcing like this is probably frowned on around here but short code to
cater
for all scenarios, should be OK

Regards,
Peter T

"Josh Sale" <jsale@tril dot cod wrote in message
...
Peter thanks for your followup. Let me try to respond ...

" Josh - On the basis of what you added you've reinforced my first
guess
"you
are trying to copy the inside vertical from your single cell source,
which
does not exist " and I'll add a second:

In the versions in which your code works I "guess" your source is NOT
a
single cell whereas in XL2003, where it fails, source IS a single
cell.

Both versions are definitely running against the identical data. The
source
range is a single cell when testing with XL97 and XL2003.


Effectively you are doing this

Dim SourceBorder As Border
Dim source as range
Set source = mySingleCell
Set SourceBorder = source.borders(xlInsideVertical)

But a single cell doesn't have an inside vertical border, so when you
try
and copy to your multi column target it will fail.

Think you need to apply same check that "source" has inside borders
as
you
have with "target"

I think you've put your finger on it here. I think I have an incorrect
mental model on how these inside borders work. I assumed that if the
user
selects A1:C1 and formats those cells with inside vertical borders,
that
if
you then selected just cell B1 it would have an inside vertical border.

Experimenting right now I see that's not the case. B1 in fact has left
and
right vertical borders and no inside border.

So given that my source will always be a single cell, I may as well
just
skip trying to copy the two inside borders because by defintion they
can
never be there.

Is that right?



Not sure why you are even using the Borders object variable. Having
done
your checks for Insides in both source and target, simply:

target.Borders(borderType).Weight = source.Borders(borderType).Weight

The reason for the two Border object variables was (hopefully) improved
performance. Since I expect this routine to be called quite a bit, I
was
trying to (in effect) have two With's going at once. I have to admit
to
not
timing the code so I don't know if it is in fact any faster.



Afraid my OE has taken upon itself to remove all attachments from
posts
in
ng's deeming them unsafe, so I can't see your screenshot of those
strange
constants!

Regards,
Peter T

"Josh Sale" <jsale@tril dot cod wrote in message
...
Peter,

Please see my replied in context:

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

The calling procedure uses the Excel constants. For example:
CopyBorder xlInsideVertical, target, source
so they should be valid.

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 !!

I agree neither 11 or 241 correspond to any of the defined LineStyle
constants. However, as you (hopefully the NG can handle
attachments)
can
see from the attached screen shot (Excel 2003) the LineStyle is 241
(I
got
the 11 under Excel 97 ... where the code works fine!). I can only
assume
its just a conincidence that xlInsideVertical resolves to the same
value
as
I get under Excel 97.


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

The routine is always called with the Source range set to a single
cell.
So
I don't think looping should be necessary.


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

I suppose. However the range of all defined BorderIndex constants
(5
to
12)
are easily contained in an integer. I think if this were the
problem,
the
code would fail 100% of the time. However, the caller of this
routine
successively calls it (xlEdgeLeft, xlEdgeRight, etc) and all of
those
calls
work. So far its only having trouble on xlInsideVertical.

Any other thoughts? Thanks.

josh


"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

Border Mystery?
 

Thanks again Peter. Armed with my new understanding, I've got thinks all
worked out now (famous last words!).

josh



"Peter T" <peter_t@discussions wrote in message
...
Josh - Indeed I do have to modify my assertion (actually it wasn't quite
as
strong as that). I really thought I had tested your code in both xl97 &
xl2k
and it had failed in both. Just had another look, it still fails in xl2k
but
strangely not in xl97. Also as you said, the inside vertical property for
linestyle of the single cell shows as 11 in xl97 and 10 in xl2k, despite
there being no such documented values for linestyle.

I'm surprised, normally with such things xl97 is more likely to fail than
later versions, I'm also surprised that it "can" work in xl97 (ie doesn't
fail) - logically it should fail. Apologies for casting doubt on what you
said. But it doesn't change my advice don't do it that way!

Regards,
Peter T

"Josh Sale" <jsale@tril dot cod wrote in message
...
Peter thanks for your persistence! The lost response remains lost!

I added the suggested Debug.Print to the code and ran it under XL97 and
XL2003 and the Debug.Print results were identical. I think XL97 is just
more forgiving in this case.

So I would modify your assertion to: Sending both single cell and
xlInsideVertical to your original function will fail in any version of XL
after XL2000.


The bottom line is I need to rethink how I'm going to handle the two
inside
borders. I haven't bored you with the whole scope of my application but
its
pretty big and complicated. I think you've given me the background on
borders I need to sort this out now.

Much appreciation!!!

josh




"Peter T" <peter_t@discussions wrote in message
...
This is second attempt to reply, what I posted a few hours ago seems to
have
got lost in the stratosphere - annoying! If it turns up you want want
to
read both. Following from memory of what I previously wrote:

Josh - this doesn't add up. Sending both single cell and
xlInsideVertical
to
your original function will fail in any version of XL.

Both versions are definitely running against the identical data. The
source
range is a single cell when testing with XL97 and XL2003.

Near the top of your function add
Debug.print borderType, source.address(0,0)
And compare failing and working versions

So given that my source will always be a single cell, I may as well
just
skip trying to copy the two inside borders because by defintion they
can
never be there.

Is that right?

Certainly re copying inside borders of a single cell. But what do you
want
to do about the inside borders of target multicell range - apply
attributes
from one of source's edges to insides, perhaps. Also what if user
selects
a
multicell source with inside borders - then do you want to copy those.

The reason for the two Border object variables was (hopefully)
improved
performance. Since I expect this routine to be called quite a bit, I
was
trying to (in effect) have two With's going at once. I have to admit
to
not
timing the code so I don't know if it is in fact any faster.

When you say "called quite a bit" I assume you mean many times by the
user
when he wants to copy borders. Wouldn't think even inefficient code
would
be
noticeably slow. FWIW reading cells is significantly faster than
writing,
so
I wouldn't worry too much about source. Possibly setting a reference to
target borders might speed up - I haven't tested.

If you want to copy individual borders, each with their own attributes,
to
many areas at the same time you could do something like this:

Sub test()
Dim source As Range, target As Range, ra As Range
Dim arrBorders(7 To 10, 0 To 2)
Dim i As Long
Set source = Range("B3")
Set target = Range("C4:F6, H4:J10")

With source.Borders
For i = 7 To 10
arrBorders(i, 0) = .Item(i).LineStyle
arrBorders(i, 1) = .Item(i).Weight
arrBorders(i, 2) = .Item(i).ColorIndex
Next
End With

For Each ra In target.Areas
With ra
.Borders.LineStyle = arrBorders(7, 0)
.Borders.Weight = arrBorders(7, 1)
.Borders.ColorIndex = arrBorders(7, 2)
.Borders.LineStyle = xlNone
For i = 7 To 10
.Borders(i).LineStyle = arrBorders(i, 0)
.Borders(i).Weight = arrBorders(i, 1)
.Borders(i).ColorIndex = arrBorders(i, 2)
Next
If .Columns.Count 1 Then
'copy source Left to inside verticals ?
.Borders(11&).LineStyle = arrBorders(7, 0)
.Borders(11&).Weight = arrBorders(7, 1)
.Borders(11&).ColorIndex = arrBorders(7, 2)
End If

If .Rows.Count 1 Then
.Borders(12&).LineStyle = arrBorders(8, 0)
.Borders(12&).Weight = arrBorders(8, 1)
.Borders(12&).ColorIndex = arrBorders(8, 2)
End If
End With
Next
End Sub

As I said before - involves a loop unless you want to code individually
for
each border, which amounts the to the same thing but with more code.

If you want to copy the same attributes from say source left border, to
all
borders in target, then maybe:

with target
.borders.linestyle = source.borders(xledgeleft).linestyle
ditto weight & colorindex
end with

Another way might be to brute force your way through all borders,
whether
or
not they exist in either source or target

on error resume next
for i = 7 to 12
target.borders(i).linestyle = source.borders(xledgeleft).linestyle
etc
next
on error goto 0

Forcing like this is probably frowned on around here but short code to
cater
for all scenarios, should be OK

Regards,
Peter T

"Josh Sale" <jsale@tril dot cod wrote in message
...
Peter thanks for your followup. Let me try to respond ...

" Josh - On the basis of what you added you've reinforced my first
guess
"you
are trying to copy the inside vertical from your single cell source,
which
does not exist " and I'll add a second:

In the versions in which your code works I "guess" your source is
NOT
a
single cell whereas in XL2003, where it fails, source IS a single
cell.

Both versions are definitely running against the identical data. The
source
range is a single cell when testing with XL97 and XL2003.


Effectively you are doing this

Dim SourceBorder As Border
Dim source as range
Set source = mySingleCell
Set SourceBorder = source.borders(xlInsideVertical)

But a single cell doesn't have an inside vertical border, so when
you
try
and copy to your multi column target it will fail.

Think you need to apply same check that "source" has inside borders
as
you
have with "target"

I think you've put your finger on it here. I think I have an
incorrect
mental model on how these inside borders work. I assumed that if the
user
selects A1:C1 and formats those cells with inside vertical borders,
that
if
you then selected just cell B1 it would have an inside vertical
border.

Experimenting right now I see that's not the case. B1 in fact has
left
and
right vertical borders and no inside border.

So given that my source will always be a single cell, I may as well
just
skip trying to copy the two inside borders because by defintion they
can
never be there.

Is that right?



Not sure why you are even using the Borders object variable. Having
done
your checks for Insides in both source and target, simply:

target.Borders(borderType).Weight =
source.Borders(borderType).Weight

The reason for the two Border object variables was (hopefully)
improved
performance. Since I expect this routine to be called quite a bit, I
was
trying to (in effect) have two With's going at once. I have to admit
to
not
timing the code so I don't know if it is in fact any faster.



Afraid my OE has taken upon itself to remove all attachments from
posts
in
ng's deeming them unsafe, so I can't see your screenshot of those
strange
constants!

Regards,
Peter T

"Josh Sale" <jsale@tril dot cod wrote in message
...
Peter,

Please see my replied in context:

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

The calling procedure uses the Excel constants. For example:
CopyBorder xlInsideVertical, target, source
so they should be valid.

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 !!

I agree neither 11 or 241 correspond to any of the defined
LineStyle
constants. However, as you (hopefully the NG can handle
attachments)
can
see from the attached screen shot (Excel 2003) the LineStyle is 241
(I
got
the 11 under Excel 97 ... where the code works fine!). I can only
assume
its just a conincidence that xlInsideVertical resolves to the same
value
as
I get under Excel 97.


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

The routine is always called with the Source range set to a single
cell.
So
I don't think looping should be necessary.


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

I suppose. However the range of all defined BorderIndex constants
(5
to
12)
are easily contained in an integer. I think if this were the
problem,
the
code would fail 100% of the time. However, the caller of this
routine
successively calls it (xlEdgeLeft, xlEdgeRight, etc) and all of
those
calls
work. So far its only having trouble on xlInsideVertical.

Any other thoughts? Thanks.

josh


"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