Event Code to Test Cell Content After Data Entry

  • I'm trying to write event code to test the contents of every cell in a given column after data is entered. Depending on the result, the focus would move to one of two cells in that particular row.
    For example, if the column to be tested is col C and I'm entering data into row 3 then the code would be:

    Code
    If Range("C3")="PVA"
    Then Range("Z3").Activate
    Else Range("D3").Activate


    Then, if the focus had moved to Range("Z3"), after data is entered in that cell I want to return the focus to Range("D3")
    What I need is generic code that tests every cell in column C after data is entered


    Help on this would be greatly appreciated

  • Re: Event Code to Test Cell Content After Data Entry


    Try it like this......


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("C:C")) Is Nothing Then
       If Target = "PVA" Then
          Cells(Target.Row, "Z").Select
       Else
          Cells(Target.Row, "D").Select
       End If
    End If
    End Sub

    Regards.
    sktneer

  • Re: Event Code to Test Cell Content After Data Entry


    Thanks sktneer_1, but that's not the answer. Looking at my post, sorry, I may not have explained it clearly and was just about to amend it when I saw your response.


    I want to test the cell in col C for whatever row I'm in. I'm actually at row 1534 in real life. Today I want to test cell C1534 after entering a value (OnClick?) to see if contains 'PVA'. If it does I want to jump across to Z1534, enter data into that cell, then return to D1534. If C1534 contains anything other than 'PVA' (which is normally the case) then I want to move to D1534 and continue entering data as normal across that row. Then, when I move down to row 1535 and reach col C I want C1535 tested the same way. And so on down, for every time something is entered in col C.

  • Re: Event Code to Test Cell Content After Data Entry


    Not explained well to start with, I made the same assumption as sktneer_1 but these procedures may be what you are looking for.


    Excel does not have an OnClick event, so DoubleClick used. You should be aware VBA, by default, is case sensitive when comparing text. You need to explicitly convert the comparison to the same case if this is important or use the Option Compare directive at the top of the module.


    This is still vague

    Quote

    I want to test cell C1534 after entering a value


    That is what the code in the first reply does, so I changed it to fire on a double click rather than a cell change.

  • Re: Event Code to Test Cell Content After Data Entry


    Yes, I agree, what's clear in my head occasionally doesn't come out so clear on screen. As I said, I was about to amend it but I was a bit late. Apologies again.


    It's giving an error message when I run the code:
    Compile error:
    Ambiguous name detected: Worksheet_BeforeDoubleClick

  • Re: Event Code to Test Cell Content After Data Entry


    You now have 2 WorkSheet_BeforeDoubleClick events in the same scope... You will have to combine them

  • Re: Event Code to Test Cell Content After Data Entry


    Thanks Grimes0332. The answer to that little issue was that I'd forgotten to delete sktneer_1's code. My error.


    However, I've entered PVA (with correct case) in cell C1535, clicked and the cursor moved to D1535 where it should have gone to Z1535. Maybe - as you said - I'm still not being clear enough, so:
    I'm entering data across row 1535
    In cell A1535, write some data, hit Enter, cursor moves to B1535
    In cell B1535, write data, hit Enter, cursor moves to C1535
    In cell C1535, write data, hit Enter, cursor would normally move to D1535
    At this point I want the code to test what value I've written into C1535. If it's PVA I want to jump across to cell Z1535 and write additional data. When it's written, and I hit Enter, I want to move back across to cell D1535. If (as is normal) I've written anything other than PVA into cell C1535 then I want the cursor to move to cell D1535.
    And I want this process to happen for all of column C. Every time I enter a new row of data I want to test the contents of the col C cell, in that row, to see whether or not I have written PVA.


    Hope I've nailed it this time!


    I've never previously had coding that has had anything other than closed brackets at the end of the Sub line so just to be sure that I've done the right thing: I've entered the code into Module 1 of the workbook - is that the correct place for it?

  • Re: Event Code to Test Cell Content After Data Entry


    Quote

    I've entered the code into Module 1 of the workbook - is that the correct place for it


    No - it should go in the code module for the worksheet.


    You have everything you need already, some minor changes are needed. Keep in mind that code is not difficult to understand - read it out loud and you can understand what it does.


    sktneer_1's original code changed with comments


    The first 2 lines must be at the very top of the code module before any other code. I said earlier VBA is case sensitive, that turns it off. 'pVa' = 'PVA'

  • Re: Event Code to Test Cell Content After Data Entry


    Or this with a slight change....


    Regards.
    sktneer

  • Re: Event Code to Test Cell Content After Data Entry


    Thanks guys - works like a charm!


    Very much appreciated - another small step to becoming a guru completed. At my learning pace - only 17 years to go . . . .

  • Re: Event Code to Test Cell Content After Data Entry


    Quote from OldFella;769281

    Thanks guys - works like a charm!


    You're welcome. Glad we could help.


    Quote from OldFella;769281

    Very much appreciated - another small step to becoming a guru completed. At my learning pace - only 17 years to go . . . .


    Sounds interesting. :)

    Regards.
    sktneer

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!