I am using the StateMachine spreadsheet that I previously posted a few weeks back so am just struggling to identify where the code should be placed.
The following code, from another user (Apologies, I usually like to give credit but can't remember who this came from), uses 'Sheet2' but I specifically want my logged Cells to be B40:K40 on 'Sheet1', you can see what I mean if you open up the attachment! (30 second iterations = B41:K71).
Code: Select all
Private Sub Worksheet_Calculate()
If Range("A4") = "TICK" And Range("A6") = 0 And Range("A1") = True Then Call TICK
If Range("A4") = "TOCK" And Range("A6") = 1 And Range("A1") = True Then Call TOCK
End Sub
Code: Select all
Sub Record_data()
'
' Record_data Macro
'
Sheets("Data").Select
Sheets("Data").Rows("5:5").Select
Sheets("Data").Range("B5").Activate
Selection.Copy
Sheets("Data").Rows("7:7").Select
Sheets("Data").Range("B7").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Rows("7:7").Select
Sheets("Data").Range("B7").Activate
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
Sub TICK()
Range("A6") = 1
Call Record_data
End Sub
Sub TOCK()
Range("A6") = 0
If Sheets("Data").Range("G1") = 1 Then Call Record_data
End Sub
Sub Delete_data()
'
' Delete_data Macro
'
'
Sheets("Data").Select
Sheets("Data").Range("A8").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Sheets("Data").Range("A1").Select
End Sub
Code: Select all
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False 'Turn off events so changes to cells don't retrigger event
Application.Calculation = xlCalculationManual ' set calculations to manual whilst code runs
Call StateMachineONE
Call StateMachineTWO
Call StateMachineTHREE
Call StateMachineFOUR
Call StateMachineFIVE
Call StateMachineSIX
Application.Calculation = xlCalculationAutomatic 'turn on calculations
Application.EnableEvents = True 'Turn back on events
End Sub
How far down does the BA template (that this spreadsheet is built from) look for ‘Report’, ‘Odds’ and ‘Stake’ selections in columns L, M and N? I don't want to be accidentally firing in orders or confusing excel.
Have Tick/Tock happening in the invisible A column or cell I2 or I4.
so even if the above code can be modified to fit the space I have ready for the values to scroll between, I still have the problem of it only remembering the previous 30 seconds and not more.
Can anybody at least start me off in the right direction please?
Kind regards,
Callum.