Code limiting gizmo

[Fixador]

Code: Select all

Private Sub worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("CH10:CL50")) Is Nothing Then
Debug.Print "entered arena"
End If
End Sub

Am i going mad ? i expected this peice of code to return "Entered arena" whenever a change occurs in the range CH10:CL50

it isnt .............. anyone any ideas ?

cheers

[phrenetic]

Should that be MsgBox "entered arena" rather than Debug.Print?

Alistair

[Fixador]

hello phrentic, b4 yr post, i got a tune out of it

i suspect Excel gets its knickers in a twist at my lousy coding

just went to try my version again , b4 coming on here, and it - it wouldnt work - again . Which is a bit odd ...........

However, my vba screen is showing 3 projects open at the mo, whereas , there is ownly actually one open Which is a bit odd ........... since they were all opened in seperate instances of Excel - and closed.....

Just tried again - aggh ! in a seperate instance of Excel - and it works ..

Code: Select all

Private Sub worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a1:g10")) Is Nothing Then ' only passes if a change has occurrd in the range contents
   ' Application.EnableEvents = False
        Debug.Print "got in"
   ' Application.EnableEvents = True
Else:
    Debug.Print "got out"
    'Exit Sub
End If
End Sub


and - golly gosh - the vba code shows only 1 project open......

wheres the exit ?

[Ian]

What is it that is causing CH10:CL50 to change ? It won't work if it's formulas.

[Fixador]

i was just typing in any old number , then hit return

[Fixador]

this code is ok , if number is typed in, or put in by vba code, it doesnt work if it is a sheet calculation

the prior code i put into an otherwise empty workbook and worksheet - and it worked fine

However, putting into a worksheet where BA is exporting to , and it never works

so far, all i can determine is that if

Code: Select all

If Not Intersect(Target, Range("CH10:CL50")) Is Nothing Then

is changed to:-

Code: Select all

If Not Intersect(Target(1,1), Range("CH10:CL50")) Is Nothing Then

Code: Select all

debug.print Intersect(Target(1,1), Range("CH10:CL50"))

returns the race details from cell A10 ( i export BA data to start from A10 )

i suspect this "If not intersect " is going to be no use here

[PeteB]

The test I use is:

Code: Select all

            if (Target.Column == 1)
            {
                ProcessPrices();
            }
            if (Target.Column == 20)
            {
                processBets();
            }


(admittedly this is C# rather than VBA, but the test should work the same way)

[Fixador]

Thanks for input PeteB........

tried yr suggestion with code:-

Code: Select all

Private Sub worksheet_Change(ByVal Target As Range)
If Target.Columns <= 16 Then
    Debug.Print " process BA inputs"
    'so do code for data changes in this area - BA inputs
End If
If Target.Columns > 20 Then
    Debug.Print "do selection process "
    'so do code for data changes in this area- selection process
End If
End Sub


Got some odd results - BUT - consistently odd

If i entered a numeric in say A10 , then the anticipated "process BA inputs" was obtained

If i entered a character at A10 , then i got "do selection process "

And it was the opposite outcomes if from inserting numerics/characters in say cell Z10



Alas , it was not consistent , when i dropped the string "a1b1c1" into A10 then A10. It gave the same outcome

Target.Columns takes the 'value' of whatever is entered into the cell
So, to test :-

Code: Select all

If Target.Columns > 20 Then


is testing the contents of the cell, not where the cell is located


my vb unfortunately is based on 1970's Basic, this 'joined' up VBA is a trial .......

[PeteB]

That should be Target.Column not Target.Columns

Target.Column will return an integer, Target.Columns returns an Excel.Range object representing the columns in the changed range - who knows what it means to ask whether an Excel.Range object <= 16 or > 20 - this wouldn't even compile in C# - one of the reasons I hate VBA!!

[Fixador]

Whooops ! your right !

where's that dunces hat

thats much better

kickin and screamin in protest , i get dragged into the 21st century