Event trouble...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

Ok... I have been all over the code in my workbook and I have narrowed
down the problem but am lost as it should not perform this way...

The worksheet_change event fires... Turns off events... executes a series
of procedures as follows...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sDate As String, sField As String
Dim sValue As String, sTable As String
Dim dOld As Date, rCell As Range

Application.EnableEvents = False

If Not Intersect(Range("curYear"), Target) Is Nothing Then
dOld = Range("curMonth").Value
Range("curMonth").Value = Month(dOld) & "/1/" & Target.Value
Reload
GoTo Done
End If

If Not Intersect(Range("curMonth"), Target) Is Nothing Then
Reload
GoTo Done
End If

Sub Reload()

' ********************************************************
' ** Load Data from Database to 'Worksheet' &
' ** Move Data to 'Monthly Cash Sheet'
' ********************************************************

Range("RawData").ClearContents

LoadDay
LoadMonth
LoadWeek

MoveDay
MoveMonth

End Sub

The problem is that the process never gets back to the change event to turn
events back on... I put break points everywhere to track down the problem...
LoadDay runs fine. LoadMonth runs but then the code just ends... The last
code to run is a custom worksheet function. If I disable this function then
everything works... the code comes back to the change event and turns them
back on like expected. But once I reenable the function it does not work
right.. Here is the function:

Function CountDaysWithSales(dEnd As Date) As Integer

Dim fld As Field, rst As ADODB.Recordset
Dim sConn As String, sSQL As String
Dim dLast As Date

CountDaysWithSales = 0

sFile = ThisWorkbook.Path & DB_FILE
dLast = DateSerial(Year(dEnd), Month(dEnd) + 1, 0) + 1

' Create a new recordset object
Set rst = New ADODB.Recordset
' Connection details
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile

'Count the days
sSQL = "SELECT COUNT([ID]) FROM " & DAY_TABLE & _
" WHERE [Daily Sales]>0 AND Date<#" & dLast & "# " & _
"AND Date>#" & dEnd & "#;"

rst.Open sSQL, sConn
If rst.State = adStateOpen Then
CountDaysWithSales = rst.Fields(0).Value
End If

Done:
rst.Close
Set rst = Nothing

End Function

I just cannot see anything that could cause errors... No errors are
generated either... Any help on this would be great...

Ernst.
 
Hi Ernst

If an error occurs within a UDF called from the worksheet, that will halt
the code in its tracks. You must add appropriate error handling to your code,
to handle that case.

For example, your function could include at the top:

On Error Goto ErrHandler

and at the bottom:

Exit Function
ErrHandler:
ConutDaysWithSales = 0
End Function

That will ensure that any errors in the function will be handled tidily,
returning zero.

Hope that helps

Stephen Bullen

Ernst Guckel said:
Hello,

Ok... I have been all over the code in my workbook and I have narrowed
down the problem but am lost as it should not perform this way...

The worksheet_change event fires... Turns off events... executes a series
of procedures as follows...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sDate As String, sField As String
Dim sValue As String, sTable As String
Dim dOld As Date, rCell As Range

Application.EnableEvents = False

If Not Intersect(Range("curYear"), Target) Is Nothing Then
dOld = Range("curMonth").Value
Range("curMonth").Value = Month(dOld) & "/1/" & Target.Value
Reload
GoTo Done
End If

If Not Intersect(Range("curMonth"), Target) Is Nothing Then
Reload
GoTo Done
End If

Sub Reload()

' ********************************************************
' ** Load Data from Database to 'Worksheet' &
' ** Move Data to 'Monthly Cash Sheet'
' ********************************************************

Range("RawData").ClearContents

LoadDay
LoadMonth
LoadWeek

MoveDay
MoveMonth

End Sub

The problem is that the process never gets back to the change event to turn
events back on... I put break points everywhere to track down the problem...
LoadDay runs fine. LoadMonth runs but then the code just ends... The last
code to run is a custom worksheet function. If I disable this function then
everything works... the code comes back to the change event and turns them
back on like expected. But once I reenable the function it does not work
right.. Here is the function:

Function CountDaysWithSales(dEnd As Date) As Integer

Dim fld As Field, rst As ADODB.Recordset
Dim sConn As String, sSQL As String
Dim dLast As Date

CountDaysWithSales = 0

sFile = ThisWorkbook.Path & DB_FILE
dLast = DateSerial(Year(dEnd), Month(dEnd) + 1, 0) + 1

' Create a new recordset object
Set rst = New ADODB.Recordset
' Connection details
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile

'Count the days
sSQL = "SELECT COUNT([ID]) FROM " & DAY_TABLE & _
" WHERE [Daily Sales]>0 AND Date<#" & dLast & "# " & _
"AND Date>#" & dEnd & "#;"

rst.Open sSQL, sConn
If rst.State = adStateOpen Then
CountDaysWithSales = rst.Fields(0).Value
End If

Done:
rst.Close
Set rst = Nothing

End Function

I just cannot see anything that could cause errors... No errors are
generated either... Any help on this would be great...

Ernst.
Click to expand...
 
Back
Top