So here is what I am trying to do....
I have a form/timesheet that needs to filed out by employees that are working on projects for customers. The form is often filled out incorrectly or items are omitted that are necessary for our purposes. So I end up having to go back to the employee and ask questions. To reduce the number of errors and omissions I have done the following...
Column E - Employee must enter an activity from a list provided. I created a dropdown menu using data validation to prevent users of the form from entering an activity that is not an option on the list we provided to them.
Column D - Next, if the employee enters one of the activities that is a direct customer related activity (these activities must be associated with a customer, if it is not associated with a customer, then they are using the wrong activity) they must enter the customer name in column D. Otherwise, they can leave this cell blank.
I added conditional formatting to cells in Column D. Basically, if the activity entered into the adjacent cell under column E is a direct customer related activity, the cell will turn red if it is left blank.
And here is what I would like to do, but cannot figure out how...
If there is a red cell in column D, I want any one of these to happen...
1) A message box will pop up prompting the user to enter a customer name as soon as a cell under column D turns red.
2) A message box will pop up prompting the user to enter a customer name and preventing them from saving the file when they try to save the file, if there are any red cells under column D.
3) Both 1 and 2 ... so they message box will pop up prompting the user to enter a customer name as soon as a cell under column D turns red. They can remedy the issue or choose they can ignore it until they try to save at which point #2 would occur
4) 1) A message box will pop up prompting the user to enter a customer name as soon as a cell under column D turns red and they will be unable to move on to another field until a customer name is entered.
I have tried doing this using VBA, but I'm new to VBA and despite researching the internet and learning how to use if then else statements and msgbox I have been unsuccessful. Can someone please give me some suggestions or at least point me in the right direction.
Thank you
I have a form/timesheet that needs to filed out by employees that are working on projects for customers. The form is often filled out incorrectly or items are omitted that are necessary for our purposes. So I end up having to go back to the employee and ask questions. To reduce the number of errors and omissions I have done the following...
Column E - Employee must enter an activity from a list provided. I created a dropdown menu using data validation to prevent users of the form from entering an activity that is not an option on the list we provided to them.
Column D - Next, if the employee enters one of the activities that is a direct customer related activity (these activities must be associated with a customer, if it is not associated with a customer, then they are using the wrong activity) they must enter the customer name in column D. Otherwise, they can leave this cell blank.
I added conditional formatting to cells in Column D. Basically, if the activity entered into the adjacent cell under column E is a direct customer related activity, the cell will turn red if it is left blank.
And here is what I would like to do, but cannot figure out how...
If there is a red cell in column D, I want any one of these to happen...
1) A message box will pop up prompting the user to enter a customer name as soon as a cell under column D turns red.
2) A message box will pop up prompting the user to enter a customer name and preventing them from saving the file when they try to save the file, if there are any red cells under column D.
3) Both 1 and 2 ... so they message box will pop up prompting the user to enter a customer name as soon as a cell under column D turns red. They can remedy the issue or choose they can ignore it until they try to save at which point #2 would occur
4) 1) A message box will pop up prompting the user to enter a customer name as soon as a cell under column D turns red and they will be unable to move on to another field until a customer name is entered.
I have tried doing this using VBA, but I'm new to VBA and despite researching the internet and learning how to use if then else statements and msgbox I have been unsuccessful. Can someone please give me some suggestions or at least point me in the right direction.
Thank you
