If certain criteria is met, I need a 3 entered in the column E for the row and a message box to appear.

[dpaton05]

I have an excel table and I am trying to make a value in column E for any row be 3 if these conditions are met

• If anything lower then 3 is entered in column E
• If the value in column B for that row is any of these values
  • Supervised Contact
  • Supervised Transport
  • Daytime Respite

I then want a message box to appear informing the user that the minimum charge for that service is 3 hours.


This is my code in the worksheet_change event for the worksheet that has the table I tried to write for the first item in the list but it wouldn't work.

        If Intersect(Target, Me.ListObjects("CSS_Quote").ListColumns(2).Range) = "Supervised Contact" Then
            If Me.ListObjects("CSS_Quote").ListColumns(5).Range < 3 Then
                MsgBox "The minimum hourly charge for a Supervised Contact is 3 hours"
                Me.ListObjects("CSS_Quote").ListColumns(5) = 3
            End If
        End If

I get an error Wrong number of arguments or invalid property assignment with the following line highlighted

.ListColumns(5) =

This is part of a larger section of code

    If Selection.Count = 1 Then
        If Intersect(Target, Me.ListObjects("CSS_Quote").ListColumns(6).Range) > 1 Then
            If Selection.Count = 1 Then
                cars = InputBox("Please enter how many cars are required.")
                    If cars > 1 Then
                    Cells(Target.Row, "L") = cars
            Else
                    Cells(Target.Row, "L") = 1
                End If
            End If
        Else
            If Intersect(Target, Me.ListObjects("CSS_Quote").ListColumns(6).Range) = 1 Then
                Cells(Target.Row, "L") = 1
            End If
        End If
        If Intersect(Target, Me.ListObjects("CSS_Quote").ListColumns(2).Range) = "Supervised Contact" Then
            If Me.ListObjects("CSS_Quote").ListColumns(5).Range < 3 Then
                MsgBox "The minimum hourly charge for a Supervised Contact is 3 hours"
                Me.ListObjects("CSS_Quote").ListColumns(5) = 3
            End If
        End If
       
    End If

Could someone show me what have I done wrong with my code and help me with the rest of the code please?

 

[Michael M]

If it always going to be 3 for anything supervised, can't you remove one of the If statements
AND Why would there be more than 1 selection.count ?...can't it be removed also ?

If Selection.Count = 1 Then
            If Cells(Target.Row, "B") = "Supervised Contact" Or Cells(Target.Row, "B") = "Supervised Transport" Or Cells(Target.Row, "B") = "Daytime Respite" Then
               MsgBox "The minimum hourly charge for a Supervised Contact is ALWAYS 3 hours"
               Cells(Target.Row, "E") = 3
            End If
    End If

 

[dpaton05]

As you know, I am still learning about vba and much of the code I have written is just copying code that is working and trying to modify it. I saw selection.count was working in part so I thought that needed an extra selection.count.

What do you mean about removing an If statement, how would I do that?

Also, how do I write it so the message box applies to the service selected in column B, not Supervised Contact every time the message box appears?

 

[Michael M]

I removed this one

If Cells(Target.Row, "E") < 3 Then

and of course the End If as well
So, I would have thought this would do the same thing....2 less IF statements

If Cells(Target.Row, "B") = "Supervised Contact" Or Cells(Target.Row, "B") = "Supervised Transport" Or Cells(Target.Row, "B") = "Daytime Respite" Then
               MsgBox "The minimum hourly charge for a Supervised Contact is ALWAYS 3 hours"
               Cells(Target.Row, "E") = 3
            End If

 

[dpaton05]

But the problem with that is that the service might be more than 3 hours, 3 is just the minimum.

I got it to work with this code

    If Selection.Count = 1 Then
        If Cells(Target.Row, "E") < 3 Then
            If Cells(Target.Row, "B") = "Supervised Contact" Or Cells(Target.Row, "B") = "Supervised Transport" Or Cells(Target.Row, "B") = "Daytime Respite" Then
               MsgBox "The minimum hourly charge for a " & Cells(Target.Row, "B") & " is 3 hours"
               Cells(Target.Row, "E") = 3
            End If
        End If
    End If

 

[dpaton05]

Going on your previous advice, I removed one of the instances of

 If Selection.Count = 1 Then

and it seems to still work. Do I only need it once in this section of code?

    If Selection.Count = 1 Then
        'This sets the minimum of 3 hours for the named services and lets the user know that is a minimum of 3
        If Cells(Target.Row, "E") < 3 Then
            If Cells(Target.Row, "B") = "Supervised Contact" Or Cells(Target.Row, "B") = "Supervised Transport" Or Cells(Target.Row, "B") = "Daytime Respite" Then
               MsgBox "The minimum hourly charge for a " & Cells(Target.Row, "B") & " is 3 hours"
               Cells(Target.Row, "E") = 3
            End If
        End If
    End If
    'If Selection.Count = 1 Then
        If Intersect(Target, Me.ListObjects("CSS_Quote").ListColumns(6).Range) > 1 Then
            If Selection.Count = 1 Then
                cars = InputBox("Please enter how many cars are required.")
                    If cars > 1 Then
                    Cells(Target.Row, "L") = cars
            Else
                    Cells(Target.Row, "L") = 1
                    End If
            End If
        Else
            If Intersect(Target, Me.ListObjects("CSS_Quote").ListColumns(6).Range) = 1 Then
                Cells(Target.Row, "L") = 1
            End If
        End If
    'End If

 

[Michael M]

As long as the End If for that If statement encompasses the enire block of code
AND
maybe this way for the supervised hours

            If Cells(Target.Row, "E") < 3 AND Cells(Target.Row, "B") = "Supervised Contact" Or Cells(Target.Row, "B") = "Supervised Transport" Or Cells(Target.Row, "B") = "Daytime Respite" Then
               MsgBox "The minimum hourly charge for a " & Cells(Target.Row, "B") & " is 3 hours"
               Cells(Target.Row, "E") = 3
            End If

 

[dpaton05]

I tried your code to combine the conditions but I got all these errors so I will just stick with what works.

 

[Michael M]

OK...no worries.

 

[dpaton05]

An old saying comes to mind,

If it ain't broke, don't fix it.

 

[Michael M]

But you can build a better mousetrap !!!