Force user to make entry into column T when leaving that record

Let's say that I am working making entries to any particular record.

If record already has an entry in col T, then bypass this requirement.

If record does not have an entry in col T . . . upon leaving that record,
Then, give MsgBox that says: "You must make an entry into col T

you could make use of the worksheet_change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Cells(Target.Row, 20) = "" Then
        Cells(Target.Row, 20) = InputBox("You must make an entry in the T column!!")
        
    End If
End Sub

Edit -- attached example worksheet with this function written into the worksheet_change event.

Hi Launchnet

With no VBA, you can use Validation for this.

In the range that you need to do this,Data>>Validation>>Custom>>-counta(T)

Take a look to the attachment.(N:R Columns, in my example)

Hope to helps you.

dang, that is a cool way to do it

Hi Fortis . . . Nice code but . . .

I use 175 columns. Don't worry about such a large size.

I believce that the code has to more or less say: Any time that a user enters part of the data but forgets to enter a value into col T by clicking on a different record or goes CTRL HOME, the msgbox should appeaar. All data is not necessarily available at all times, So if any data is entered into a record, but nothing entered into col T and the user tries to go CTRL HOME or clicks on another record, the messae box will appear.

I just now typed data into 2 different cells . . . col C & col D There is still more data to be entered later, but I want to force the user to enter data into cell T for this record. If they don't enter data into col T, the user can not save the record without data in col T.

So, I went CTRL HOME and I also tried clicking on different record. Both do their function, but no msg Box is displayed, which allows the new record to be saved with out data in col T. No data in col T causes many problems. There must be data in col T

Any clicking on a different record or even going CTRL HOME has to display the MsgBox if no entry has been made for this record in column T.

Column T has to have data period

what did the Worksheet_change function do for you? I thought using validation was clever and simpler, but if it's not working out for you how about the VBA approach?

What specific range of cells(columns/rows) do you need protected?

For example if you only care about rows between some lower/upper bound then you could alter the macro as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

If (target.row >LowerBoundNumber) AND (target.row<upperBoundNumber) Then
    'Check the T column for the record just altered to make sure it is not blank'
    If Cells(Target.Row, 20) = "" Then
        Cells(Target.Row, 20) = InputBox("You must make an entry in the T column!!")
        
    End If
ELSE
   'ignore worksheet change, the target range is outside of the range we care about'
END IF

End Sub

Hi Launchnet

Thank you for your good words..

I just now typed data into 2 different cells . . . col C & col D There is still more data to be entered later, but I want to force the user to enter data into cell T for this record. If they don't enter data into col T, the user can not save the record without data in col T. .........

Two things only i have to tell you:
1) The range that i "control" with my formula, is Columns(N:S--Yellow colour) Not C or D...

2) We can modify the formula, to "control" any range you like, But, we can not prevent the user to save it...

If you have to do also this, i think that VBA code is the solution.

GeneralDisarray: Thank you for your good words..

not only do i like that solution, i stole your signature

Never mind my friend. And I have it stolen by another ...!

Starting with the 1st reply till the last.

Hi GeneralDisArray . . . #2
It appears that it is checking row 20, which I don't undestand. I have 19 columns before col T. Any of the cells on row 20 could have or not have data. Example, if Data is entered into columns c-f-g and that is all the data available. The user clicks some place on row 40. What happens

Fortis 1991 . . . #3
Your attachments . . . I make 2 entries into most any cell in the record. It allows me to leave the record and no MsgBox Saving to make entry in column T of record.

GeneralDisArray . . . #6
No columns or rows are protected. What I need the user to make any entry(s) and when finished and they ded not make an entry into T, moving to a row either higher or lower should have a MsgBox telling the user that they have to make an entry in column T. If any cell in the record being worked on has to have an entry in column T of that record.

If data is entered into any cell where T already has a value in that record, the user can change to any record without any MsgBox being displayed


Fortis . . . #7
I don't see how VBA could do this. How could the row trigger a MsgBox due to column T having no daa ??? If you have an idea on how VBA could do this, I'd sure like your suggestion.

Hi Launchnet

Fortis 1991 . . . #3
Your attachments . . . I make 2 entries into most any cell in the record. It allows me to leave the record and no MsgBox Saving to make entry in column T of record.

Maybe i didn't explain good to you(Because of my bad English..), but as i told you in my previous post the range that i control in my example, is Columns N:S, not all the Sheet....

Take a look to the new example and try to type something, anywhwre, when the T Column is Empty.

Then, type something in T2(FOR EXAMPLE) and type something in another column(same row)

For VBA solution, i don't know to help you..

Launchnet, why don't you post an example workbook and let us add this feature to it. That would be easier than explaining:

But, as to your last post (#10)

The workbook i attached earlier was just an example of a Worksheet_Change event driven macro -- that means that ANY change made to the worksheet will cause the macro to run automatically. SO if the user enters data into any cell in the sheet the macro runs AFTER the change is commited to the workbook (which happens when the user presses "enter" or in any way exits a cell he/she has just entered data into). To be clear, the macro will not run when they are entering the data but will run after.

So, what the macro was doing was just capturing the Address of the cell the user has just altered -- it then takes the row number from that address and checks to see if the T column for that Row is blank...IF it is blank THEN the user is prompted for a value to place in the T column.

Does that make sense? please just upload an example sheet for me (or Fotis) to trick out for you. I still think the Validation is a better idea than my macro (i just didn't know that particular validation trick).

Hi GeneralDisarray . . .

I have attached a sample worksheet so that you can visually see what I'm talking about. Please feel free to ask any questions.

This can not work with VBA, but maybe by coding a Private Sub it possibly could.

Well, I found out how to calculate by myself.

Sorry, I posted text to wrong Thread. I will post to correct Thread.

This one is still open.

Thanks everyone for trying. I couldn't figure it out either, so I attached it another way. Just about as good, maybe just as good ?

I have a macro that goes to the last record and then goes down 1 row and then to column a so that the new record can be entered.

I changed it to go to column T.

Then I entered the value of ? in Red Color, with a message box telling the user to change to a good value or delete it if they had hit the wrong button on the dashboard in error.

Thanks again.

I'll mark Solved.