• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Prevent cells being left blank!

S

Slimline

Member
Hello,

A1='Ref#'
B1 = Name
C1 = Age
D1= Comments
E1=DOB


I am looking for a formula or code to prevent cells from being left blank.

1) If A1 is blank than nothing can be written in B,C,D,E.
2) When something is entered in A1, I'd like only B1 to become mandatory. E.g:

If B1 is left blank & user moves to C1, or E1 , starts to write something, only then a message box appears informing user to fill B1. When user clicks 'OK' on msg box, B1 is automatically selected. So user cannot write anything in C & E (EXCEPT for D) until B1 is completed.

- I would like all cells in column D to be free of any restrictions.
- Also it would be great if I can customise my warning message.
- Also a lot of people will be using the spreadsheet at the same time, therefore if something simple can be done by using a formula please let me know, so it doesn't crash down.

I've upload my file.
Please help!!


Many Thanks,

Serena
 

Attachments

  • Database.xlsx
    8.8 KB · Views: 18
Hi Serena ,

There is some inconsistency in the way your problem has been written down ; as far as I can see :

1. You need data validation in columns B , C and E ; column D is excluded from any data validation rule.

2. Data can be entered in column B only if there is data in column A.

3. Data can be entered in columns C and E only if there is data in columns A and B.

If it is only this much , then data validation based on formulae is possible.

If you want that along with any error message the cursor has to be moved to the appropriate cell , then some amount of VBA coding will be required.

Narayan
 
Hello Narayan,

Apologies about the inconsistency but your understanding is correct.

Is it possible to make two sheets: one with only the data validation (no error message etc) & the other with the vba coding (with error message) as I am very interested to learn how both are done.

Many Thanks,

Serena
 
Hi Serena,

Just in case you need to show the error messages and without any hardcore protection then you can use the below:

Cell B2 :- "=IF($A2<>"","","Enter Data in Col.A")"
Cell C2 & E2 :- "=IF($A2="","Enter Data in Col.A",IF(AND($A2<>"",$B2=""),"Enter Data in Col.B",""))"

to make to more User Friendly, you can add Conditional Formatting to it..
And in case you need a concrete & full proof solution, so I am sure Narayan can help you on this...
Meanwhile, I will also try to get something if I can...
 
Hi Abhi,

Thanks for the above, I tried it out without conditional formatting, but it can be easily overwritten. However I can definitely use it for my other work as a quick reminder to people.

Also how can I apply it along with conditional formatting? Did you mean I can add this formula under 'add new rule' in conditional formatting?

Cheers!
Serena
 
Hi Serena ,

Can you check the file now ?

Sheet1 has the DV , which is formula based ; Sheet2 has the VBA based data checking and prevention.

In case you want anything more , please let me know.

Narayan
 

Attachments

  • Database.xlsm
    16.5 KB · Views: 30
Hi Serena,

You can use the words "Enter Data in Col.A" & "Enter Data in Col.B" to capture the formatting and highlight it with bright red background or something to that it's easily understood that this has errors..

For this you need to do the below:
1. Select you range of data from Col.A to Col.E
2. Goto Conditional Formatting
3. In the Formula section enter the below formula:
"=IF(OR(A1="Enter Data in Col.A",A1="Enter Data in Col.B"),1,0)"
4. Apply some bright color and hit OK..

This should work for conditional formatting..(go can change the values to whatever error message you have displayed earlier)

Hope this helps...:)
 
Hi Narayan,

That's great! :)

One more thing is that after the message appears and you click ‘ok’, the cell is cleared- which is fine. However when I added a drop down list in column B and ran the macro – the drop down list in the cell also got deleted.

Is there a way to bypass this in the code?

So if I try to enter in B1, after the message I only want the selection/text in the B1 to be cleared & not the drop down list.
I have uploaded a new file.

Many Thanks

Serena
 

Attachments

  • Database 2.xlsm
    19 KB · Views: 17
You must log in or register to reply here.
Back
Top