VBA FORM add MISSING to empty cell

[dave_w1967]

Hello all,

I would greatly appreciate any help on this problem, as follows; I have created a VBA form with a lot of entries and the form works great, the user enters data into the cells and after pressing enter the data loads into the the spreadsheet, after entering the next set of data the next row is selected and the data is entered into the row, so far all good,

so what I would like to do now is if a particular cell of the form lets say orientations has not been entered (as it might not have happened as yet) and has been left blank for future use, then a formula to input into the blank cell "MISSING" so that it does not get forgotten about.

I had a formula entered into a cell as follows; =IF(ISNUMBER(SEARCH("*",$A$6)),$BQ$6,"") it looks to see if there is any text in A6 if there is no text then it looks to another cell that has "MISSING" which it then enters (this formula was copied down the column by hand) all seemed to work fine until another entry is done by the 'form' to which it left the cells blank and either overwrote or deleted the formula,

I have even tried using conditional formatting and cant get the formula to work, I have tried to up load my spreadsheet but for some reason it wont upload? probably to big or im doing something wrong, so I have just entered a very short sample and also the VBA code below, so either a formula taht I can use in the VBA FORM or a formula that I can use on the spreadsheet itself that will not be written over unless its the actual date that is being entered into..............again thanks in advance for any help or advice, it is very much appreciated.


=======================================================================================================================

A B C D E F G
1 EMPLOYEE NAME | EMPLOYEE ID | AREA | JOB TITLE | GLOBAL ORIENTAION | WHIMS | ORIENTATIONS |
2 --------------------------------------------------------------------------------------------------------------------------------------
3 JOHN DOE 123456 LEVEL 1 CLEANER 2022-01-23 2022-02-02 2022-03-02
4 JOE BLOGGS 234567 LEVEL 2 ADMIN MISSING MISSING MISSING
5 PAUL MAUL 345678 LEVEL 3 TRAINER 2022-03-04 2022-03-06 2022-05-27
6 DAVE RAVE 456789 LEVEL 4 SUPERVISOR ETC
7 STEVE REEVE 567890 LEVEL 5 HELPER

VBA CODE

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[BadlySpelledBuoy]

I think it would be easier to help with a solution if we could see it in context. Could you upload the file? Use dummy data rather than any real life sensitive data.

BSB

[dave_w1967]

[dave_w1967]

hopefully my file has been uploaded?

[BadlySpelledBuoy]

One way to deal with this would be replacing lines that stamp data to the sheet, like this:

Please Login or Register  to view this content.

With something like this:

Please Login or Register  to view this content.

I've used TRIM so that if the user just puts a space character in the field it will still stamp missing.

Note, you don't often need to use .Value when stamping data to the sheet. In fact, I only ever use it if I'm stamping numeric values like numbers, dates, times.

BSB

[dave_w1967]

sorry for the mix up of uploading the example spreadsheet, I guess the site was doing maintenance at the time. Sir your code works perfectly thank you for taking the time to create the code and reply, you are a gentleman and a scholar. thanks again your help is very much appreciated.

[BadlySpelledBuoy]

Very glad I could help.

Good luck with the rest of the project and let us know if you need more help.

BSB