Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Worksheet Calculate
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 333
Default Worksheet Calculate
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.
--

Karen
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Worksheet Calculate
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 su

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

--
Regards,
Tom Ogilvy


"Karen53" wrote:

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.
--

Karen
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 333
Default Worksheet Calculate
Thanks Tom,

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

Thanks
--

Karen


"Tom Ogilvy" wrote:

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 su

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

--
Regards,
Tom Ogilvy


"Karen53" wrote:

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.
--

Karen
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 333
Default Worksheet Calculate
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.
--

Karen


"Tom Ogilvy" wrote:

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 su

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

--
Regards,
Tom Ogilvy


"Karen53" wrote:

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.
--

Karen
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 333
Default Worksheet Calculate
Hi,

I got it. Thanks!
--

Karen


"Tom Ogilvy" wrote:

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 su

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

--
Regards,
Tom Ogilvy


"Karen53" wrote:

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.
--

Karen
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Worksheet Calculate Karen53 Excel Programming 6 September 27th 07 05:59 AM
worksheet calculate RobcPettit[_2_] Excel Programming 4 August 3rd 06 11:09 PM
Worksheet will not calculate jk85016 Excel Discussion (Misc queries) 1 May 19th 06 02:41 PM
Can I calculate just one worksheet in a multi-worksheet workbook? Captive Thinker Excel Discussion (Misc queries) 3 March 2nd 06 10:36 AM


All times are GMT +1. The time now is 09:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement