Change Cell Alert

[Dave]

I am very new to VBA and need some help regarding some codes. Here is the
complete scenario:

I have 3 worksheets:

Worksheet 1 = Master Page
Worksheet 2 = Data 1
Worksheet 3 = Data 2

In worksheet 3 I have got 2 columns. The first column has the name of the
companies and the second column has buy and sell signal which is based on
certain criteria and this cell updates

itself automatically in real time.

I would like to have a VBA Code which just flashes me a messagebox when ever
there is some change in Column 2 of Worksheet 3. The message should display
the updated value od column 2 (in

this case "buy" or "Sell" from Column 2 and also the name of the company
from the adjacent Column 1)

The message box should appear as follows:

"Microsoft changed to BUY"

Here Microsft is actually the value in Column 1 and the BUY is in Colummn 2.

The message box should appear even if I am on worksheet 1 and not on
worksheet 3.

 

[Ryan H]

Put this in the Worksheet 3 Module. Here you are using the Sheet3 Change
Event which will fire each time any cell is changed in the worksheet. The
Target is the cell that was changed. The code tests if the Target is located
in Col. B and if it is then the message box will show. Hope this helps! If
so, let me know, click "YES" below.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 2 Then
MsgBox Target.Offset(0, -1).Value & " changed to " & Target.Value,
vbInformation
End If

End Sub

 

[Devesh V]

Ryan

I do not completely understand your program. I tried to put the code in worksheet 3 and the value changed from a buy to neutral (in column B), there was no alert or msgbox whatsoever. Just in case to make if I am missing something I will make it clearer, the column looks like this :

Col. A Col. B
MSFT BUY
GOOG SELL

The cells in col. B updates itself through the New York stock exchange via a DDE Link. So it feeds in data automatically (every single second).

Now the buy can change to Sell at any point of the day during market hours. I want the alert to tell me immediately the price changes.

Now this alert should come to me even if I am working on Worksheet 1.

Hope this helps

Thanks
Devesh



Ryan H wrote:

Put this in the Worksheet 3 Module.
06-Jan-10

Put this in the Worksheet 3 Module. Here you are using the Sheet3 Chang
Event which will fire each time any cell is changed in the worksheet. Th
Target is the cell that was changed. The code tests if the Target is locate
in Col. B and if it is then the message box will show. Hope this helps! I
so, let me know, click "YES" below

Private Sub Worksheet_Change(ByVal Target As Range

If Target.Column = 2 The
MsgBox Target.Offset(0, -1).Value & " changed to " & Target.Value
vbInformatio
End I

End Su
-
Cheers
Rya

:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET: Prevent Default Frameset on Refresh or F5
http://www.eggheadcafe.com/tutorial...b892-4824c10da516/aspnet-prevent-default.aspx