Message Box VBA: Multiple Criteria

tedholly

New Member
Joined
Feb 19, 2021
Messages
17
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

BatCoder
Joined
Feb 28, 2002
Messages
1,571
@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?

HideColumns.jpg


ProjectMessage01.jpg
 

Yongle

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

ADVERTISEMENT

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

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


@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

New Member
Joined
Feb 19, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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 Number1 Man Crew1 Man crew Hourly OT Rate1 Man crew Premium Day Rate1 Man Crew Standby1 Man Crew Mob/Demob2 Man Crew2 Man crew Hourly OT Rate2 Man Crew Premium Day Rate2 Man Crew Standby2 Man Crew Mob/Demob3 Man Crew3 Man crew Hourly OT Rate3 Man crew Premuim Rate3 Man Crew Standby3 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 Number1 Man Crew1 Man crew Hourly OT Rate1 Man crew Premium Day Rate1 Man Crew Standby1 Man Crew Mob/Demob2 Man Crew2 Man crew Hourly OT Rate2 Man Crew Premium Day Rate2 Man Crew Standby2 Man Crew Mob/Demob3 Man Crew3 Man crew Hourly OT Rate3 Man crew Premium Rate3 Man Crew Standby3 Man Crew Mob/Demob
12345​
1021201
23456​
151151
34567​
3524


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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
ProjectMessage02.jpg

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

New Member
Joined
Feb 19, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Q1: Please see attached image of the entire totals table along with the respective columns.
1614094859323.png

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)
1614095248990.png

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,144,622
Messages
5,725,338
Members
422,618
Latest member
Shull1

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