Recorded macro fails after save, close, then reopen
 

I recorded the following macro

Sub Macro1()
'

'

'
Application.Goto Reference:="Print_Area"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
Columns("A:W").Select
Columns("A:W").EntireColumn.AutoFit
End Sub

PrintArea is a portion of a Pivot Table

The macro works fine as long as the workbook remains open. After I close
the workbook, and subsequently open it at a later time, the macro fails on
the statement

..LineStyle = xlContinuous

with a message that states

Runtime error 1004

Unable to set the line style of the border class.

Any help would be appreciated.

Recorded macro fails after save, close, then reopen
 

I'd be willing to bet that it's failing because you don't have an Inside
Vertical or Inside Horizontal in your print area.

Put
On Error Resume Next

at the beginning of your sub and

On Error GoTo 0

at the end of this sub.
--
HTH,
Barb Reinhardt



"Marvin Buzz" wrote:

I recorded the following macro

Sub Macro1()
'

'

'
Application.Goto Reference:="Print_Area"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
Columns("A:W").Select
Columns("A:W").EntireColumn.AutoFit
End Sub

PrintArea is a portion of a Pivot Table

The macro works fine as long as the workbook remains open. After I close
the workbook, and subsequently open it at a later time, the macro fails on
the statement

.LineStyle = xlContinuous

with a message that states

Runtime error 1004

Unable to set the line style of the border class.

Any help would be appreciated.

Recorded macro fails after save, close, then reopen
 

Barb-

Your solution works. Would you mind explaining what went wrong, and how you
came upon your solution?

Thanks.

"Barb Reinhardt" wrote:

I'd be willing to bet that it's failing because you don't have an Inside
Vertical or Inside Horizontal in your print area.

Put
On Error Resume Next

at the beginning of your sub and

On Error GoTo 0

at the end of this sub.
--
HTH,
Barb Reinhardt



"Marvin Buzz" wrote:

I recorded the following macro

Sub Macro1()
'

'

'
Application.Goto Reference:="Print_Area"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
Columns("A:W").Select
Columns("A:W").EntireColumn.AutoFit
End Sub

PrintArea is a portion of a Pivot Table

The macro works fine as long as the workbook remains open. After I close
the workbook, and subsequently open it at a later time, the macro fails on
the statement

.LineStyle = xlContinuous

with a message that states

Runtime error 1004

Unable to set the line style of the border class.

Any help would be appreciated.

Recorded macro fails after save, close, then reopen
 

try

Sub borderprintarea()
With Range("Print_Area").Borders
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
Columns("A:W").EntireColumn.AutoFit
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Marvin Buzz" wrote in message
...
I recorded the following macro

Sub Macro1()
'

'

'
Application.Goto Reference:="Print_Area"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
Columns("A:W").Select
Columns("A:W").EntireColumn.AutoFit
End Sub

PrintArea is a portion of a Pivot Table

The macro works fine as long as the workbook remains open. After I close
the workbook, and subsequently open it at a later time, the macro fails on
the statement

.LineStyle = xlContinuous

with a message that states

Runtime error 1004

Unable to set the line style of the border class.

Any help would be appreciated.

Recorded macro fails after save, close, then reopen
 

Don-

Barb's reply worked for me. I was curious as to why it was necessary to
ignore the error, why the error occurred, and what insight she had that led
to the resolution.

Thanks.

"Don Guillett" wrote:

try

Sub borderprintarea()
With Range("Print_Area").Borders
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
Columns("A:W").EntireColumn.AutoFit
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Marvin Buzz" wrote in message
...
I recorded the following macro

Sub Macro1()
'

'

'
Application.Goto Reference:="Print_Area"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
Columns("A:W").Select
Columns("A:W").EntireColumn.AutoFit
End Sub

PrintArea is a portion of a Pivot Table

The macro works fine as long as the workbook remains open. After I close
the workbook, and subsequently open it at a later time, the macro fails on
the statement

.LineStyle = xlContinuous

with a message that states

Runtime error 1004

Unable to set the line style of the border class.

Any help would be appreciated.

Recorded macro fails after save, close, then reopen
 

All the error handler said was if you have a problem then skip the part you
are having problems with and goto the next step. Barb was saying that you
probably have not vertical or horizontal in your selection area. If you want
to test that then take the error handling parts that Barb had you put in out
and remove the below sections of code.


With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With

Marvin Buzz wrote:
Don-

Barb's reply worked for me. I was curious as to why it was necessary to
ignore the error, why the error occurred, and what insight she had that led
to the resolution.

Thanks.

try

[quoted text clipped - 67 lines]

Any help would be appreciated.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200808/1

Recorded macro fails after save, close, then reopen
 

What result did mine give?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Marvin Buzz" wrote in message
...
Don-

Barb's reply worked for me. I was curious as to why it was necessary to
ignore the error, why the error occurred, and what insight she had that
led
to the resolution.

Thanks.

"Don Guillett" wrote:

try

Sub borderprintarea()
With Range("Print_Area").Borders
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
Columns("A:W").EntireColumn.AutoFit
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Marvin Buzz" wrote in message
...
I recorded the following macro

Sub Macro1()
'

'

'
Application.Goto Reference:="Print_Area"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
Columns("A:W").Select
Columns("A:W").EntireColumn.AutoFit
End Sub

PrintArea is a portion of a Pivot Table

The macro works fine as long as the workbook remains open. After I
close
the workbook, and subsequently open it at a later time, the macro fails
on
the statement

.LineStyle = xlContinuous

with a message that states

Runtime error 1004

Unable to set the line style of the border class.

Any help would be appreciated.

Recorded macro fails after save, close, then reopen
 

It worked because you had no inside verticals or inside horizontals. I don't
know without looking at your selection.

I came across this solution because I've seen it before. I haven't tried
Don's solution, but I will the next time.
--
HTH,
Barb Reinhardt



"Marvin Buzz" wrote:

Barb-

Your solution works. Would you mind explaining what went wrong, and how you
came upon your solution?

Thanks.

"Barb Reinhardt" wrote:

I'd be willing to bet that it's failing because you don't have an Inside
Vertical or Inside Horizontal in your print area.

Put
On Error Resume Next

at the beginning of your sub and

On Error GoTo 0

at the end of this sub.
--
HTH,
Barb Reinhardt



"Marvin Buzz" wrote:

I recorded the following macro

Sub Macro1()
'

'

'
Application.Goto Reference:="Print_Area"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
Columns("A:W").Select
Columns("A:W").EntireColumn.AutoFit
End Sub

PrintArea is a portion of a Pivot Table

The macro works fine as long as the workbook remains open. After I close
the workbook, and subsequently open it at a later time, the macro fails on
the statement

.LineStyle = xlContinuous

with a message that states

Runtime error 1004

Unable to set the line style of the border class.

Any help would be appreciated.

Recorded macro fails after save, close, then reopen
 

To all-

Thanks for the responses. I did not go any further than Barb's suggestion.
For clarification purposes, I guess I was asking why it worked when I
recorded the macro, why it continued to work when I ran the macro, and why it
failed after the workbook was closed and subsequently opened.

Thanks again.

Marvin

"Don Guillett" wrote:

What result did mine give?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Marvin Buzz" wrote in message
...
Don-

Barb's reply worked for me. I was curious as to why it was necessary to
ignore the error, why the error occurred, and what insight she had that
led
to the resolution.

Thanks.

"Don Guillett" wrote:

try

Sub borderprintarea()
With Range("Print_Area").Borders
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
Columns("A:W").EntireColumn.AutoFit
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Marvin Buzz" wrote in message
...
I recorded the following macro

Sub Macro1()
'

'

'
Application.Goto Reference:="Print_Area"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 1
End With
Columns("A:W").Select
Columns("A:W").EntireColumn.AutoFit
End Sub

PrintArea is a portion of a Pivot Table

The macro works fine as long as the workbook remains open. After I
close
the workbook, and subsequently open it at a later time, the macro fails
on
the statement

.LineStyle = xlContinuous

with a message that states

Runtime error 1004

Unable to set the line style of the border class.

Any help would be appreciated.