Show Form from cell click, then CommandButton to pass to next ActiveCell

Question

I'm creating an input form for use with a 'tablet' PC in Excel for our guys to fill out when in the field. As inputs like drop-down menus and the like are quite fiddly, I wish to use a basic input Form with big Pass/Fail Command buttons. Rather than using individual CommandBoxes to call the form, I've elected to use the following code (within the object/sheet) to call up the Form when certain cells are selected:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 2 Then
        If Not Intersect(Target, Me.Range("W16:W46,W74:W143")) Is Nothing Then
            PFInputForm.Show
        End If
    End If
End Sub

The input form calls on information relative to the ActiveCell, to populate the Item#, Description & Comments using the following code in the FORM.

Private Sub UserForm_Initialize()
    PFInputForm.INPUTCOMMENT.Text = CStr(ActiveCell.Offset(0, 1).Value)
    PFInputForm.ITEMNO = "Item " & ActiveCell.Offset(0, -2)
    PFInputForm.ITEMDESCR = ActiveCell.Offset(0, -1)
End Sub

The intention is for the user to enter a comment (if applicable), then click one of the four buttons "Pass", "Fail", "Pass & Continue" and "Fail & Continue".

If they click "Pass" or "Fail", I have code in the Form return the text in the Comment box to the adjacent cell (using Offset), enter "P" or "F" respectively in the Active Cell, and close the user form with no problems (using the following code in the FORM).

Private Sub PASSButton_Click()
    PFInputForm.Hide
    ActiveCell = "P"
    ActiveCell.Offset(0, 1) = PFInputForm.INPUTCOMMENT
End Sub

The problem I have is when I want the user to click either "Pass & Continue" or "Fail & Continue", where the above is meant to happen, but the Active Cell then moves down one cell, and opens up a new Form specific for that row.

The code I currently run from the FORM for the "Pass & Continue" is below. The problem I have is that when the new Form opens, I can see the new active cell is selected on the Sheet, but the form is still calling on the information from the previous ActiveCell.

Private Sub PCONTButton_Click()
    PFInputForm.Hide
    ActiveCell = "P"
    ActiveCell.Offset(0, 1) = PFInputForm.INPUTCOMMENT
    ActiveCell.Offset(1, 0).Select
End Sub  

I've unsuccessfully tried a few variants, including hiding the user form (both in the FORM code and the Sheet code), but after a few hours of trawling this (and other) forums, I feel I'm now just stabbing in the dark!

Answer 1

I found a solution thanks to another user. I added an additional IF THEN statement to the function calling the form, which is looking for a flag (either 1 or 0) in cell AI14 (which starts as 1):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("AI14") Then
        If Selection.Count = 2 Then
            If Not Intersect(Target, Me.Range("W16:W46,W74:W143")) Is Nothing Then
                Range("AI14") = 0
                PFInputForm.Show
            End If
        End If
    End If
End Sub  

I then added a flag change back to 1 for the PASS and FAIL buttons:

Private Sub PASSButton_Click()
    Range("AI14") = 1
    ActiveCell = "P"
    ActiveCell.Offset(0, 1) = PFInputForm.INPUTCOMMENT
    PFInputForm.Hide
End Sub

I omit the flag change on the "Continue" buttons, to keep the flag at zero, so the top code doesn't continue. I then added some extra content to the Continue button code so that it pulls through the related information from the new ActiveCell:

Private Sub PCONTButton_Click()
    ActiveCell = "P"
    ActiveCell.Offset(0, 1) = PFInputForm.INPUTCOMMENT
    ActiveCell.Offset(1, 0).Select

    PFInputForm.INPUTCOMMENT.Text = CStr(ActiveCell.Offset(0, 1).Value)
    PFInputForm.ITEMNO = "Item " & ActiveCell.Offset(0, -2)
    PFInputForm.ITEMDESCR = ActiveCell.Offset(0, -1)
End Sub

This code uses the same information source as the code for initializing the form (see original post)

I just need to tidy the end of the "continue" coding so that the flag is returned to 1 once the last item in the list has been reached.

Hope this helps!