message box

[Guest]

Hi All!

I'd like to create the following-

I have a cell (D31) that is computed via formula that depends on the values in numerous other cells. It is a dollar amount and I would like a message box to pop-up and say "Target Reached" when D31 exceeds 1,000,000. So, when I change the values in the cells that change D31, D31 will eventually exceed 1,000,000, and then the message box should appear. Any help is greatly appreciated!

 

[Tom Morales]

Yo, Anon
Right-click the worksheet tab, select "View Code", and insert this macro:
Private Sub Worksheet_Change(ByVal Target As Range)
If [d31].Value > 1000000 Then _
MsgBox "Target Reached."
End Sub
Tom
This message was edited by Tom Morales on 2002-02-25 11:26

 

[Mudface]

Sorry, Tom, posted just after you

Try the following: -

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Cells(31, 4).Value > 1000000 Then MsgBox "Target Reached!"

End Sub

To enter the code, right-click on the sheet tab where you want the message to appear, select 'View Code', this will open up the VB editor and just paste the above into the right-hand code window.
This message was edited by Mudface on 2002-02-25 11:28

 

[Guest]

Not working...I think because those cells that I used to calculate D31 are derived from other cells, etc. Therefore, when D31 ends up being larger than 1,000,000, nothing happens....any workarounds? sorry for the confusion, I appreciate the help.

 

[Mudface]

Hmm, sorry, both work OK for me, have you pasted the code into the right place?

 

[Guest]

Yeah, I think the code is in the right place...the thing is that I can get your code to work if for example, I have the following scenario:

C2*C3= C31, where I can manually enter numbers into C2 & C3. Then, if I enter 10000 in to C2 and 10000 into C3, C31=100,000,000. In this case, I do get the message box as I would like it. The problem is, this is not how my model is set up. In my model, C2 & C3 are calculated based on other values in other cells, and in addition to this, there is no manual entry of numbers anywhere...combo boxes are used. So, I am not sure if there is some method to deal with all of this confusion or not! I'm sure you are thoroughly confused at this point! But if by chance you have any ideas...GREAT!

 

[Tom Morales]

Jeez, Anon, you're right. You learn something new every day.

I think the way around this is to assign the macro to your combobox or comboboxes, such as:

Private Sub ComboBox1_Change()
If [d31] > 1000000 Then _
MsgBox "Target Reached"
End Sub

I tried it, and it works.

Tom

 

[Guest]

Thanks, now what if I have like 5 different combo boxes instead of just one? How can I incorporate them into the code to cover all bases? I appreciate it...

 

[Tom Morales]

If you're using comboboxes from the controls toolbar, you would have to add that code to each combobox or, alternately, you could put the code on a regular module, and have each combobox call that sub on its change. eg, on your worksheet module, put:

Private Sub ComboBox1_Change()
whatever
End Sub

Private Sub ComboBox2_Change()
whatever
End Sub

...and so on.

And on your regular code module:

Sub whatever
If [d31].value > 1000000 Then _
MsgBox "Target Reached"
End Sub

If you're using comboboxes from the forms toolbar, simply link each combobox to sub "whatever".
Tom
This message was edited by Tom Morales on 2002-02-26 11:43