Message Box VBA: Multiple Criteria

[tedholly]

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.

Any help would be much appreciated.

Thanks

 

[tedholly]

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.

 

[Yongle]

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)

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]

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)

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

 

[tedholly]

I did as instructed but nothing happened

@Yongle

I closed out of the workbook and re-opened it and now it works.

 

[Yongle]

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]

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?

 

[tedholly]

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]

I am occupied elsewhere today. Will update thread tomorrow.