Macro vs Data Validation vs Ifs statement

[VARD]

Hi,
I have the following task and I am looking for the optimized solution. Please help.

Problem statement.
User inputs:
B1: wage
B2: income tax percent
B3: social security percent
B4: Age

B5: =B1*(B2+B3)

I need a solution to meet the following:

If Age<30 then B5 <=10000
If Age>=30 then B5<=12000
Otherwise, reset the inputs in B2 and B3 and show a message that explains the limitations of the entry. (B1 isn’t arbitrary, B2 and B3 are)

What will be the easiest and smartest way to do this, having in mind that B5 is a formula that uses percentage inputs from B2, B3, and B1?

I can do this with if statements in the formula bar but first, the message is too long, second, can’t reset B2 & B3 to empty.

Thank you!

 

[Joe4]

Welcome to the Board!

What do you mean by this?

If Age<30 then B5 <=10000
If Age>=30 then B5<=12000

Do you mean capping the Wages (B1) by these dollar amount?
Or are you trying to cap the final calculation bu those amounts?

Maybe walk us through an actual example.

 

[Joe4]

If you are looking to cap the final calculation by those amounts, try this formula:

=MIN(B1*(B2+B3),IF(B4<30,100000,120000))

 

[VARD]

Joe4, thank you for reacting.
I guess I didn't specify my problem clearly.

I need to show a warning message to a user when the percentages he/she input result in total of higher than certain amount allowed. As a numeric example:
B1: 500,000 input
B2: 10% input
B3: 10% input
B5: total= 500,000*(0.1+0.1)=100,000 formula
Now, 100,000 exceeds the amount allowed by the law, so I need a message box telling the user that he needs to lower the percentages, and I need to reset those percentages in B2 and B3 to 0.

 

[VARD]

I have the following code, however it only runs once. I need it to appear again and again until the user changes the percentages, so that B18 is not larger than 10000 (or12000).

Sub contr_limit()
If Range("Age_1") <= 30 And Range("B18").Value >= 10000 Then
MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", vbRetryCancel + vbCritical, "WARNING"
ElseIf Range("Age_1") >= 31 And Range("B18").Value >= 12000 Then
MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", vbRetryCancel + vbCritical, "WARNING"
End If
End Sub

 

[Joe4]

I have the following code, however it only runs once. I need it to appear again and again until the user changes the percentages, so that B18 is not larger than 10000 (or12000).

Sub contr_limit()
If Range("Age_1") <= 30 And Range("B18").Value >= 10000 Then
MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", vbRetryCancel + vbCritical, "WARNING"
ElseIf Range("Age_1") >= 31 And Range("B18").Value >= 12000 Then
MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", vbRetryCancel + vbCritical, "WARNING"
End If
End Sub

OK, you are changing your ranges all around on us. Your original example had ranges B1:B5, but now you are saying B18.
I am going off of your original layout, and you can adjust the code as needed.

You can do what you want with a Worksheet_Change event procedure. This is VBA code that is automatically triggered to run when certain watched cells are manually updated.
This must be placed in the appropriate sheet module in order to run automatically.
The easiest way to ensure that is to right-click on the sheet tab name at the bottom of the screen, right-click, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim pctRng As Range
    
'   Enter range to watch for new entries in
    Set pctRng = Range("B1:B4")
    
'   Exit if updated cell not in watched range
    If Intersect(Target, pctRng) Is Nothing Then Exit Sub
    
'   Check for each condition
    If Range("B4") >= 30 Then
        If Range("B5") > 12000 Then
            MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", _
                vbRetryCancel + vbCritical, "WARNING"
        End If
    Else
        If Range("B5") > 10000 Then
            MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", _
                vbRetryCancel + vbCritical, "WARNING"
        End If
    End If
    
End Sub

This check will run automatically any time a cell in the range B1:B4 is manually updated.

 

[VARD]

OK, you are changing your ranges all around on us. Your original example had ranges B1:B5, but now you are saying B18.
I am going off of your original layout, and you can adjust the code as needed.

You can do what you want with a Worksheet_Change event procedure. This is VBA code that is automatically triggered to run when certain watched cells are manually updated.
This must be placed in the appropriate sheet module in order to run automatically.
The easiest way to ensure that is to right-click on the sheet tab name at the bottom of the screen, right-click, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim pctRng As Range
   
'   Enter range to watch for new entries in
    Set pctRng = Range("B1:B4")
   
'   Exit if updated cell not in watched range
    If Intersect(Target, pctRng) Is Nothing Then Exit Sub
   
'   Check for each condition
    If Range("B4") >= 30 Then
        If Range("B5") > 12000 Then
            MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", _
                vbRetryCancel + vbCritical, "WARNING"
        End If
    Else
        If Range("B5") > 10000 Then
            MsgBox "You have entered a higher amount than what is permissible for total contributions. Please enter smaller percentages.", _
                vbRetryCancel + vbCritical, "WARNING"
        End If
    End If
   
End Sub

This check will run automatically any time a cell in the range B1:B4 is manually updated.

By bad, should have been B5. The code worked. Thank you very much!

 

[Joe4]

You are welcome.
Glad I was able to help!