Tech Support banner
Status
Not open for further replies.
1 - 9 of 9 Posts

·
Registered
Joined
·
11,131 Posts
Discussion Starter · #1 ·
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
 

·
Security Manager, Analyst , Rangemaster, TSF Acade
Joined
·
39,538 Posts
Hi Chris

This should do it. On the relevant sheet, right click the tab, then copy and paste in this code:-
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:
 

·
Registered
Joined
·
11,131 Posts
Discussion Starter · #3 ·
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:
 

·
Registered
Joined
·
11,131 Posts
Discussion Starter · #4 · (Edited)
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:
 

Attachments

·
Registered
Joined
·
11,131 Posts
Discussion Starter · #5 ·
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??
 

·
Registered
Joined
·
11,131 Posts
Discussion Starter · #7 ·
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.
 

Attachments

·
Security Manager, Analyst , Rangemaster, TSF Acade
Joined
·
39,538 Posts
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).
Code:
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!
 
1 - 9 of 9 Posts
Status
Not open for further replies.
Top