message box

G

Guest

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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top