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