worksheet change using ranges

davie1982

Board Regular
Joined
Nov 19, 2007
Messages
170
Office Version
  1. 365
  2. 2019
Hiya, i'm currently attempting to write in a worksheet change event.

In column C I have a number in each row. This number is the 'maximum' (it differs a lot) amount of product that can be put in. In column E is the number of products going into that row. For example:

In column C1 the number is 3.

In column E1 someone puts 2 products in there.

This is fine.

What i want is for when someone tries to do this:

In E1 someone tries to put 4 products in there. I'd like for a msgbox to pop up -

Code:
msgbox "Please do not put more than " & range("c1").value

Then i'd like E1's contents to be cleared.

E:E is the monitored range.

I just can't get my head around this, can someone help please?
 
Last edited:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi

Something like this:

Code:
Private Sub Worksheet_Change(byVal Target As Range)
If Target.Count > 1 Then Exit Sub   'this means only recats to single cell changes
If Target.Column <> 5 Then Exit Sub
If IsNumeric(Target.Value) Then
  If Target.Value > Me.Cells(Target.Row,"C").Value Then
    Application.EnableEvents = False
    Msgbox "Please do not put more than " & Me.Cells(Target.Row,"C").value
    Application.Undo
    Application.EnableEvents = True
  End If
End If
  
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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