Prior, I have always used Private Sub Workbook_SheetChange in ThisWorkbook before.... but now I am getting cocky and trying to use in a sheet instead ! ( so only Private Sub WorkBook_Open() ) is in the ThisWorkbook.
Following is the code in a sheet named "Mkt-1" ( BA feeds data into cell A10 )
This is an WinXP sp3 pc with Excel 2003 with BA ver 1.1.0.66x84
- Code: Select all
Option Explicit
'Private Const strCountdownCell1 As String = "D11" 'moved from ThisWorkbook - now disabled
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim c As Range, dblLastSnap As Double, sht1 As Worksheet, sht2 As Worksheet
Dim Racename As String, myHour As Variant, myMin As Variant, ShtMarker As Integer, mySeqRow As Integer
Dim strCountdownCell1 As String 'moved from ThisWorkbook & mod
If (Target.Worksheet.name <> "Mkt-1") Then Exit Sub
If Target.Columns.count <> 16 Then Exit Sub
'>>>>>>>>>>market identifier for this sheet >>>>>>>>>>>>>>>>>>>>>
ShtMarker = 1
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
If ShtMarker = 1 Then Set sht1 = ThisWorkbook.Worksheets("Mkt-1")
If ShtMarker = 2 Then Set sht1 = ThisWorkbook.Worksheets("Mkt-2")
Set sht2 = ThisWorkbook.Worksheets("MyBets")
'There needs to be a sequence
'control for each of the markets: PUT ALL in'MyBets'
If ShtMarker = 1 Then mySeqRow = 4
If ShtMarker = 2 Then mySeqRow = 6
Application.ScreenUpdating = False
ThisWorkbook.Worksheets.Application.EnableEvents = False: ThisWorkbook.Worksheets.Application.Calculation = xlCalculationManual
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'--------------------------- core sequencing controls ---------------------------------
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
strCountdownCell1 = Str(sht1.Range("D11"))
For Each c In Target.Cells
If (c.Address(False, False, xlA1) = strCountdownCell1) And VarType(c) = vbDouble Then
dblLastSnap = sht1.Range("A1")
sht1.Range("A1") = c.Value
If c.Value <= sht1.Range("B2") And dblLastSnap > sht1.Range("B2") Then '====> Handling on t1
If sht1.Cells(4, 1) <> sht1.Cells(10, 1) Then ' no point in doing it twice for same race !
sht1.Cells(7, 24) = "" 'racetime display
sht1.Cells(8, 21) = "" 'last error
sht1.Cells(7, 21) = "RESET" 'sequence control display
Racename = sht1.Cells(10, 1)
sht1.Cells(4, 1) = sht1.Cells(10, 1)
sht1.Range("w8:x8").ClearContents 'sequence label display
Call Module1.Race_Time(Racename, ShtMarker, mySeqRow)
'returns = "AAAA" which intiates sequence control in MyBets Sequence Controllers
End If
End If '<==== Handling on t1
End If
Next c
'>>>>>>>>>>>>>>>>>
Application.ScreenUpdating = True
ThisWorkbook.Worksheets.Application.Calculation = xlCalculationAutomatic: ThisWorkbook.Worksheets.Application.EnableEvents = True
End Sub
Countdown timer in D11 is copied upto A1 ...... except nothing is happening in cell A1 when BA is linked to this sheet.
I put the debugger against :-
- Code: Select all
If (Target.Worksheet.name <> "Mkt-1") Then Exit Sub
no response, ie did not stop the code here - because it isnt getting there ?? ! most puzzling...
I tried modifying :-
- Code: Select all
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
to
- Code: Select all
Private Sub Workbook_SheetChange( ByVal Target As Range)
so more consistent with procedure codes on this forum - still no response in cell A1