What's wrong with this?

litestream

Active Member
Joined
Jul 24, 2006
Messages
323
When I use the following code, it affects other cells also. How can I stop this?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Const limit = 100
Set rng = Range("C10")
If Target.Count > 1 Then Exit Sub
With Application
.EnableEvents = False
MsgBox "100 is the Maximum bundle size"
If Target > limit Then .Undo
.EnableEvents = True
End With
End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
You have to tell the code to only look at C10 (note the Intersect argument):

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Const</SPAN> limit = 100
    <SPAN style="color:#00007F">Set</SPAN> rng = Range("C10")
        <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#00007F">With</SPAN> Application
                .EnableEvents = <SPAN style="color:#00007F">False</SPAN>
                    MsgBox "100 is the Maximum bundle size"
                    <SPAN style="color:#00007F">If</SPAN> Target > limit <SPAN style="color:#00007F">Then</SPAN> .Undo
                .EnableEvents = <SPAN style="color:#00007F">True</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Note that you have no test if C10 is <> 100, so the MsgBox will always fire.

HTH,

Smitty
 

litestream

Active Member
Joined
Jul 24, 2006
Messages
323

ADVERTISEMENT

I think I figured it out, at least this seems to work :wink:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Const limit = 100
Set rng = Range("C10")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Range("C10") <= 100 Then Exit Sub
With Application
.EnableEvents = False
MsgBox "100 is the Maximum bundle size"
If Target > limit Then .Undo
.EnableEvents = True
End With
End Sub

Thanks again
 

litestream

Active Member
Joined
Jul 24, 2006
Messages
323
Limiting more than one cell value on a worksheet

I use this code (kindly assisted by some board regulars) but how would I go about limiting 2 different cells to 2 different values on the same worksheet?

Code:
Private Sub Worksheet_Change(ByVal Target As Range) 
Dim rng As Range 
Const limit = 100 
Set rng = Range("C10") 
If Target.Count > 1 Then Exit Sub 
If Intersect(Target, rng) Is Nothing Then Exit Sub 
If Range("C10") <= 100 Then Exit Sub 
With Application 
.EnableEvents = False 
MsgBox "100 is the Maximum bundle size" 
If Target > limit Then .Undo 
.EnableEvents = True 
End With 
End Sub
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
how would I go about limiting 2 different cells to 2 different values on the same worksheet?

Add your second range like this:

Code:
Set rng = Range("C10", "B10")

Then you can just add another IF statement for your second test.

Smitty
 

Forum statistics

Threads
1,140,924
Messages
5,703,182
Members
421,280
Latest member
Jaycee01

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
Top