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

Private Sub CommandButton1_Click()
Dim sht As Worksheet, sht1 As Worksheet, lastrow As Long
Set sht = ThisWorkbook.Sheets("TRAINING DATABASE")

With sht
.Range("A" & lastrow).Value = txtEMPLOYEENAME.Value
.Range("B" & lastrow).Value = txtEMPLOYEEID.Value
.Range("C" & lastrow).Value = txtAREA.Value
.Range("D" & lastrow).Value = txtjobtitle.Value
.Range("E" & lastrow).Value = txtSHQGLOBALORIENTATION.Value
.Range("F" & lastrow).Value = txtWHIMS.Value
.Range("G" & lastrow).Value = txtORIENTATIONS.Value

End With
sht.Activate
End Sub

Private Sub CommandButton2_Click()
With Me

.txtEMPLOYEENAME.Value = ""
.txtEMPLOYEEID.Value = ""
.txtAREA.Value = ""
.txtjobtitle.Value = ""
.txtSHQGLOBALORIENTATION.Value = ""
.txtWHIMS.Value = ""
.txtORIENTATIONS.Value = ""

End With
End Sub