If user filled specific cell then he must fill in another specific cell before closing

[Khaled040]

Hi all,
I have a little problem and hoping you can help me, I have a sheet on the share drive that all guys use as a template for sending work requests, this sheet contains many cells they should fill in before they send it to my department (maintenance) to fix the problem they have, two of the main cells are E5 for the plant number & B15 for description of the work required. currently we have a problem with many guys that they do not fill in the plant name cell before sending it so we got headache while trying to find out which plant is the correct one.
My request is, is there any formula or macro I can use to force them filling in the plant name cell IF the work description cell is already filled in ??
Sorry for bad English & too much writing

 

[ParamRay]

This must be placed in the code module for ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  
  With ThisWorkbook.Worksheets("Sheet1")  'change sheet name as needed
    If Not IsEmpty(.Range("B15")) Then
      If IsEmpty(.Range("E5")) Then
        MsgBox "Enter plant name first.", vbExclamation
        Cancel = True
      End If
    End If
  End With

End Sub

 

[Khaled040]

Sorry but did not work with my sheet, is there any way that I attach the sheet for you to save your time ?

 

[Khaled040]

Any help please

 

[jeffreybrown]

Maybe you could help with how did it not work?

Where did you place the code?

Did you rename the target sheet?

Are the targeted cells correct?

What did you expect to happen?

Remember, we can't see your sheet and no, there is not a way to attach a sheet to the forum.

The best you can do here is show a screen shot of your data layout.

Download

 

[Khaled040]

I do not know which site is preferred to upload pictures so I just used this one Pasteboard — Uploaded Image

1- I right clicked on the tab name and selected "view code" then pasted the code there
2- the yes I renamed the target sheet, I even tried the code with a new sheet with no formulas at all but still not working
3- yes I checked the targeted cells and they are correct
4- I expected to have the msg box telling me to enter the plant name before I close the sheet if cell B15 have data in it

I'm using excel 2013, also below a macro which already there in the "view code" area for the drop down list

Private Sub ComboBox1_GotFocus()
ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown
End Sub

Hope this helps, regards

 

[jeffreybrown]

1- I right clicked on the tab name and selected "view code" then pasted the code there

That is the wrong place for this code...Please refer back to post #2

This must be placed in the code module for ThisWorkbook:

Where do I paste the code that I find on the internet

 

[Khaled040]

Thanks a lot, I pasted it in the correct place but it did not work at first until I typed something on E5 then delete it again to get it working!!
Anyway it is working great now, thanks a lot gentlemen
Best Regards

 

[jeffreybrown]

You are very welcome. Glad you have a working solution and thanks for the feedback.

 

[Khaled040]

Thanks for your help. I think I will need it again, something is not right with my sheet but do not know what it is. The code really worked well and it does not allow you to close the sheet without filling the required cell but I found that the user can write anything in that cell E5 then delete it again then the sheet will not ask to fill it what do you think I should look at ??