# Macro to display alert



## carsey (Aug 19, 2006)

Im trying to get a macro on excel to appear when something is below a certain number. Such as if the number is below 3 then a message will appear saying that a order needs to be placed for example. I know that a validation rule could be used here but I would prefer it if a pop-up appeared alerting me of it being below the number.

Is this possible to do??

Many warm regards,

Chris


----------



## Glaswegian (Sep 16, 2005)

Hi Chris

This should do it. On the relevant sheet, right click the tab, then copy and paste in this code:-

```
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub

If Target.Value < 3 Then
    MsgBox "Warning!  The value is too low!", vbInformation, "Warning"
End If
End Sub
```
Remember to change the cell reference to suit. You'll probably want to change the actual wording of the message as well. :grin:


----------



## carsey (Aug 19, 2006)

When I copy and paste that...i loose my validation rule which tells me if more needs to be ordered. 

For example.

*In Stock*
50Gphx cards. 

*Sold*
45

Now, when this gets below 10 or whatever, I have a validation rule that will prompt me to order more for example. I want the popup to appear aswell. So where does the coding go for the macro?? Does it just get copied and pasted all into 1 cell. (CTRL + V, CTRL +C in cell E6 for example)

Its really confusing, first time ive done this type of stuff :smile:


----------



## carsey (Aug 19, 2006)

When I copy and paste that...i loose my validation rule which tells me if more needs to be ordered. 

For example.

**see attached**

Now, when this gets below 10 or whatever, I have a validation rule that will prompt me to order more for example. I want the popup to appear aswell. So where does the coding go for the macro?? Does it just get copied and pasted all into 1 cell. (CTRL + V, CTRL +C in cell E6 for example)

Its really confusing, first time ive done this type of stuff :smile:


----------



## carsey (Aug 19, 2006)

Sorry for the double post. I managed to get it to work for one cell only. To make it work over a range of cells (individual only) will I need to create a seperate code under the previous one and change the cell reference??


----------



## Glaswegian (Sep 16, 2005)

Sorry Chris - didn't see all your posts there. :grin:

Leave this with me and I'll look at, probably tomorrow.


----------



## carsey (Aug 19, 2006)

Right. I hope this workbook will help you understand. Ill attach it in a .zip folder.

I took that macro you gave me and put it into cell C4. You will see that when the value is below 8 or 3, that the error message appears telling more stock is needed. I have not managed to get it to work for the other products though, as there is a error.

What I need to know is how to get the rest of the products that are under the stated value, to display a message to order more.

I hope this makes it more clear.


----------



## Glaswegian (Sep 16, 2005)

Hi Chris

Had a quick look at this and re-written the code for you. A brief explanantion to help you understand...

I defined a *range*, in this case C4:C9, and assigned a *variable*. I then used *Intersect* to see if the cell that changed was in that range. If not, nothing happens. If yes, then a *Select Case* statement is used to test the cell address and issue the appropriate message box. I'll post the code here, but I've also attached your sample workbook. I wasn't very sure what value you wanted set for the message box to appear so I've just used 8 - you can change this to suit. You'll find the bold words above in Excel Help (which is pretty good).

```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range

Set myRng = Range("C4:C9")

If Intersect(Target, myRng) Is Nothing Then Exit Sub

Select Case Target.Address
    Case "$C$4"
        If Target.Value < 8 Then
            MsgBox "Order more PCI cards NOW!", vbCritical, "Warning"
        End If
    Case "$C$5"
        If Target.Value < 8 Then
            MsgBox "Order more monitors NOW!", vbCritical, "Warning"
        End If
    Case "$C$6"
        If Target.Value < 8 Then
            MsgBox "Order more cases NOW!", vbCritical, "Warning"
        End If
    Case "$C$7"
        If Target.Value < 8 Then
            MsgBox "Order more keyboards NOW!", vbCritical, "Warning"
        End If
    Case "$C$8"
        If Target.Value < 8 Then
            MsgBox "Order more mice NOW!", vbCritical, "Warning"
        End If
    Case "$C$9"
        If Target.Value < 8 Then
            MsgBox "Order more hard disks NOW!", vbCritical, "Warning"
        End If
    Case Else
End Select
End Sub
```
Cheers!


----------



## carsey (Aug 19, 2006)

Awesome...Thanks. Thats just what I needed. Many thanks for the effort. ray:


----------

