Code to add text to a cell that is left blank after a VBA form has been filled in.

Hi All,

I have a VBA form which is complete, except for 1 code that I cannot get to work, basically when a user completes the form it enters the information in to the data sheet, but if the user leaves a cell on the form blank (may come back to update the cell at a leter date) i need a code that would insert "MISSING" into the cell, I used the following code which works but it overwrites the users input if they do add text to the cell when completing the form, CODE as follows;

Worksheets("TRAINING DATABASE").Cells(lastrow + 1, 2).Value = "MISSING"

The next following code puts the users input into the cell;

Worksheets("TRAINING DATABASE").Cells(lastrow + 1, 2).Value = TextBox2.Text

I have tried using various ways of doing this, one way was using 'Then' and/or 'IF' statement but i keep getting an error using 'Then' and/or 'IF'! any body have any ideas on how to use this code so that if the user inputs text it doesnt overwrite the users input and if the user does not enter code then it will enter "MISSING"

I appreciate all help on this matter

(please note i have tried to upload a small spreadsheet for viewing etc but I keep getting a error code on the upload page, maybe due to the ongoing site update.

Is the form just adding records to the next blank row or are you also using it to update previously submitted rows? i.e. not necessarily the last row of data?

Ignore the attached file. I'm just attaching a blank file to test if I get an error when uploading.

BSB

Hi and thanks BadlyspeleedBuoy for the quick reply there is two command buttons one to add new data to the next row and another command button to update any row that has a id number. I have posted small sections of each VBA command the list is short but it does go to textbox66;

Please Login or Register  to view this content.

Again thanks for any help.

So, let me just get this straight in my mind, if a control on the form is left blank when submitting a new row of data it should put "missing".
If a record (row of data) is being updated it should overwrite if it says missing, but leave the value as is IF control was left blank but the corresponding cell on the worksheet already has a value other than "missing"??

It would really help if you could try again to attach your file. Looking at your code above we could really help tidy that up if we could see it in context.
For example, looping through the form controltls rather than having a line of code for each.

BSB

So hopefully this upload works, fingers crossed.

Hi BSB, I have condensed the worksheet as best as possible, and yes to your question, I have added the comments below to the spreadsheet so that you can see what im trying to achieve, again thanks for your time and help.

AFTER USER HAS

FINISHED FILLING IN THE FORM AND DATA HAS BEEN ENTERED INTO THIS SPREADSHEET IF ANY CELL IS BLANK THEN A CODE FROM THE VBA FORM WILL ENTER "MISSING" INTO THE BLANK CELLS,
UNTIL THE USER COMES BACK AT A LATER DATE AND THEN EITHER USES THE FORM TO UPDATE OR MANUALLY UPDATES THE "MISSING" CELLS WITH THE NEW INFORMATION .

ALTHOUGH THIS IS A CONDENSED VERSION ENTRIES WILL BE OVER THE 2000 PLUS ROW RANGE SO THE REASON FOR THE "MISSING" IS SO THAT THE BLANK CELLS WILL NOT BE FORGOTTEN ABOUT

OK, in the attached version of your file what I've done is add a number to the "Tag" property of all the Combo & Text boxes on your form that corresponds to the column number the data should be stamped to.
That will allow you to loop through the controls and use that tag value rather than have to have a line of code for each control.

This is the revised code for your "add new" button. It will stamp a new record to the end of the data and any blank controls will be stamped as "MISSING".

Please Login or Register  to view this content.

BSB

I've also revised the code for the "update" button. IT will find the row to update based on the customer ID entered.
If any controls are left blank it will stamp them as missing, unless there's already a value recorded in the data for that column.
If the column says missing it will be overwritten by the value entered on the form.

For some reason the forum won't let that part of the code paste and just gives me a blank screen when I try.

Give it a try and let me know if it does what you need.

BSB

Hmmm. Seems to let me add that bit of the code when I don't include my explanation with it.

Please Login or Register  to view this content.

BSB

My hat goes off to you sir ......again I thank you for your time and help, your code works perfectly, I tried adding reputation but its not letting me? will keep at it though, but thanks anyway you are very much appreciated.

Very happy I could help And hopefully the revised code will save you wearing your fingers out writing all that code using your original approach

BSB