2

I am new to VBA, I have an excel sheet that contains data on stocks.

  • column A - stock ticker
  • column B - stock name
  • column C - "the variable number"，the number that changes all the time.

When the number in column C is between [-4;4], then a message box should pop up showing the stock ticker in column A & the name in column B, with the number in column C.

For example, if at this moment, Google value in column C is -1, then the message box will show: Google Alphabet Inc -1 [in this example: Googl(column A)、Alphabet Inc(column B), -1 (the change value is column C).

Please help me out, thank you so much!

Ilya Cherevkov
  • 1,743
  • 2
  • 17
  • 47
Laura Lee
  • 35
  • 3

1 Answers1

0

Insert this Event change listener to your Sheet (e.g. Sheet1). Target.Column is your Column C (pass your correct column number)

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.column = 3 And Target.value >= -4 And Target.value <= 4 Then
    Call MsgBoxMacro(Target.value, Target.column, Target.row)
  End If
End Sub

Then add new macro (right click -> insert -> new module). Pass cell value to it.

Sub MsgBoxMacro(value, column, row)
    MsgBox "Ticker: " & Cells(row, column - 2) & vbNewLine & "Stock Name: " & Cells(row, column - 1) & vbNewLine & "Variable Value: " & value
End Sub
Ilya Cherevkov
  • 1,743
  • 2
  • 17
  • 47
  • Thank you for helping but It does not seem to work. Let me clarify: i need the vba to automatically go to column A & B to find the stock ticker and the name, along with column c value to show in msgbox. base on what you have written above the msgbox will only show "column c value is" along with the column c value. thank you again! – Laura Lee Mar 16 '16 at 12:36
  • it works for me. ensure you copied listener to Sheet Object and macro to Module – Ilya Cherevkov Mar 16 '16 at 12:39
  • i am sorry, can you please explain "ensure you copied listener to Sheet Object"? – Laura Lee Mar 16 '16 at 12:43
  • When you open VBA Editor, you see list of Sheets and Modules. You need to copy first piece of code into the sheet, where you have your stock data and columns A,B,C – Ilya Cherevkov Mar 16 '16 at 12:47
  • i don't know what I'm doing wrong. I followed your instruction copied the first piece of code into the sheet where i have the stock data. then i went over to the left right click and created a new module, then i copied the sub msgboxmacro code you have designed. what do i do afterwards? i pressed save, and press F5 try to see if it works but nothing happened. am i doing it right? or missing any steps? thanks again! – Laura Lee Mar 16 '16 at 12:57
  • First ensure you have correct column number (in my code 3). Then ensure, MsgBoxMacro finds columns A and B correctly to fetch their values. I just assume A and B preceding C, but you need to substract correct number. Finally, you don't need to press F5, just go to your sheet and try to change value in your column manually – Ilya Cherevkov Mar 16 '16 at 13:00
  • Do I understand correctly, that you have DDE server that fetches stock data in real-time and you need to capture it, calculate C column and show message box in case it meets your requirements? Or you just have static data and want to show messagebox? In this case, my code will not work, it only captures realtime changes. If you want ot find which rows fall under your criteria, you can just do simple Excel formula – Ilya Cherevkov Mar 16 '16 at 13:03
  • what is i have Column D, (a different critiria), which needs exact same instruction as Column C. I have trouble putting two codes together, so that that when column D value match the critria it will also pop up in msgbox column A & B to find the stock ticker and the name, along with column D value. I need both C & D to pop up msgbox. Thank you. – Laura Lee Mar 17 '16 at 08:07