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
--
Thanks for your help.
Karen53

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" wrote:

> 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
> --
> Thanks for your help.
> 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?
--
Thanks for your help.
Karen53


"JLGWhiz" wrote:

> 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" wrote:
>
> > 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
> > --
> > Thanks for your help.
> > 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.
--
Thanks for your help.
Karen53


"JLGWhiz" wrote:

> 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" wrote:
>
> > 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
> > --
> > Thanks for your help.
> > Karen53

The code works for me.
Also, xlNone and xlLineStyleNone are identical. Both have a value of -4142
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

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" wrote:

> It's interesting. If I record a Macro removing borders, Excel records xlNone
> for LineSyle yet the Help info says it should be xlLineStyleNone.
> --
> Thanks for your help.
> Karen53
>
>
> "JLGWhiz" wrote:
>
> > 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" wrote:
> >
> > > 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
> > > --
> > > Thanks for your help.
> > > 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


---
Thanks for your help.
Karen53


"Jim Cone" wrote:

>
> The code works for me.
> Also, xlNone and xlLineStyleNone are identical. Both have a value of -4142
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)
>
>

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.

--
Thanks for your help.
Karen53


"Jim Cone" wrote:

>
> The code works for me.
> Also, xlNone and xlLineStyleNone are identical. Both have a value of -4142
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)
>
>

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.
--
Thanks for your help.
Karen53


"Jim Cone" wrote:
> The code works for me.
> Also, xlNone and xlLineStyleNone are identical. Both have a value of -4142
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)
>
>

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


"Jim Cone" wrote:

>
> 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.
> --
> Thanks for your help.
> Karen53
>
>
> "Jim Cone" wrote:
> > The code works for me.
> > Also, xlNone and xlLineStyleNone are identical. Both have a value of -4142
> > --
> > Jim Cone
> > San Francisco, USA
> > http://www.realezsites.com/bus/primitivesoftware
> > (Excel Add-ins / Excel Programming)
> >
> >
>