Macro vs Data Validation vs Ifs statement

VARD

New Member
Joined
Oct 20, 2022
Messages
6
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,531
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,531
Office Version
  1. 365
Platform
  1. Windows
If you are looking to cap the final calculation by those amounts, try this formula:
Excel Formula:
=MIN(B1*(B2+B3),IF(B4<30,100000,120000))
 
Upvote 0

VARD

New Member
Joined
Oct 20, 2022
Messages
6
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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.
 
Upvote 0

VARD

New Member
Joined
Oct 20, 2022
Messages
6
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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
 
Upvote 0

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,531
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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.
 
Upvote 0
Solution

VARD

New Member
Joined
Oct 20, 2022
Messages
6
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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:
VBA Code:
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!
 
Upvote 0

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,531
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,186,506
Messages
5,958,247
Members
438,346
Latest member
Andrew1234

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