Force a default value in certain cells if the user trys to leave them blank

[D.Lovell]

Hi,

I am trying to force a default value of "Not Selected" into cells that have dropdown lists, because rather annoyingly, it is still possible to accidently delete the contents of the cell on the keyboard and press enter, leaving the cell blank.

If anyone knows a better approach than what I am taking then please do let me know. Otherwise then please help me build on this code

I have done this to force a certain cell to a default value if you try to leave it blank...

Please Login or Register  to view this content.

I want to make it so that it will apply to multiple cells on a worksheet but not ones that are joined together in a range, instead certain cells scattered about across the worksheet. Also how to do this in a way where you can just add or remove individual cells in an array or something in a line of code to alter what cells are included with just a minor adjustment to the code?


Many thanks in advance for any assistance

Regards,

[xladept]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Your code only works on $A$7 ??

[AlphaFrog]

Change the drop down list cells to suit.

Please Login or Register  to view this content.

[D.Lovell]

Yes exactly, the code only works on A7, I could repeat it over and over for other cells of course but that seams very inefficient.

I have attached an example, there are dropdown lists and for the one in A7, if you try selecting it and then delete with the keyboard and press enter, it will revert back to say "Not Selected". I would like the apply the same to others scattered about across the worksheet.

Is the vba the only way to do this and if so, is there a way to do the code effectively so that cells can just be added or removed from a list in the code to include them?

[D.Lovell]

Also what if you wanted to force the cell value to remain the last item from the list that was selected if you tried to delete it and press enter with it blank, is there a way to do that?

[AlphaFrog]

See post #3

[D.Lovell]

Hi AlphaFrog,

Yes that is very helpful thank you, exactly what I meant

Are you also able to help with my question in post #5 ? Its kind of an extension to my original question.

Want to try and make the value in the dropdown box remain the same as what it was if you try to delete it and press enter

[AlphaFrog]

Want to try and make the value in the dropdown box remain the same as what it was if you try to delete it and press enter

Please Login or Register  to view this content.

[D.Lovell]

Yes that works exactly as I meant but screen flickers very noticeably unfortunately... Probably the undo I guess, tried turning off screen updating but it didn't make any difference. Anyway got what I need for now, your posts were very helpful thank you