Excel Data Capture

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
StellaBot
Posts: 818
Joined: Thu Jan 26, 2017 11:52 am

Hello All

Looking for help/advice on data capture VBA.
Bit of code here that works manually when not connected to BA.
It works with any cells eg H9 to AH9 or G9 to AG9.
Example here is C2 to D2.

Dim xVal As String
Private Sub Worksheet_Change(ByVal Target As Range)
Static xCount As Integer
Application.EnableEvents = False
If Target.Address = Range("C2").Address Then
Range("D2").Offset(xCount, 0).Value = xVal
xCount = xCount + 1
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("C2").Address Then
xVal = Target.Value
End If
End Sub




Try it out please.
I cant understand why it wont work when connected.
Any ideas ?
StellaBot
Posts: 818
Joined: Thu Jan 26, 2017 11:52 am

Edit.
Try changing values in C2 and watch D2, D3 ,D4 etc
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

I haven't got BA but if you change the lines

Code: Select all

If Target.Address = Range("C2").Address Then
to

Code: Select all

If Not Intersect(Target, Range("C2")) Is Nothing Then
it should at least kick off the routines when connected to BA
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

The cells are updated in blocks and you can't target a single one. Try changing it to "$C$2:$C$6"


put this into your sheet and open the immediate window

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

Debug.Print Target.Address

End Sub
and you'll see the blocks as they arrive
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”