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

 

[Yongle]

I have a solution but am having difficulties uploading images to your thread
I wll get back to you when forum admin revert to me

 

[smozgur]

@Yongle - I got the images from your test thread. It looks like I was able to post them here.
I used Safari on Mac, tested on Chrome, Edge on Windows. Could you please let me know your browser type?

 

[Yongle]

@Yongle - I got the images from your test thread. It looks like I was able to post them here.
I used Safari on Mac, tested on Chrome, Edge on Windows. Could you please let me know your browser type?

View attachment 32704

View attachment 32705

The browser I use is Firefox

 

[Yongle]

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.

View attachment 32602
View attachment 32600
View attachment 32601

Any help would be much appreciated.

Thanks

Have a look at the 2nd image in post#3
Is that what you want?

 

[Yongle]

@tedholly
We seem to be back in business. The image that refused to upload 2 days ago is uploading today. But nothing has changed, other than a laptop reboot!

Is the above message box what you are looking for?
The above uses the standard VBA message box which is rather inflexible but is simple

Other data
Can you explain further what really want when you say that you also want "Summary of Previous data entered in this row: This would total each days"
What I need to understand is how complex your requirement is.
I do not really care which values you require (if the values are in the sheet, VBA can be used to summarise and extract them)
What matters is how much that may vary for each column
Does the list of required values grow as the user moves across the sheet?

Alternative (for information only at this stage)
With a standard VBA message box ....
- VBA builds everything "on the hoof" as it runs which can make the code quite long
A userform could be used instead ....
- the structure would then be ready for VBA to open the form at runtime and grab the relevant values
- the code would be shorter
Both can be made to work, but we may find ourselves leaning towards a userform if there are lots more things to add to the alert or if there are other complexities

 

[tedholly]

@Yongle

Yes this is exactly what I am looking for.

For the summary of previous data:

The worksheet currently is a table with these columns: This represents One day. There are 14 versions of each of these columns per day of the week. Example: 1 man Crew Sun. 1 Man Crew Tue. through Saturday then the second week looks like this: 1 Man Crew Sun.2 and so on. (Since this is in table format and the names of columns can not be identical)

Ideally if Matt the Project manager enters project number 12345 and 5 One Man crews on Sun and 2 two man crews on tue.2 then there would be a "totals" that would say:

Project 12345
Summary:
1 Man crew - 5
2 Man Crew 2

The reason for this idea of the summary is because one of the over time qualifiers is if their is a designated crew that worked on the project all week then the PM should approve the OT charges.

Ex: If John's crew worked on Monday, Tuesday and Wednesday on the same project number then we should charge OT for Thursday and Friday. But this is a case by case basis. So the idea would be if the Project Manager Matt entered the data into the OT column, he would see a summary of what hours were charged to that project already so that he can make the judgement to approve or deny the OT charges.

Project Number

1 Man Crew

1 Man crew Hourly OT Rate

1 Man crew Premium Day Rate

1 Man Crew Standby

1 Man Crew Mob/Demob

2 Man Crew

2 Man crew Hourly OT Rate

2 Man Crew Premium Day Rate

2 Man Crew Standby

2 Man Crew Mob/Demob

3 Man Crew

3 Man crew Hourly OT Rate

3 Man crew Premuim Rate

3 Man Crew Standby

3 Man Crew Mob/Demob

If this is not possible, then the image that you sent would be sufficient.

Does the list of required values grow as the user moves across the sheet? The way the spreadsheet is setup is the PM will enter a unique number into a column. Sometimes he does not enter any number in a column because it is 0. For Example:

Project 12345.
1 Man Crew. Sun. - 5
1 Man Crew Hourly OT Rate Mon. - 2
2 Man crew Standby Mon. 2- 5
1 Man Crew. Mon. - 10
2 Man Crew Mon. - 20

At the end of the spreadsheet there is a "Totals" table that gives a summary of the entire weeks totals. If we were able to add a "Summary" to the alert, then it could pull from this information.

Totals

Project Number

1 Man Crew

1 Man crew Hourly OT Rate

1 Man crew Premium Day Rate

1 Man Crew Standby

1 Man Crew Mob/Demob

2 Man Crew

2 Man crew Hourly OT Rate

2 Man Crew Premium Day Rate

2 Man Crew Standby

2 Man Crew Mob/Demob

3 Man Crew

3 Man crew Hourly OT Rate

3 Man crew Premium Rate

3 Man Crew Standby

3 Man Crew Mob/Demob

12345​

10

2

1

20

1

23456​

15

1

15

1

34567​

3

5

2

4

Ideally the user form would look identical to your image that was sent, but adding the following:

(If OT was entered on project 12345 and using the above totals table as an example)

1 Man Crew - 10
1 Man Crew Hourly OT Rate - 2
1 Man Crew Mob/Demob - 1
2 Man Crew - 20
2 Man crew premium day rate - 1

I appreciate your help

 

[Yongle]

A userform would give you the better solution
- let's try to make a good job of the first draft of the userform to avoid re-organising it later

Q1 Which column contains 1 Man Crew in the Totals Table?
Q2 Is the Totals Table in the same rows as the data?
Q3 Are headers in row 1?
Q4 Approx userform layout - post alternative if you do not like the image below

Note
I will give you detailed help ... but you will be building the userform
- that way you will understand how all the bits of the puzzle join together

 

[tedholly]

Q1: Please see attached image of the entire totals table along with the respective columns.

Q2: Yes it is in the same rows as the data. If the totals table needs to be moved to a different worksheet, then I can change accordingly.
Q3: Yes. Row 1 Has headers (EX: The days associated with each of the columns)

Q4: Can we add project number under the project name?

Note: Can this user form also apply for premium day rates? (with the same conditions) They are also considered Over Time. The difference is one is an hourly rate and one is a day rate, though both are considered Over Time.

Your note: Splendid, that is what I was hoping for. Thank you.

 

[Yongle]

Q2: Yes it is in the same rows as the data. If the totals table needs to be moved to a different worksheet, then I can change accordingly.

No need to move anything - I just needed to know where to tell VBA to find the data

Q4: Can we add project number under the project name?

Yes

Can this user form also apply for premium day rates?

Yes

Step 1
Tomorrow, I will post draft of the userform and you can tell me what to amend
Step 2
I'll help you create the same userform
Step 3
I'll provide code to make it pull the correct values from the worksheet etc with explanatory detail

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