Helen Obomanu


I am trying to create a simple spreadsheet in excel that can perform a macro that allows me to insert a new row on the existing worksheet and have it formatted with the borders of the cells darkened so that it looks like the rest of the spreadsheet. I recorded the macro, but I recieved an error message that looks like the following:

"Run time error 1004: Unable to set the line style property of the border class"

The following code appears when I try and debug it:


Sub AddNewEntry()
'
' AddNewEntry Macro
' Macro recorded 5/24/2007 by imihxo0
'
' Keyboard Shortcut: Ctrl+a
'
Rows("6:6").Select
Selection.Insert Shift:=xlDown
Range("A6:K6").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("A6").Select
End Sub

If you have any idea how to debug it, please respond and relay to me how to resolve this issue.

Thanks

Helen




Re: Beginner needs help resonling an error message

Feng Chen - MSFT


Hi Helen Obomanu,

Move the thread from Visual Basic General in order to get better answers.
Thanks for your understanding!







Re: Beginner needs help resonling an error message

Andy Pope

Hi Helen,

Not sure how you managed to record that last bit where you set the inside horizontal border as the selection is only 1 row high.

Any way the problem is there is not InsideHorizontal when you are formatting a single row. Either remove it or add conditional test if you intend using the code on different ranges.

If Selection.Rows.Count > 1 Then
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End If


By the way the same problem will occur with InsideVertical if the range is only 1 column wide.