Worksheet Calculate

[Karen53]

Hi,

I'm having a weird problem with Worksheet Calculate. My message boxes all
show the correct values but the code does not run beyond a certain point. It
goes into the routine Borders2 but never comes out. However, if I Step into
the code it runs perfectly all the way through.

Does anyone have any suggestions?

Private Sub Worksheet_Calculate()

'Created 9/13/2007 by Karen Hoagland
' Check if value in pools 10
' if value add borders and add label

On Error GoTo ws_exit
Application.EnableEvents = False

'Check CAM Pool

Dim Grid As String
Dim LabelRng As Range
Dim LCol As String
Dim StartRow As Long
Dim EndRow As Long


Grid = ("F11:N13")

If Me.Range("F10").Value <> "" Then
MsgBox ("Not Null " & Me.Range("F10"))
MsgBox ("Not Null " & Grid)
'set the values
LCol = "E"
StartRow = 11
EndRow = 13

Call Me.Borders2(Grid)
Call Me.PoolSideLabels(StartRow, EndRow, LCol)

End If

If Me.Range("F10").Value = "" Then

MsgBox ("Is Null " & Me.Range("F10"))
MsgBox ("Is Null " & Grid)


Me.Range(Grid).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Me.Range("E11").Value = ""
Me.Range("E12").Value = ""
Me.Range("E13").Value = ""

End If

ws_exit:
Application.EnableEvents = True

End Sub


Sub Borders2(Grid)
'
MsgBox ("Borders " & Grid) 'I get this msgbox but nothing beyond it

Me.Range(Grid).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

End Sub


Sub PoolSideLabels(StartRow, EndRow, LCol)

Dim iCtr As Long

iCtr = 0
MsgBox ("Labels " & StartRow & "," & EndRow)

For iCtr = StartRow To EndRow Step 1
Me.Range(LCol & iCtr).Select
ActiveCell.FormulaR1C1 = Tablespg.Range("PoolSideLabels").Item(iCtr -
10, 1).Value
'subtract 10 as the invoice cell is in row 11 and the named range
'begins at 1
Next

'right justify
Me.Range(LCol & StartRow & ":" & LCol & EndRow).HorizontalAlignment = xlRight

End Sub


Thanks for your help.

 

[Tom Ogilvy]

comment out the line that sets your error handler


'On Error GoTo ws_exit

then make a change that would make it run. I suspect you will be alerted to
an error in your code probably here but who can say for sure:

ActiveCell.FormulaR1C1 = _
Tablespg.Range("PoolSideLabels").Item(iCtr - 10, 1).Value

 

[Karen53]

Thanks Tom,

I get an Application or Object defined error on my call Borders2(Grid) but I
don't understand why.

Thanks

 

[Karen53]

Tom,

I tried commenting out the call Borders2(Grid) to see if it would go into
the labels routine. I get the message box I have in the routine so it does
go into the routine but I get the same Application or object defined error
at the call to the routine. I don't understand what is happening. Can you
give me any direction?

Thanks.

 

[Karen53]

Hi,

I got it. Thanks!
--

Karen

comment out the line that sets your error handler


'On Error GoTo ws_exit

then make a change that would make it run. I suspect you will be alerted to
an error in your code probably here but who can say for sure:

ActiveCell.FormulaR1C1 = _
Tablespg.Range("PoolSideLabels").Item(iCtr - 10, 1).Value