Message Box VBA: Multiple Criteria

tedholly

New Member
Joined
Feb 19, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hello -

I am trying to figure out how to create a message box that appears based on multiple criteria. I have tried doing research and still can not figure it out. I know minimal VBA, though I am confident it is possible.

I currently have a workbook that my project manager enters his crews time into and we generate invoices to our client based off of the inputted data. We have a few Over Time Qualifiers that we want to ensure get billed correctly to. When my project manager enters time into a cell that contains premium rates or OT rates, and it is a project number that is not associated with our clients PM jane or joe, then I want a pop-up message to come up for the project manager to confirm what was entered. We have a few OT qualifiers that must be met in order for us to charge OT to our client. If time is entered on a project that is our clients project manager (jane or joe) then we always charge OT. But if the project is not associated with jane or joe then we want to confirm if we should be charging OT rates.

1. If data is entered into certain cells (Cells that contain OT hours, so the hourly OT Rate columns or the premium day rate columns)
EX: Cells in column J, K, N,O,R,S,W,X,AB,AC,AG,AH,AL,AM.AQ.AR.AV.AW.BA.BB.BF.BG,BK,BL,BP,BQ,BU,BV,BZ,CA,CE,CF,CJ,CK,CO,CP,CS,CT,CW,CX,DA,DB,DE,DF,DI,DJ,DM,DN,DR,DS,DW,DX,EB,EC,EG,EH,EL,EM,EQ,ER,EV,EW,FA,FB,FF,FG,FK,FL,FP,FQ,FU,FV,FZ,GA,GE,GF,GJ,GK,GN,GO,GR,GS,GV,GW

2. AND if the Client PM is NOT Jane or Joe.
3. THEN bring a popup message box that forces the Project Manager to Confirm or Deny the inputted data.
4. If the PM approves it, then the value entered stays and if the PM denies it, then the value entered is cleared.

Ideally the popup message would provide the following details:
  • [ALERT: CONFIRM OT QUALIFIER]
  • Project Number
  • Client PM Name
  • Amount that was entered into the cell.
  • Summary of Previous data entered in this row: This would total each days. (This is not a necessity but would be beneficial to have. EX:
    • 2 - One man crew standby
    • 1 - two man crew premium day rate

The worksheet I have created is a 14 day cycle, Sunday - Saturday. Weekends look like the Sunday image and weekdays look like the Monday image. There is a VLOOKUP that runs when a project number is entered into the Project Number column that pulls the name, AFE, client PM name, and client pm email.

1613768718387.png

1613768660740.png

1613768695358.png


Any help would be much appreciated.

Thanks
 

tedholly

New Member
Joined
Feb 19, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Okay those steps sounds good to me.

Q Is it only columns containing either OT Rate or Premium Day Rate that will trigger the Alert Message? Yes that is correct.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
TEST only on a COPY of your workbook!

Please confirm that the Alert is only triggered by changes to desired cells
Install the code and then test by amending values in random rows and columns to see what happens
A message should pop up when you amend values in the "correct" columns

Place this code in the SHEET module
(right click on sheet tab \ view code \ paste code into open window)
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("J3:GY3").Resize(Rows.Count - 2)) Is Nothing Then Exit Sub
    Dim hdr As String: hdr = Cells(2, Target.Column).Value
    If hdr Like "*OT Rate*" Or hdr Like "*Premium Day Rate*" Then MsgBox "Approve or Reject", vbExclamation, "ALERT"
End Sub
 

tedholly

New Member
Joined
Feb 19, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
TEST only on a COPY of your workbook!

Please confirm that the Alert is only triggered by changes to desired cells
Install the code and then test by amending values in random rows and columns to see what happens
A message should pop up when you amend values in the "correct" columns

Place this code in the SHEET module
(right click on sheet tab \ view code \ paste code into open window)
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("J3:GY3").Resize(Rows.Count - 2)) Is Nothing Then Exit Sub
    Dim hdr As String: hdr = Cells(2, Target.Column).Value
    If hdr Like "*OT Rate*" Or hdr Like "*Premium Day Rate*" Then MsgBox "Approve or Reject", vbExclamation, "ALERT"
End Sub
I did as instructed but nothing happened
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I have no idea why closing and reopening the workbook was necessary
- unless EnableEvents was previously set to FALSE

Thanks for testing that, I will not be posting anything further until tomorrow.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
USERFORM Draft 1
To keep everything simple ...
- Approve & Reject are COMMAND BUTTONS
- everything else is a TEXTBOX

Is the form complete?
Ignore formatting issues and functionality for the present ...
- is all the required information on the userform?
- is everything in the correct position and sequence?
- is anything missing? eg should the date be included for item being approved?

Have you created userforms before or is it a new adventure?

ProjectMessage03.jpg
 

tedholly

New Member
Joined
Feb 19, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Is the form complete?
Ignore formatting issues and functionality for the present ...
- is all the required information on the user form? Yes
- is everything in the correct position and sequence? Yes
- is anything missing? eg should the date be included for item being approved? If we could add the date above the "1 Man Crew Hourly OT Rate - 15"

I have not created a user form before, so this will be a new adventure.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
I am occupied elsewhere today. Will update thread tomorrow.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,016
Messages
5,628,150
Members
416,296
Latest member
smartua

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