Trying to trap if a cell has changed value in real time

[gautammalkani]

Hi

I would like my MS Excel template to display announcements that I store on a web server. I have created two dummy cells (hidden from the user). If cell B1 has changed value, a message box pops up displaying that the announcement. Then A1 is set to B1. Ideally, I would like a macro that automatically compares a1 to B1 in real-time. Is that possible in excel?

is there a cell change event that I am not aware of? Any help would be much appreciated.

Thanks

Gautam

 

[gautammalkani]

Hi All

Here is my attached code that doesnt seem to work

Ideally I would like to do the folllowing:
Basically B1 gets the data from the webserver
if A1 and B1 are not alike, a message box pops up
B1 is set to A1.
However, I cant get A1 and B1 constantly being checked with eachother. please let me know if there is anything wrong with my code:

Sub auto_open()

' Run the macro DidCellsChange any time a entry is made in a
' cell in Sheet1.
ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange"

End Sub


Sub DidCellsChange()
Dim KeyCells As String
' Define which cells should trigger the KeyCellsChanged macro.
KeyCells = "A1:G10"

' If the Activecell is one of the key cells, call the
' KeyCellsChanged macro.
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then KeyCellsChanged

End Sub

Sub KeyCellsChanged()
Dim Cell As Object
' If All and B11 are different
For Each Cell In Range("A11:B11")
If Range("A11").Value <> Range("B11").Value Then

' If A11 and B11 are not equal, send an announcement

response = MsgBox(Range("B1").Value, vbOKOnly, "Supplier Portal Announcement", "DEMO.HLP", 1000)
Range("B11").Value = Range("A11").Value
Else
' Set the subscribed value from B11 to A11


End If
Next Cell

End Sub

Thanks

Gautam

 

[Rofeu]

Hi,

you have to use the SelectionChange Event of the worksheet.

Go to the codepane of the sheet and put your code like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Cells(1, 1).Value = Cells(1, 2).Value Then

'your code

End If

End Sub

Cheers,

Roel

 

[gautammalkani]

Hi Roel

Thanks appreciate the help

Gautam

Here is a snippet of what i did that worked:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
ThisRow = Target.Row
If Target.Value <> Range("B1").Value Then
Range("b1").Value = Range("a1").Value
response = MsgBox(Range("B1").Value, vbOKOnly, "Supplier Portal Announcement", "DEMO.HLP", 1000)

Else

End If
End If
End Sub