Formatting a Pivot Table
 

I posted this message before but didn't resolve it. I have a pivot table I
am trying to format. The sheet is unprotected and the code fails with an
error

"Unable to set the linestyle property of the border class"

It does however work correctly on other worksheets in the same workbook.

Dim rng As Range

Set rng = ActiveSheet.Range("A5:B600")
rng.Interior.ColorIndex = xlNone
rng.Borders(xlDiagonalDown).LineStyle = xlNone ' Error occurs here
rng.Borders(xlDiagonalUp).LineStyle = xlNone

Formatting a Pivot Table
 

Hi NevilleT-

I can't reproduce your error; the code works fine on a test pivot table (as
you've described for your other sheets). I'd suggest converting your code as
follows to report the cell address where the error occurs; that might provide
us with a clue about the cause. Does the error occur when formatting all
cells or just in a particular column or row? Use Ctrl-Break if to stop this
procedure if all cells seem to be causing the error (so you don't have to
respond to ~1200 error messages...).

Sub NevilleT()

Set rng = ActiveSheet.Range("A5:B600")

For Each cel In rng
On Error GoTo errorReport
cel.Interior.ColorIndex = xlNone
cel.Borders(xlDiagonalDown).LineStyle = xlNone ' Error occurs here
cel.Borders(xlDiagonalUp).LineStyle = xlNone
On Error GoTo 0
nextCel:
Next 'cel

normalExit:
Exit Sub

errorReport:
MsgBox "An error occurred when formatting cell " & cel.Address
Resume nextCel

End Sub

----
Jay




"NevilleT" wrote:

I posted this message before but didn't resolve it. I have a pivot table I
am trying to format. The sheet is unprotected and the code fails with an
error

"Unable to set the linestyle property of the border class"

It does however work correctly on other worksheets in the same workbook.

Dim rng As Range

Set rng = ActiveSheet.Range("A5:B600")
rng.Interior.ColorIndex = xlNone
rng.Borders(xlDiagonalDown).LineStyle = xlNone ' Error occurs here
rng.Borders(xlDiagonalUp).LineStyle = xlNone

Formatting a Pivot Table
 

Thanks a lot Jay. That is getting closer to the truth. The error occurs
only on column A (every cell). The pivot table is tracking resources
allocated to project. It has col A "Role", col B "Name", col C "Project" Col
D "Jan 07", Col E "Feb O7" etc. You can collapse the table to look at a
single role, a person within the role, or expand it fully to display each
combination of role, name and project. Could it be something to do with the
fact that you can expand or hide the column?

"Jay" wrote:

Hi NevilleT-

I can't reproduce your error; the code works fine on a test pivot table (as
you've described for your other sheets). I'd suggest converting your code as
follows to report the cell address where the error occurs; that might provide
us with a clue about the cause. Does the error occur when formatting all
cells or just in a particular column or row? Use Ctrl-Break if to stop this
procedure if all cells seem to be causing the error (so you don't have to
respond to ~1200 error messages...).

Sub NevilleT()

Set rng = ActiveSheet.Range("A5:B600")

For Each cel In rng
On Error GoTo errorReport
cel.Interior.ColorIndex = xlNone
cel.Borders(xlDiagonalDown).LineStyle = xlNone ' Error occurs here
cel.Borders(xlDiagonalUp).LineStyle = xlNone
On Error GoTo 0
nextCel:
Next 'cel

normalExit:
Exit Sub

errorReport:
MsgBox "An error occurred when formatting cell " & cel.Address
Resume nextCel

End Sub

----
Jay




"NevilleT" wrote:

I posted this message before but didn't resolve it. I have a pivot table I
am trying to format. The sheet is unprotected and the code fails with an
error

"Unable to set the linestyle property of the border class"

It does however work correctly on other worksheets in the same workbook.

Dim rng As Range

Set rng = ActiveSheet.Range("A5:B600")
rng.Interior.ColorIndex = xlNone
rng.Borders(xlDiagonalDown).LineStyle = xlNone ' Error occurs here
rng.Borders(xlDiagonalUp).LineStyle = xlNone

Formatting a Pivot Table
 

Hi

Are you sure you didn't protect any cells for changes?

Leung

"NevilleT" wrote:

I posted this message before but didn't resolve it. I have a pivot table I
am trying to format. The sheet is unprotected and the code fails with an
error

"Unable to set the linestyle property of the border class"

It does however work correctly on other worksheets in the same workbook.

Dim rng As Range

Set rng = ActiveSheet.Range("A5:B600")
rng.Interior.ColorIndex = xlNone
rng.Borders(xlDiagonalDown).LineStyle = xlNone ' Error occurs here
rng.Borders(xlDiagonalUp).LineStyle = xlNone

Formatting a Pivot Table
 

Hidden and expanded rows and columns don't seem to have any adverse affect in
my test workbook. I hid detail and also tried various combinations of
grouping and collapsing the groups. I ran all tests on both column-dominated
and row-dominated pivot tables. The linestyles are set with no errors.

Can you manually adjust the linestyles in Column A cells? Using Excel's
menu, try Format, Cells, Borders. Set a diagonal to visible and click OK.
Then try to clear it using the same menu maneuever and see what happens.

---
Jay


"NevilleT" wrote:

Thanks a lot Jay. That is getting closer to the truth. The error occurs
only on column A (every cell). The pivot table is tracking resources
allocated to project. It has col A "Role", col B "Name", col C "Project" Col
D "Jan 07", Col E "Feb O7" etc. You can collapse the table to look at a
single role, a person within the role, or expand it fully to display each
combination of role, name and project. Could it be something to do with the
fact that you can expand or hide the column?

"Jay" wrote:

Hi NevilleT-

I can't reproduce your error; the code works fine on a test pivot table (as
you've described for your other sheets). I'd suggest converting your code as
follows to report the cell address where the error occurs; that might provide
us with a clue about the cause. Does the error occur when formatting all
cells or just in a particular column or row? Use Ctrl-Break if to stop this
procedure if all cells seem to be causing the error (so you don't have to
respond to ~1200 error messages...).

Sub NevilleT()

Set rng = ActiveSheet.Range("A5:B600")

For Each cel In rng
On Error GoTo errorReport
cel.Interior.ColorIndex = xlNone
cel.Borders(xlDiagonalDown).LineStyle = xlNone ' Error occurs here
cel.Borders(xlDiagonalUp).LineStyle = xlNone
On Error GoTo 0
nextCel:
Next 'cel

normalExit:
Exit Sub

errorReport:
MsgBox "An error occurred when formatting cell " & cel.Address
Resume nextCel

End Sub

----
Jay




"NevilleT" wrote:

I posted this message before but didn't resolve it. I have a pivot table I
am trying to format. The sheet is unprotected and the code fails with an
error

"Unable to set the linestyle property of the border class"

It does however work correctly on other worksheets in the same workbook.

Dim rng As Range

Set rng = ActiveSheet.Range("A5:B600")
rng.Interior.ColorIndex = xlNone
rng.Borders(xlDiagonalDown).LineStyle = xlNone ' Error occurs here
rng.Borders(xlDiagonalUp).LineStyle = xlNone

Formatting a Pivot Table
 

Hi Guys

I am using Excel 2007. I tried to format and when I click Format, Format
Cells, I don't get a menu. When I click somewhere else, I get a menu. I am
not an Excel expert but am reasonably capable with VBA using Access. I am
certainly struggling with this one.

"Leung" wrote:

Hi

Are you sure you didn't protect any cells for changes?

Leung

"NevilleT" wrote:

I posted this message before but didn't resolve it. I have a pivot table I
am trying to format. The sheet is unprotected and the code fails with an
error

"Unable to set the linestyle property of the border class"

It does however work correctly on other worksheets in the same workbook.

Dim rng As Range

Set rng = ActiveSheet.Range("A5:B600")
rng.Interior.ColorIndex = xlNone
rng.Borders(xlDiagonalDown).LineStyle = xlNone ' Error occurs here
rng.Borders(xlDiagonalUp).LineStyle = xlNone

Formatting a Pivot Table
 

Hi NevilleT -

I don't use XL2007, but its menus are more context sensitive than prior
versions. So, not seeing a format menu when a cell is selected strongly
suggests that there may be some protection lurking around.

You mentioned earlier that there was no protection, but we need to reconfirm
that that is the case. Can you confirm that the cell is not protected
before we proceed ?

--
Jay

"NevilleT" wrote:

Hi Guys

I am using Excel 2007. I tried to format and when I click Format, Format
Cells, I don't get a menu. When I click somewhere else, I get a menu. I am
not an Excel expert but am reasonably capable with VBA using Access. I am
certainly struggling with this one.

"Leung" wrote:

Hi

Are you sure you didn't protect any cells for changes?

Leung

"NevilleT" wrote:

I posted this message before but didn't resolve it. I have a pivot table I
am trying to format. The sheet is unprotected and the code fails with an
error

"Unable to set the linestyle property of the border class"

It does however work correctly on other worksheets in the same workbook.

Dim rng As Range

Set rng = ActiveSheet.Range("A5:B600")
rng.Interior.ColorIndex = xlNone
rng.Borders(xlDiagonalDown).LineStyle = xlNone ' Error occurs here
rng.Borders(xlDiagonalUp).LineStyle = xlNone

Formatting a Pivot Table
 

If I can't get to "Format Cells", how do I tell if the cell is protected.
Sorry but I am not an Excel expert. The worksheet does seem to be
unprotected.

"Jay" wrote:

Hi NevilleT -

I don't use XL2007, but its menus are more context sensitive than prior
versions. So, not seeing a format menu when a cell is selected strongly
suggests that there may be some protection lurking around.

You mentioned earlier that there was no protection, but we need to reconfirm
that that is the case. Can you confirm that the cell is not protected
before we proceed ?

--
Jay

"NevilleT" wrote:

Hi Guys

I am using Excel 2007. I tried to format and when I click Format, Format
Cells, I don't get a menu. When I click somewhere else, I get a menu. I am
not an Excel expert but am reasonably capable with VBA using Access. I am
certainly struggling with this one.

"Leung" wrote:

Hi

Are you sure you didn't protect any cells for changes?

Leung

"NevilleT" wrote:

I posted this message before but didn't resolve it. I have a pivot table I
am trying to format. The sheet is unprotected and the code fails with an
error

"Unable to set the linestyle property of the border class"

It does however work correctly on other worksheets in the same workbook.

Dim rng As Range

Set rng = ActiveSheet.Range("A5:B600")
rng.Interior.ColorIndex = xlNone
rng.Borders(xlDiagonalDown).LineStyle = xlNone ' Error occurs here
rng.Borders(xlDiagonalUp).LineStyle = xlNone

Formatting a Pivot Table
 

Good question Neville. I expected too much of Excel 2007...

At this point, I'd suggest remanufacturing the Pivot Table from scratch and
running your procedure on the new Pivot Table before doing anything else to
it. We'll evaluate your procedure on a 'virgin' pivot table.
---
Jay



"NevilleT" wrote:

If I can't get to "Format Cells", how do I tell if the cell is protected.
Sorry but I am not an Excel expert. The worksheet does seem to be
unprotected.

"Jay" wrote:

Hi NevilleT -

I don't use XL2007, but its menus are more context sensitive than prior
versions. So, not seeing a format menu when a cell is selected strongly
suggests that there may be some protection lurking around.

You mentioned earlier that there was no protection, but we need to reconfirm
that that is the case. Can you confirm that the cell is not protected
before we proceed ?

--
Jay

"NevilleT" wrote:

Hi Guys

I am using Excel 2007. I tried to format and when I click Format, Format
Cells, I don't get a menu. When I click somewhere else, I get a menu. I am
not an Excel expert but am reasonably capable with VBA using Access. I am
certainly struggling with this one.

"Leung" wrote:

Hi

Are you sure you didn't protect any cells for changes?

Leung

"NevilleT" wrote:

I posted this message before but didn't resolve it. I have a pivot table I
am trying to format. The sheet is unprotected and the code fails with an
error

"Unable to set the linestyle property of the border class"

It does however work correctly on other worksheets in the same workbook.

Dim rng As Range

Set rng = ActiveSheet.Range("A5:B600")
rng.Interior.ColorIndex = xlNone
rng.Borders(xlDiagonalDown).LineStyle = xlNone ' Error occurs here
rng.Borders(xlDiagonalUp).LineStyle = xlNone

Formatting a Pivot Table
 

Thanks Jay. I know Access 2007 has a few "undocumented features" with pivot
tables so perhaps Excel does too. At this point I may try recreating the
whole thing in Excel 2003.
I was working on a program that a client inherited and which I did some
major mods to a few years back. They needed a few more changes this week
which had little impact on the pivot table other than to extend the range.
The code was working up to the point where I extended the range and converted
to 2007. Maybe I can get it working in 2003. Thanks for all your help.



"Jay" wrote:

Good question Neville. I expected too much of Excel 2007...

At this point, I'd suggest remanufacturing the Pivot Table from scratch and
running your procedure on the new Pivot Table before doing anything else to
it. We'll evaluate your procedure on a 'virgin' pivot table.
---
Jay



"NevilleT" wrote:

If I can't get to "Format Cells", how do I tell if the cell is protected.
Sorry but I am not an Excel expert. The worksheet does seem to be
unprotected.

"Jay" wrote:

Hi NevilleT -

I don't use XL2007, but its menus are more context sensitive than prior
versions. So, not seeing a format menu when a cell is selected strongly
suggests that there may be some protection lurking around.

You mentioned earlier that there was no protection, but we need to reconfirm
that that is the case. Can you confirm that the cell is not protected
before we proceed ?

--
Jay

"NevilleT" wrote:

Hi Guys

I am using Excel 2007. I tried to format and when I click Format, Format
Cells, I don't get a menu. When I click somewhere else, I get a menu. I am
not an Excel expert but am reasonably capable with VBA using Access. I am
certainly struggling with this one.

"Leung" wrote:

Hi

Are you sure you didn't protect any cells for changes?

Leung

"NevilleT" wrote:

I posted this message before but didn't resolve it. I have a pivot table I
am trying to format. The sheet is unprotected and the code fails with an
error

"Unable to set the linestyle property of the border class"

It does however work correctly on other worksheets in the same workbook.

Dim rng As Range

Set rng = ActiveSheet.Range("A5:B600")
rng.Interior.ColorIndex = xlNone
rng.Borders(xlDiagonalDown).LineStyle = xlNone ' Error occurs here
rng.Borders(xlDiagonalUp).LineStyle = xlNone

Formatting a Pivot Table
 

Hi NevilleT -

Sorry we couldn't solve the problem directly. It sounds like you chose your
updates cleanly and carefully; they weren't anything out of the ordinary and
you wouldn't expect them to lead to the VBA error. Hopefully, the
reconstruction will shed some light on the problem.

I'll switch to monitoring this thread via 'email notification' should you
like to pick up where we left off.

---
Jay

"NevilleT" wrote:

Thanks Jay. I know Access 2007 has a few "undocumented features" with pivot
tables so perhaps Excel does too. At this point I may try recreating the
whole thing in Excel 2003.
I was working on a program that a client inherited and which I did some
major mods to a few years back. They needed a few more changes this week
which had little impact on the pivot table other than to extend the range.
The code was working up to the point where I extended the range and converted
to 2007. Maybe I can get it working in 2003. Thanks for all your help.



"Jay" wrote:

Good question Neville. I expected too much of Excel 2007...

At this point, I'd suggest remanufacturing the Pivot Table from scratch and
running your procedure on the new Pivot Table before doing anything else to
it. We'll evaluate your procedure on a 'virgin' pivot table.
---
Jay



"NevilleT" wrote:

If I can't get to "Format Cells", how do I tell if the cell is protected.
Sorry but I am not an Excel expert. The worksheet does seem to be
unprotected.

"Jay" wrote:

Hi NevilleT -

I don't use XL2007, but its menus are more context sensitive than prior
versions. So, not seeing a format menu when a cell is selected strongly
suggests that there may be some protection lurking around.

You mentioned earlier that there was no protection, but we need to reconfirm
that that is the case. Can you confirm that the cell is not protected
before we proceed ?

--
Jay

"NevilleT" wrote:

Hi Guys

I am using Excel 2007. I tried to format and when I click Format, Format
Cells, I don't get a menu. When I click somewhere else, I get a menu. I am
not an Excel expert but am reasonably capable with VBA using Access. I am
certainly struggling with this one.

"Leung" wrote:

Hi

Are you sure you didn't protect any cells for changes?

Leung

"NevilleT" wrote:

I posted this message before but didn't resolve it. I have a pivot table I
am trying to format. The sheet is unprotected and the code fails with an
error

"Unable to set the linestyle property of the border class"

It does however work correctly on other worksheets in the same workbook.

Dim rng As Range

Set rng = ActiveSheet.Range("A5:B600")
rng.Interior.ColorIndex = xlNone
rng.Borders(xlDiagonalDown).LineStyle = xlNone ' Error occurs here
rng.Borders(xlDiagonalUp).LineStyle = xlNone

Formatting a Pivot Table
 

In the end I took a creative solution and changed the area to B5:AO600. Also
had to remove the line

' .Weight = xlThin

I avoided the dreaded column A. It works now. I do very little in Excel.
Mainly work in Access and while it would be nice to find the answer, it is
not worth my time and money. Thanks again for your help Jay. I learned a
few things and it did set me down a path to create a workaround.

"Jay" wrote:

Hi NevilleT -

Sorry we couldn't solve the problem directly. It sounds like you chose your
updates cleanly and carefully; they weren't anything out of the ordinary and
you wouldn't expect them to lead to the VBA error. Hopefully, the
reconstruction will shed some light on the problem.

I'll switch to monitoring this thread via 'email notification' should you
like to pick up where we left off.

---
Jay

"NevilleT" wrote:

Thanks Jay. I know Access 2007 has a few "undocumented features" with pivot
tables so perhaps Excel does too. At this point I may try recreating the
whole thing in Excel 2003.
I was working on a program that a client inherited and which I did some
major mods to a few years back. They needed a few more changes this week
which had little impact on the pivot table other than to extend the range.
The code was working up to the point where I extended the range and converted
to 2007. Maybe I can get it working in 2003. Thanks for all your help.



"Jay" wrote:

Good question Neville. I expected too much of Excel 2007...

At this point, I'd suggest remanufacturing the Pivot Table from scratch and
running your procedure on the new Pivot Table before doing anything else to
it. We'll evaluate your procedure on a 'virgin' pivot table.
---
Jay



"NevilleT" wrote:

If I can't get to "Format Cells", how do I tell if the cell is protected.
Sorry but I am not an Excel expert. The worksheet does seem to be
unprotected.

"Jay" wrote:

Hi NevilleT -

I don't use XL2007, but its menus are more context sensitive than prior
versions. So, not seeing a format menu when a cell is selected strongly
suggests that there may be some protection lurking around.

You mentioned earlier that there was no protection, but we need to reconfirm
that that is the case. Can you confirm that the cell is not protected
before we proceed ?

--
Jay

"NevilleT" wrote:

Hi Guys

I am using Excel 2007. I tried to format and when I click Format, Format
Cells, I don't get a menu. When I click somewhere else, I get a menu. I am
not an Excel expert but am reasonably capable with VBA using Access. I am
certainly struggling with this one.

"Leung" wrote:

Hi

Are you sure you didn't protect any cells for changes?

Leung

"NevilleT" wrote:

I posted this message before but didn't resolve it. I have a pivot table I
am trying to format. The sheet is unprotected and the code fails with an
error

"Unable to set the linestyle property of the border class"

It does however work correctly on other worksheets in the same workbook.

Dim rng As Range

Set rng = ActiveSheet.Range("A5:B600")
rng.Interior.ColorIndex = xlNone
rng.Borders(xlDiagonalDown).LineStyle = xlNone ' Error occurs here
rng.Borders(xlDiagonalUp).LineStyle = xlNone