xlobj.selection borders malfunction

[ragtopcaddy via AccessMonster.com]

I am trying to set the interior vertical borders for a range. The range has
links to a pivot table above it on the same sheet. Depending on the column
and the value in the cell, different formatting will take place. If the cell
doesn't contain the string, "Total", then it should have no horizontal
borders, but should have vertical borders. The activeworkbook is from a
template saved under a new workbook name. The range with the linked cells has
no borders or colors to start with. The borders, colors, and bold font are
added depending on the contents of cells in columns A and F. Here's the code:

Select Case c.Column
Case 1, 6
xlObj.Range(xlObj.Selection, xlObj.Selection.End
(xlToRight)).Select
If c.Value = 0 Then
c.Value = Null
With xlObj.Selection
**The code breaks down on the next line with error
'1004' Application-defined..., etc.**
With .Borders(xlVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Interior
.ColorIndex = xlNone
End With
End With
ElseIf Right(c.Value, 5) = "Total" Then
With xlObj.Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
If Left(c.Value, 5) = "Grand" Then
With xlObj.Selection
With .Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
.Font.Bold = True
End With
xlObj.Range(c.Offset(1, 0).Address & ":C115").
Activate
With xlObj.Selection
.Borders.LineStyle = xlNone
.ClearContents
End With
Else
With xlObj.Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
End If
(other cases follow)

Any insight would be appreciated. I've tried all kinds of fixes.

 

[Jeanette Cunningham]

Bill,
it would be worthwhile to ask this question in the excel group where you are
more likely to find people who have done this before.

Jeanette Cunningham

 

[ragtopcaddy via AccessMonster.com]

Hi Jeanette,

Yes, that's the standard suggestion in this workgroup, however this
particular question has no relevance to the Excel group. The code, with just
one modification (the removal of the xlobj and all the periods before
Selection, etc.) will run perfectly in Excel. The problem is that having
modified it to effectively use the xlObj in Access, it fails. So this is
really an Access question, not an Excel question, as it only fails in Access.

Regards,

Bill

Bill,
it would be worthwhile to ask this question in the excel group where you are
more likely to find people who have done this before.

Jeanette Cunningham

I am trying to set the interior vertical borders for a range. The range has
links to a pivot table above it on the same sheet. Depending on the column

[quoted text clipped - 56 lines]

Any insight would be appreciated. I've tried all kinds of fixes.

 

[Stefan Hoffmann]

hi,

With xlObj.Selection
**The code breaks down on the next line with error
'1004' Application-defined..., etc.**
With .Borders(xlVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Don't use cascaded With-blocks. Also try to avoid using selections as
they may be invalid, e.g.:

Instead of

xlObj.Range(..).Select
xlObj.Selection.Borders(xlVertical).LineStyle = xlContinuous

use

xlObj.Range(..).Borders(xlVertical).LineStyle = xlContinuous


mfG
--> stefan <--