Solved Macro to display message box once for each cell

Microsoft Excel 2010 - complete product...
March 14, 2018 at 06:00:01
Specs: Windows 7, 2,4 GHz / 4 GB
Hello,

In an excel file with various columns, I have a formula somewhere in column V that calculates a percentage. If this percentage is over 3% (0.03) or under -3% (-0.03), i want to display a vbOKOnly + vbCritical dialog box to say some things, including to add some cell values to the msgbox.

The macro I made displays the msgbox for all cells in range meeting the criteria, but also for those where percentage cell is blank (contains formula though). It displays the msgbox each time I edit any other cell.
I have -3% in V3 and it doesn't display a msgbox for that. Cells with conditions met have +4% (V2), -88% (V4), -5% (V5) and +45% (V6) and msgbox is displayed for each, then for all the empty ones up to V22.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

For Each cell In Range("V2:V22")
     If cell.Value > 0.03 Or cell.Value < -0.03 Then
MsgBox "ATTENTION! Cargo no. " & cell.Offset(0, -20).Value & " to " & cell.Offset(0, -19).Value & _
" exceeds the maximum accepted limit of 3% between weighted and system values!", vbOKOnly + vbCritical
     End If
Next
End Sub

I'll sum up the conditions I have, to get a better picture:
- this sheet will be completed all year long, up to 50 rows a day (or more)
- i don't have a problem with making some helper columns if it helps make the process easier or the macro faster
- column V is full of formulas from V2 up to V5000 or so (made the range up to V22 for test purposes, of course)

And now here's what I would like it to obtain:
- when the percentage is displayed in a cell in V, if it's >3% or <-3%, the msgbox should appear saying stuff, including offset cell values --- it already does this, but
- when a cell is blank, it shouldn't display a box (even though I have a formula in V and cell is not actually blank)
- the msgbox should be displayed only when the value in column V changes, not when I make other changes to the sheet in any other cells
- upon displaying the msgbox, it should not appear again for the same cell unless I modify the data and percentage changes
- a module created in personal.xlsb would suit me better as I don't have to save the excel file as macro-enabled, but if not possible it's ok.

Of course, there is an easier way to alert a person via conditional formatting and some colors, green if OK, red if not, but I would like something more powerful like a message alert, which also comes with sound.

Everywhere I looked for information it is segmented and written using a different thinking process and for now it's hard for me to match them all together and make them work properly.

So I'm hoping someone can help, once again!
Thank you in advance!

message edited by Mrrrr


See More: Macro to display message box once for each cell

Report •

#1
March 14, 2018 at 08:16:49
re: "- when a cell is blank, it shouldn't display a box (even though I have a formula in V and cell is not actually blank)"

It would help if we knew what formula you are using. Since VBA is not recognizing the cell as empty, the actual value in the cell would help us offer solutions. For example, "" (Nothing) will always be >.03 so the If-Or instruction in your code will always be True. Post your formula and we might be able to tell you if you should change the formula or change the code.

re: "- the msgbox should be displayed only when the value in column V changes, not when I make other changes to the sheet in any other cells"

You have not limited the Range that will cause VBA to execute your "Loop" instructions. It is going to execute the loop with any and every change to the worksheet, whether it be user initiated or done via VBA. I would limit the execution of the loop to only those times when you change a cell that causes the values in Column V to change.

In case you aren't aware, a Worksheet_Change macro will not be triggered by a change in the result of a formula. That is not considered a "change" so you can't use it monitor Column V directly. You need to monitor some other range, such as the cells that might cause Column V to change.

re: "- upon displaying the msgbox, it should not appear again for the same cell unless I modify the data and percentage changes"

In that case, you probably shouldn't be using a loop. Why look at every cell if you don't want to display a message for every cell? Again, I would set up the macro such that it only presents the MsgBox when you change a cell that causes a cell in Column V to exceed the specified limits. Without knowing your process. i.e. what causes Column V to change, we could only guess a possible solution and that's often a waste of energy. Tell us more about your process and perhaps we can be more specific.

re: "- a module created in personal.xlsb would suit me better as I don't have to save the excel file as macro-enabled, but if not possible it's ok."

Since a Worksheet_Change macro must be stored in the Sheet module for the Sheet in which the changes are a being made, I don't that you'll be able to "automatically" display the MsgBox based on a change to the sheet without the code (or some code) residing in the workbook itself. Again, if we knew more about your process, we might be able to offer some other solution.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
March 14, 2018 at 23:48:28
re: "It would help if we knew what formula you are using."

I will tell you the cascade of values / formulas up to column V, which are:
- Q - the weight of a truck when entering the premises, brought by a formula from another excel file
- R - the weight of a truck when exiting the premises, brought by a formula from another excel file
- S - difference between the two weights above: =IF(ISERROR(R2-Q2);"";R2-Q2)
- T - weight given by the system (SAP) manually inserted for now
- U - =IF(ISERROR(S2-T2);"";IF(T2=0;"";S2-T2)), which sometimes is negative and sometimes is positive
- V - =IF(ISERROR(U2/S2);"";U2/T2)

So the cells that actually make V2 tick leaving U2 out of the equation, are in this simplified formula obtaining a percentage: =(S2-T2)/T2

I'm saying that a percentage might change because the truck where the percentage is high / low enough would be stopped at the gate and taken apart. People would thus find out where and why there is a big difference between weighed mass and system mass and eliminate the problem. The percentage in V could still be above / below limits so it would still display the box, but it would be an accepted percentage since the truck was analyzed. But nonetheless the box would be displayed once again, the condition being met (the % is out of the interval -3% to 3%).


Report •

#3
March 15, 2018 at 05:54:34
✔ Best Answer
re: "- T - weight given by the system (SAP) manually inserted for now"

As far as I can tell, the manual change to Column T is the only means to meet your requirement of only presenting the MsgBox when a specific cell in Column V exceeds the limits.

In addition, the code is only going to check the value in Column V after the manual change is made to Column T, so all of the other information required for the updated calculation has to already be in the sheet. In other words, if the user changes Column T, the code will be triggered and check Column V. However, if the user then changes a value in one of the other files which then changes the value in Column V because of a recalculation, the Worksheet_Change code will not fire.

It would take some code in all files to make sure that any change to any input forces a check of Column V.

Let me know of this works for you:

Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if change was made to Column T
'and Column V contains a number

  If Target.Column = 20 Then
    If IsNumeric(Range("V" & Target.Row)) Then
    
'If True, present MsgBox based on criteria
      With Range("V" & Target.Row)
      
        If .Value > 0.03 Or .Value < -0.03 Then
          MsgBox "ATTENTION! Cargo no. " & .Offset(0, -20).Value & " to " & .Offset(0, -19).Value & _
                 " exceeds the maximum accepted limit of 3% between weighted and system values!", _
                   vbOKOnly + vbCritical
        End If
    
      End With
  
    End If
  End If

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Related Solutions

#4
March 19, 2018 at 07:00:06
Hey, sorry for the delay, I had a few really busy days and I couldn't get back to you. I tested the code now and it works great, thank you.

As I said before, the weight is manually inserted "for now", which means I want to automate it because only the logistics people have access to the system, but especially the gate keeper must be alerted, and he doesn't have access to the system to check the weight that must be in the truck and compare it to the weighed mass.

Let me explain a little better what I have:
- 2 excel files and I will have a third for extracting the SAP report with system weights (currently inserted manually in column T by the logistics people - which isn't very effective because they don't insert it on the spot, when the truck is on the scale or soon after, but later when the truck is already gone - we need the alert right away so I must automate it (logistics people have other things to do, but they still need to be alerted whenever the truck weight exceeds the system weight check by at least +/-3%):
- 1st excel is located at the gate keeper who can stop the truck if an alert is displayed; the gate keeper is not allowed to have access to the system so he cannot get the weight needed to obtain the V column percentage
- the other excel file is located on a PC at the logistics department where there is a possibility to use the SAP system to extract the system weight of the goods
- the gate keeper inserts the truck weights manually, upon weighing the truck at entry and exit, so in his excel file it would be easy to adapt the macro you made to the column where he types the exit weight, so when the gate keeper inserts that value the alert can pop up for column V (thanks again for the code!)
- the problem is on the PC with access to the system (logistics people) where practically all values from column Q to V will be automatic, either as formulas in the current file or extracting data from the gate keeper's excel file and from the SAP report excel file - the logistics people will not insert any data at the moment of the truck passing by, but they need to be alerted

What would you suggest I do if I had to do something like that? Two questions arise, 2 possibilities I can think of:

1. Would it be possible to make a macro in the logistics people excel file but with a condition like "if the value in column R (exit weight) or T (system weight) is >1, then pop up alert" (weight of the truck will always be larger than 1 kg of course)? Would it work even if the value is brought from another excel file?
or
2. Would it be possible to connect the macro to the value of the truck weight inserted manually by the gate keeper in that other excel file?

Thanks in advance for your time and efforts.


Report •

#5
March 19, 2018 at 19:49:37
First, please keep in mind that you live your process on a daily basis, while I only understand it by what you post in the forum. Forgive me if my head stars to spin when you talk about SAP and truck scales and gate keepers and who has what privileges in which of the three files and who doesn't and when alerts need to be presented and to whom, etc.

re: 2. Would it be possible to connect the macro to the value of the truck weight inserted manually by the gate keeper in that other excel file?

Are you asking if a manual change to one workbook could automatically cause an alert in another workbook? A workbook that is already open on someone else's computer?

You could try something like this...

The following site explains how to have a macro run via a timer. Maybe you could use a timer to check the values in a range and show the alert. The link to the site is mainly for the instructions. Some simple test code is included below.

https://www.datanumen.com/blogs/upd...

Sub Auto_Open()
    AlertTime = Now + TimeValue("00:00:10") 'interval in 10 seconds
    Application.OnTime AlertTime, "MsgEvery10" 'call Sub MsgEvery10, below
End Sub

Sub MsgEvery10()
    MsgBox "Alert"
    AlertTime = Now + TimeValue("00:00:10") 'move trigger to 10 seconds
    Application.OnTime AlertTime, "MsgEvery10"
End Sub

As a set-up, copy the current values from the link formula, as values to a Helper column. The timer code compare each new formula value to the Helper column value. If the values are different and the new exceeds the thresholds, pop up the alert and then copy the values again. I think (though untested) that would only result in an alert once per update for each value since you wouldn't test the thresholds if both columns were equal, i.e. unchanged.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#6
March 20, 2018 at 00:07:42
Sorry about that, didn't know how else to put it. Thought that more detail is better than no detail.

Interesting idea with the timer, I'm gonna try that and also check out that website.

Thanks for all your help!
Best regards!


Report •

Ask Question