Making a Cell Mandatory in a workbook with multiple sheets

Hello,

I am currently trying to make one cell mandatory in a specific sheet. The cell does have a Vlookup formula that is based on another cell with a dropdown. These choices from the dropdown cause a name to automatically pop up on the cell im trying to make mandatory. However this cell allows that name to be erased and another name to be put in. This functionality needs to stay as the automatic name is just the most common name for this purpose but others may be used. This means that people are able to delete the name or not even pick a choice from the dropdown and save it which causes hassle. Am I able to make this cell mandatory at all? Thanks.

It's hard to say without seeing things in context in a sample workbook, but maybe you can set up conditional formatting on other cells which turns the font white if the drop-down cell is not equal to your chosen name, so that the sheet appears blank.

Hope this helps.

Pete

I think this requires VBA. If you put up your worksheet (or a representative worksheet) so I know where everything is, I'll have a look at it for you (at the very least).
You'll probably get a few responses before I even get to see it.

I've attached pictures of the sheet name the cell is in,

the lookup formula on the cell and a picture showing the location dropdown next to endorsed by.

Endorsed by is the cell that I don't want to be left blank. As I said previously it changes based on location (left blank for confidentiality in picture) automatically but that text can just be deleted and then the worksheet can be saved.

The endorsed by cell is L8

Thanks

Yeah I know...'I should've gone to Specsavers' but I can't see that.

Like I said, if you put up a worksheet, I'll have a look at it.

Hello,

I have now attached the worksheet after taking off sensitive data. So as you can see the cell under "location" is a drop down which affects what pops up in the cell under "endorsed by".

And if you just delete the text in the cell under endorsed by you can just save it and this creates hassle and follow ups

Hopefully you can see the attached worksheet.

If you (or anyone else) could help that would be amazing! thanks for guiding me through this so far.

Hello Croweater,

This is a great code, thank you very much! I was just wondering if instead of endorsed by not being able to be changed at all. If it can be changed but just not left blank, this is because the autopopulate on that is the most likely name that should be there however there are times where a different name should be put there.

For example say if john smith was the most common endorser of house 1 his name would show up if house 1 was to be selected in located. However they may be away or there could be a change of responsibilities so automatically john smith would pop up but if that could also be allowed to change that would be great. Obviously this keeps with the theme that while it can be changed it cant be left blank.

I'm thinking that maybe the auto populate for endorsed by might need to be taken away to make this possible? I don't believe that would be a problem if I must do that, just as long as you need to put something in the endorsed by.

Thanks for your (and others) help so far.

Hey Croweater,

Thanks for all your help so far this is great. I decided to get rid of the autopopulate for endorsed by as it makes it easier anyway.

I have found that the code works and i save it as an xlsm file. I quit out of it through task manager as I want it to be blank to start with when distributed.

When opening it again it has the code still in the visual basic but I am able to quit out of it while endorsed by is blank.

Do you know if I am saving it wrong or something like that?

Thanks.

OK. I did exactly the same thing as you did, now I have an empty worksheet and it will NOT let me quit while endorsed by is blank.

Did you put the code in the correct class modules (One in Standard Request and one in ThisWorkbook)?
As you have got rid of the autopopulate, did you take this line out?

Please Login or Register  to view this content.

Otherwise, post up the worksheet that is not working and I'll see what you have done.

Hey Croweater,

Here is the worksheet, I have taken that line out now as well but the problem hasn't changed for me.

Appreciate your continued help!

Are you opening it in 'protected view'? If you enable editing on that file, it should work. It does for me.

Oh yes it seems to be working now thanks very much!

So they won't be able to save this at all before entering something in the endorsed by right?

Seems great!

Thanks very much!

Yep I was able to make it stop saving as well with a tiny change.

Learned something myself

Thanks again!!!

Actually might need one more thing if possible aha.

So i just copied the previous code for not allowing closing before endorsed by is filled and replaced first bit to before save.

I have now realised it doesn't let me save the code unless endorsed by is filled however I want that to be blank when it is opened.

Is it possible at all to save this code while it is contradicting its own rule? sorry aha

Thanks

It seems as soon as i try to put in the code for before saving, it will not let me save so I dont know if im able to do that.

I've attached the worksheet with the before close codes in and this is the code i made for before save:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheets("STANDARD REQUEST").Range("L8").Value = "" Then
Cancel = True
MsgBox "Endorsed By name cannot be blank"
Else
ActiveWorkbook.Close SaveChanges:=True
End If
End Sub

So when i put that in below the before close code it wont let me save the code or sheet at all.

Thanks,