Worksheet_SelectionChange(ByVal Target As Range)

[asking]

Hi all,

Using excel 2007 on vista

Does selection change sub only work if you manually change a cell?

I want to have a dynamic chart from input via API that updates a cell on my worksheet.

This is not happening.

The cell on my work sheet is changing ok via the api ~ cell F8 on worksheet 2

My worksheet sub in Worksheet1 is ~

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Sheet2").Range("F8") <> Sheets("Sheet1").Range("M5000").End(xlUp).Offset(0, 0) Then
Sheets("Sheet1").Range("M5000").End(xlUp).Offset(1, 0) = Sheets("Sheet2").Range("F8")
End If
End Sub

It actually works if I click on any cell manually and press enter.

Have I written the code wrong?
It pops up a box telling me "A formula in this worksheet contains one or more invalid references"

My named range formula for the table that generates the graph is ~

=OFFSET(Sheet1!$M$2,,,COUNT(Sheet1!$M$2:$M$500))

Cheers Skinman

 

[pgc01]

Does selection change sub only work if you manually change a cell?

Hi Skinman

No, it can also be fired when using code to activate a cell.

The problem (and you should check the api code) is that many times we don't allow events when we write the code.

 

[TryingToLearn]

I believe Worksheet_SelectionChange is literal in that you must select a cell other than the active one in order to trigger the code.

if current cell = B5 your code should do it's thing then select any other cell

i.e. range("B6").select

 

[asking]

Thanks very much for the answers given.

TryingToLearn
As the api updates quite regularly I needed the graph to update while the computer is unattended. Thanks anyway.

pgc01

Thanks for your reply, pity about that.

Cheers Skinman.

 

[pgc01]

Hi

Another thing that may happen is that the api does not select the cell.

If you want to write a value in a cell you don't usually selected, you just reference it. For ex. to change the value in A1 you don't have to select A1, you can use, for ex.:

Range("a1").Value = 3

In that case, have you tried the Change event?

 

[TryingToLearn]

I didnt' make myself clear as I did mean to be used in an unattended sheet.

Whatever your code is to update through the api, add a last line that will change the selection on the sheet and that will trigger the SelectionChange event.

 

[asking]

Sorry about the late reply, I have spent many hours on this and have finally worked it out.

Thanks for all the suggestions which helped me figure it out.

The solution was to have a change event in the sheet2 that was updating via internet (API)

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("Sheet1").Select
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-24]C[3]"
End Sub

This triggered the cell change in sheet 1, then some slight modification to my original code in worksheet1 module ~

Private Sub Worksheet_Change(ByVal Target As Range)

   'Do nothing if more than one cell is changed or content deleted

   If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

    If Target.Address = "$C$32" Then

       'Ensure target is a number before multiplying by 1

        If IsNumeric(Target) Then

            'Stop any possible runtime errors and halting code

            On Error Resume Next

                'Turn off ALL events so the Target * 1 does not put the code into a loop.

                Application.EnableEvents = False

                Target = Target * 1
                If Target <> Range("M5000").End(xlUp).Offset(0, 0) Then

                    Range("M5000").End(xlUp).Offset(1, 0) = Target
                        End If
                'Turn events back on

                Application.EnableEvents = True
                
            'Allow run time errors again

            On Error GoTo 0

        End If

    End If
       
End Sub

Then do the dynamic chart part.


Thanks again.