Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: message box

  1. #1
    Guest

    Default

    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!

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Richland, Washington
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Guest

    Default

    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.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    Guest

    Default

    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!

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Richland, Washington
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Guest

    Default

    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...

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Richland, Washington
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •