| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Borders Error
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Borders Error
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Borders Error
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Borders Error
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Borders Error
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) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Borders Error
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Borders Error
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) |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Borders Error
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) |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Borders Error
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) |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Borders Error
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) |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Borders Error
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 "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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Borders | Excel Discussion (Misc queries) | |||
| Borders | Excel Programming | |||
| borders | Excel Programming | |||
| Deleting cell borders before file opening error occurs? | Excel Programming | |||
| Borders | Excel Discussion (Misc queries) | |||
Linear Mode
