Record Available Balance sample sheet problem

[brett]

Hello,

This very useful program only runs in compatibility mode on my 2010 Excel , and when I try to copy and paste the code, it doesn't work at all. It only works off the original downloaded sheet. I have no idea about Visual basic, has anyone got a solution to this?
Option Explicit

Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
If [A1] <> currentMarket Then
currentMarket = [A1]
logBalance
End If
End If
End Sub

Private Sub logBalance()
Application.EnableEvents = False
Dim r As Integer
r = 2
While Sheet2.Cells(r, 1) <> ""
r = r + 1
Wend
Sheet2.Cells(r, 1) = currentMarket
Sheet2.Cells(r, 2) = [I2]
Application.EnableEvents = True
End Sub

[alrodopial]

did you place the code in the "ThisWorkbook" section in VB editor?

[brett]

Hi there alrodopial, BTW I've also been using been using your great 2009 program Recording odds with BA , but I notice this Record Available Balance sample sheet (and all the other sample sheets available) have been developed on Excel1997-2003. I want it to work on my XL2010 without compatibility mode.
Yes ThisWorkbook appears in VB editor and I click on view code on the sheet and it's pasted correctly but it doesn't work on other sheets but works perfectly on the original downloaded sheet only and it's very valuable as it updates down a column as each of your markets are opened so no need for a results sheet etc. as it all shows up in your balance if you won or lost. So how can I paste the code in XL2010 to make it work on other sheets?

[alrodopial]

Sorry,
code goes inside "sheet1"

It's working fine for me in office 2010

[Captain Sensible]

Have you pasted the code into alrodopial's other sheet? You probably need to post what code's already running as it just needs to be merged in there rather than another instance of Worksheet_Change .

[brett]

Yes I always put the code in sheet 1 named "Market" and the workbook I'am trying to put it in has no other code on it . I'll keep working on it but I can always make copies of the original downloaded sheet and work from there. This program is also great for using staking plans with it.

[Captain Sensible]

Without seeing your sheet it's hard to know where you're going wrong. It may be something as simple as your naming of cell references and the sheet your code is in. It's always worthwhile fully referencing cells such as Worksheets("Sheet1").Range("A1").Value rather than just using generic references like [A1]. Takes a little longer but when you start using more than one sheet it's worth the effort.

Maybe try it again with the cells fully referenced like below, Make sure the code is in the sheet you're linking excel to and change Sheet1 and Sheet2 to whatever your worksheets are actually called

Code: Select all

Option Explicit

Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
If Worksheets("Sheet1").Range("A1").Value <> currentMarket Then
currentMarket = Worksheets("Sheet1").Range("A1").Value
logBalance
End If
End If
End Sub

Private Sub logBalance()
Application.EnableEvents = False
Dim r As Integer
r = 2
While Sheet2.Cells(r, 1) <> ""
r = r + 1
Wend
Sheet2.Cells(r, 1) = currentMarket
Sheet2.Cells(r, 2) = Worksheets("Sheet1").Range("I2").Value
Application.EnableEvents = True
End Sub



[brett]

I tried your code Captain Sensible but unfortunately it didn't work it came up with a run script debug etc. error so I went back to the original code and tried something new , putting the code in a new blank worksheet and it's working perfectly without compatibility mode that's what I wanted so all I have to do now is make copies and go from there.
So problem solved, and thank you very much for your efforts Captain Sensible and alrodopial.