What's wrong with this?

[litestream]

When I use the following code, it affects other cells also. How can I stop this?

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

 

[Smitty]

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]

Thanks!

 

[litestream]

You're right

How do I test if C10 <> 100?

 

[litestream]

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

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]

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?

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]

how would I go about limiting 2 different cells to 2 different values on the same worksheet?

Add your second range like this:

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

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

Smitty

 

[litestream]

Thanks, I'll try that in the morning