Msgbox with two different criteria

Question

I have an excel sheet, with column both A、B、C、D.

Both C & D number changes all the time (they have different criteria), since it calculates by stock data that fetches in real-time.

I need message box to pop up both when C & D matches my target value, and showing the the ticker in column A, the name in column B, and the number in C/D.

With the help I know the code when there is only column C:

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

Sub MsgBoxMacro(value, column, row)
    MsgBox "Ticker: " & Cells(row, column - 2) & vbNewLine & "Stock Name: " & Cells(row, column - 1) & vbNewLine & "Variable Value: " & value
End Sub

I don't know what to do, when I want to add column D data into the code. (so i can have message box pop up when D number reaches the criteria) please help.

Thank you!

Nathan_Sav · Answer 1 · 2016-03-17T07:17:25.890

0

Something like this, not far from what you had. This will go in the worksheet where the changes are to be made.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
    If ((Target.Offset(0, -1).Value > -4 And Target.Offset(0, -1).Value < 4) And _
                        (Target.Value > -4 And Target.Value < 4)) Then
        '   Msgbox here
    Else

    End If

End If
End Sub

edited Mar 17 '16 at 07:17

answered Mar 17 '16 at 07:12

Nathan_Sav

8,466
2
13
20

Thank you for your help. So changing the the code you wrote into the Private Sub wWorksheet_change(ByVal Target As Range). However, what about the Sub MsgBoxMacro code?? because base on what i have right now, C will have MsgBox "Ticker: " & Cells(row, column - 2) & vbNewLine & "Stock Name: " & Cells(row, column - 1). But D will need MsgBox "Ticker: " & Cells(row, column - 3) & vbNewLine & "Stock Name: " & Cells(row, column - 2)...where do i place the code? Thank You! – Laura Lee Mar 17 '16 at 07:16
It shows error, please help me. do i need to delete my sub module? I have problem putting two codes together(both c & D) – Laura Lee Mar 17 '16 at 07:31
this is what i written: Private Sub Worksheet_Change(ByVal Target As Range) If Target.column = 32 And Target.value >= -4 And Target.value <= 4 Then Call MsgBoxMacro(Target.value, Target.column, Target.row) If Target.column = 33 Then If ((Target.Offset(0, -1).value > -4 And Target.Offset(0, -1).value < 4) And _ (Target.value > -4 And Target.value < 4)) Then MsgBox "ticker: " & Cells(row, column - 32) _ & vbNewLine & "name: " & Cells(row, column - 31) _ & vbNewLine & "variable: " & value Else End If End Sub – Laura Lee Mar 17 '16 at 07:31
If you're already checkkng the column is 33, then there is no need for the maths as it can only be 1 column, so reference cells(row,1) instead of column-32, as it can only equate to 33-32, I think you need to use elseif, or end the 1st if, so if 1 elseif 2 or if 1 end if if 2 end if. I think you need if target.column=32 or target.column=33 then...... – Nathan_Sav Mar 17 '16 at 07:41
still not working. i tried to add in the code in both private sub worksheet and in my sub module, but either way, the column D message box wont appear when the value meet the criteria. i am not sure what am i doing wrong please help. thank you. – Laura Lee Mar 17 '16 at 07:57
You are using columns 32 & 33, why not 4 like in my post? – Nathan_Sav Mar 17 '16 at 08:07
i am sorry. this is because my excel sheet the the changing number (column D & C) is actually column AF & AG, i just wanted to make my question simple. thats why i used column A 、B、C、D. – Laura Lee Mar 17 '16 at 08:09
Private Sub Worksheet_Change(ByVal Target As Range) If Target.column = 32 And Target.value >= -4 And Target.value <= 4 Then Call MsgBoxMacro(Target.value, Target.column, Target.row) End If End Sub – Laura Lee Mar 17 '16 at 08:11
Sub MsgBoxMacro(value, column, row) MsgBox "Ticker: " & Cells(row, column - 31) _ & vbNewLine & "Name: " & Cells(row, column - 30) _ & vbNewLine & "Variable: " & value End Sub – Laura Lee Mar 17 '16 at 08:12
The above codes worked perfectly, when i only have one changing column [column c /column AF], now i have a new changing column [column D /column AG], sorry if i wasnt being clear. – Laura Lee Mar 17 '16 at 08:14

score 0 · Accepted Answer · answered Mar 17 '16 at 09:26

0

By passing another parameter to the function MsgBoxMacro will solve your problem:

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

Sub MsgBoxMacro(value, column, row, counter)
    MsgBox "Ticker: " & Cells(row, column - 31 - counter) & vbNewLine & "Stock Name: " & Cells(row, column - 30 - counter) & vbNewLine & "Variable Value: " & value
End Sub

Hope this helps.

answered Mar 17 '16 at 09:26

Mrig

11,612
2
13
27

Thank you for your help! It works, except that for column AF[32] & column AG[33], i need to have it show differently for variable name, because column32 & 33 have their own heading (Variable name). Please help, sorry i didn't make my question clear. – Laura Lee Mar 18 '16 at 00:47
Do you mean that name of column AF and column AG needs to be shown? – Mrig Mar 18 '16 at 07:47
yes, I need the name of column AF & AG to be shown thank you! – Laura Lee Mar 21 '16 at 01:24
You have to make one change in the code(where message box is displayed) to show the column name: – Mrig Mar 21 '16 at 04:44
@LauraLee - here is the code: MsgBox "Ticker: " & Cells(row, column - 31 - counter) & vbNewLine & "Stock Name: " & Cells(row, column - 30 - counter) & vbNewLine & Cells(1, column) & ": " & value – Mrig Mar 21 '16 at 04:51
Thank you Mrig, it works! but the only problem now is if i hand type, for example: "3" in column AF, then the msgbox pop out and everything looks perfect. Yet my column AF data is link to DDE data, for some reason the msgbox won't pop out when the data numbers change to "4 ~ -4" criteria. please help if is possible, thank you. – Laura Lee Mar 21 '16 at 05:08

Msgbox with two different criteria

2 Answers2