+ Reply to Thread
Results 1 to 17 of 17

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

  1. #1
    Valued Forum Contributor
    Join Date
    09-04-2007
    Location
    Ontario, Ca
    Posts
    624

    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
    Last edited by Launchnet; 12-26-2011 at 10:54 PM. Reason: Typed in wrong value in header
    Thanks for helping . . .
    Matt @ Launchnet

  2. #2
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Force user to make entry into column T when going when leving that record.

    you could make use of the worksheet_change event:

    Please Login or Register  to view this content.
    Edit -- attached example worksheet with this function written into the worksheet_change event.
    Attached Files Attached Files
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Force user to make entry into column T when going when leving that record.

    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.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Force user to make entry into column T when going when leving that record.

    dang, that is a cool way to do it

  5. #5
    Valued Forum Contributor
    Join Date
    09-04-2007
    Location
    Ontario, Ca
    Posts
    624

    Re: Force user to make entry into column T when going when leving that record.

    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

  6. #6
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

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

    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:

    Please Login or Register  to view this content.

  7. #7
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

    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..

  8. #8
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

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

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

  9. #9
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

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

  10. #10
    Valued Forum Contributor
    Join Date
    09-04-2007
    Location
    Ontario, Ca
    Posts
    624

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

    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.

  11. #11
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

    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..
    Attached Files Attached Files

  12. #12
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

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

    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).
    Last edited by GeneralDisarray; 12-22-2011 at 09:54 AM.

  13. #13
    Valued Forum Contributor
    Join Date
    09-04-2007
    Location
    Ontario, Ca
    Posts
    624

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

    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.
    Attached Files Attached Files

  14. #14
    Valued Forum Contributor
    Join Date
    09-04-2007
    Location
    Ontario, Ca
    Posts
    624

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

    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.
    Last edited by Launchnet; 12-23-2011 at 10:53 PM. Reason: Posted to the wrong thread - Sorry

  15. #15
    Valued Forum Contributor
    Join Date
    09-04-2007
    Location
    Ontario, Ca
    Posts
    624

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

    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.

  16. #16
    Registered User
    Join Date
    01-13-2011
    Location
    new york, usa
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: Force user to make entry into column T when going when leving that record.

    sorry for bringing up such an old thread..

    Quote Originally Posted by GeneralDisarray View Post
    you could make use of the worksheet_change event:

    Please Login or Register  to view this content.
    Edit -- attached example worksheet with this function written into the worksheet_change event.
    i like this one!!

    i just wanto modify it a little,

    is it possible that only when i change a certain range it should force me to enter in column "t"?
    also is there a way with another macro to open/close this macro? (something like on/off) ??

  17. #17
    Registered User
    Join Date
    01-13-2011
    Location
    new york, usa
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: Force user to make entry into column T when going when leving that record.

    sorry for bringing up such an old thread..

    Quote Originally Posted by GeneralDisarray View Post
    you could make use of the worksheet_change event:

    Please Login or Register  to view this content.
    Edit -- attached example worksheet with this function written into the worksheet_change event.
    i like this one!!

    i just wanto modify it a little,

    is it possible that only when i change a certain range it should force me to enter in column "t"?
    also is there a way with another macro to open/close this macro? (something like on/off) ??

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1