Borders Error

[Karen53]

Hi,

I've been having trouble with this. I keep getting an 'unable to get the
linestyle property of the border class' error message at the line "If
..Borders(xlEdgeTop).LineStyle <> xlNone Then".

If I remove this line and go directly into Sub RemoveBorders, I get the
error on the first xlNone line.

Have I missed something?

Sub ChkCAMPool(ShName)

Debug.Print "Starting ChkCAMPool " & Sheets(ShName).Name

' Check if value in pools 10
' if value add borders to 2nd row of Pools
'Check CAM Pool

Dim Grid As Range

Set Grid = Sheets(ShName).Range("F34:N36")

With Grid
If Sheets(ShName).Range("F33").Value <> "" Then

If .Borders(xlEdgeTop).LineStyle <> xlThin Then
Call Formating.AddThinBorders(ShName, Grid)
End If
End If

If Sheets(ShName).Range("F33").Value = "" Then
If .Borders(xlEdgeTop).LineStyle <> xlNone Then 'Error is
here
Call Formating.RemoveBorders(ShName, Grid)
End If
End If
End With

End Sub


Sub RemoveBorders(ShName, Grid As Range)

With Grid
.Borders(xlDiagonalDown).LineStyle = xlNone 'error is here
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With

End Sub

 

[JLGWhiz]

Hi Karen, I don't see xlNone listed as a LineStyle option. It might work
with .Borders(xlEdgeTop) = xlNone, but I haven't tried it.

 

[Karen53]

Hi,

Thanks for your response. I have tried this with both xlNone and
xlLineStyleNone. Neither works. For xlLineStyleNone the error is unable to
set the LineStyle property.

Any ideas?

 

[Karen53]

It's interesting. If I record a Macro removing borders, Excel records xlNone
for LineSyle yet the Help info says it should be xlLineStyleNone.

 

[Jim Cone]

The code works for me.
Also, xlNone and xlLineStyleNone are identical. Both have a value of -4142

 

[JLGWhiz]

When I took a second look and tried the code, it ran without a glitch. Maybe
you encountered a Grimlin. They are abundant on Sunday's, you know. <g>

 

[Karen53]

Thanks, Jim,

This code is part of the Worksheet_calculate. I can make changes in several
different places and everything works fine. I can add Line Items, Edit Line
Items and all is good. As soon as I delete a Line Item I get this error on
the line below.

I see nothing that should affect the setting or getting of the LineStyle
Property for the borders. Also, it's the same grid. Why would I get an
error on the second one and not the first?

If .Borders(xlEdgeTop).LineStyle <> xlThin Then 'no error
If .Borders(xlEdgeTop).LineStyle <> xlLineStyleNone ' error here

At this point I don't know what I'm looking for and could use any
suggestions or guidance you may have.

Sub ChkCAMPool(ShName)

Debug.Print "Starting ChkCAMPool " & Sheets(ShName).Name

' Check if value in pools 10
' if value add borders to 2nd row of Pools

'Check CAM Pool

Dim Grid As Range

Set Grid = Sheets(ShName).Range("F34:N36")

With Grid
If Sheets(ShName).Range("F33").Value <> "" Then
If .Borders(xlEdgeTop).LineStyle <> xlThin Then
Call Formating.AddThinBorders(Grid)
End If
End If

If Sheets(ShName).Range("F33").Value = "" Then
If .Borders(xlEdgeTop).LineStyle <> xlLineStyleNone Then 'error
is here
Call Formating.RemoveBorders(Grid)
End If
End If
End With

End Sub

 

[Karen53]

Jim, sorry scratch that about why the second and not the first...Duhhh. I'm
tired.

So, I'm left with I can add and remove borders using this code until I
delete a Line Item. Then I get this error.

 

[Jim Cone]

Just a guess, but maybe the line item (row?) you are deleting is affecting
the range returned by "Grid". Try running a msgbox with the Grid.Address
in a few spots and see if the range changes.
And since the code is in a worksheet module, make sure every range reference
includes the parent sheet.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Karen53"
wrote in message
Jim, sorry scratch that about why the second and not the first...Duhhh. I'm
tired.

So, I'm left with I can add and remove borders using this code until I
delete a Line Item. Then I get this error.

 

[Karen53]

Hi Jim,

I did as you suggested and the message boxes came back with the correct
range and again just before the error with the correct range.

By including the parent sheet, do you mean this?
Set Grid = Sheets(ShName).Range("F34:N36")

If so, the parent sheet is included.

This is so frustrating!

 

[Jim Cone]

Karen,
I can come up with no more suggestions.
Jim Cone




"Karen53"
wrote in message
Hi Jim,

I did as you suggested and the message boxes came back with the correct
range and again just before the error with the correct range.
By including the parent sheet, do you mean this?
Set Grid = Sheets(ShName).Range("F34:N36")

If so, the parent sheet is included.
This is so frustrating!--
Thanks for your help.
Karen53