CODEWITHSUNDEEP
excelvba
intrigued_66
intrigued_66

Reputation: 17248

VBA Checking a cell for change

My scenario is as follows:

I have an addin call which queries the server using a formula in cell(1,1)). The server sends a response back, changing the value of cell(1,1). My function detects this and then calls a display function, beginning at cell(6,1) to paste the data which was sent to the cashe by the server. The data could be X Rows and Y columns big.

This is what I have:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.HasArray = False Then
        If Target = Sheets("My_Sheet").Cells(1, 1) Then
            If InStr(Target.Value, "ServerGettingData") = 0 Then
                Sheets("My_Sheet").Cells(6, 1).Formula = "=My_Display_Call(A1)"
                Sheets("My_Sheet").Cells(6, 1).Calculate
            End If
        End If
    End If

End Sub

The first IF statement is because I was getting type mismatches in the second IF statement, when the array data was being pasted into the sheet. However, I am still getting type mismatches on the same line.

This is all to simulate synchronous activity with two asynchronous calls.

EDIT The point of the instr IF statement is to only detect the change when the server returns data, as opposed to fetching the data. When fetching data the cell says "ServerGettingData" and once the data has been retrieved, it says something else. It is the latter I am trying to detect.

Upvotes: 0

Views: 520

Answers (1)

InContext
InContext

Reputation: 2501

Add the below in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Cells(1, 1)) Is Nothing Then

            If InStr(Target.Value, "ServerGettingData") = 0 Then
                Sheets("Cancelled").Cells(6, 1).Formula = "=My_Display_Call(A1)"
                Sheets("Cancelled").Cells(6, 1).Calculate
            End If

    End If

End Sub

Upvotes: 1

Related Questions