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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
VBA Code:
        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
VBA Code:
.ListColumns(5) =


This is part of a larger section of code
VBA 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?
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,520
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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 ?
VBA Code:
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,520
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I removed this one
VBA Code:
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
VBA Code:
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

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
VBA 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

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Going on your previous advice, I removed one of the instances of
VBA Code:
 If Selection.Count = 1 Then
and it seems to still work. Do I only need it once in this section of code?

VBA 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

Well-known Member
Joined
Oct 27, 2005
Messages
20,520
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
As long as the End If for that If statement encompasses the enire block of code
AND
maybe this way for the supervised hours
VBA Code:
            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

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
An old saying comes to mind,

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,520
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
(y) :biggrin:(y)
But you can build a better mousetrap !!!
 

Forum statistics

Threads
1,136,275
Messages
5,674,786
Members
419,524
Latest member
helensesc

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