Validate User Input

Add my lines 5 to 8.

'If user doesn't enter the number 1
'then a message box warns they can't go any further
' If they do enter the number 1 then continue

If strMyIPBMsg <> "1" Then 
    Msgbox "You can't go further"
  Exit Sub
End If


 With Sheets("Master")
 
 
 lngLstRow = .UsedRange.Row + .UsedRange.Rows.Count

Select allOpen in new window

I thought it would go back to allow user to try again.

Msgbox "You can't go further" - show again

Can you do that? The input boxes have ok and cancel so user can cancel if they like. Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lngLstRow&
Dim strMyIPBMsg$
Dim p As Range
Dim LastRow As Long
Dim c As Range

LastRow = Cells(Rows.Count, "C").End(xlUp).Row

For Each c In Range("C1:C" & LastRow)
    If UCase(c.Value) = "ADD NEW" Then
        Do Until strMyIPBMsg = "1"
            strMyIPBMsg = InputBox("Please enter the number 1 to confirm:", "Confirmation")
            If strMyIPBMsg <> "1" Then
                MsgBox "You can't go further"
            End If
        Loop

        With Sheets("Master")
            lngLstRow = .UsedRange.Row + .UsedRange.Rows.Count
            .Range("H" & lngLstRow).Value = strMyIPBMsg
        End With

        Sheets("Courses").Select

        strMyIPBMsg = InputBox("Enter Course Number (ex. AAA-1050):", "Course Number")

        With Sheets("Master")
            .Range("A" & lngLstRow).Value = strMyIPBMsg
        End With

        Sheets("Courses").Select
        strMyIPBMsg = InputBox("Enter Course Name (ex. Basic Accountin Principles):", "Course Name")

        With Sheets("Master")

            .Range("B" & lngLstRow).Value = strMyIPBMsg
        End With

        Sheets("Courses").Select

        strMyIPBMsg = InputBox("Enter Department (ex. PEP):", "Department")

        With Sheets("Master")
            .Range("C" & lngLstRow).Value = strMyIPBMsg
        End With

        Sheets("Courses").Select
    End If
Next c

Select allOpen in new window

Okay, almost there. If I click cancel it keeps popping up, it should come back if the user clicks OK but not if they click cancel. Sorry I wasn't specific.

Thanks, that will work but now I noticed that the box pops up whenever I double click in any cell on my worksheet, not just cells in C column. I didn't have that happen until I entered the code...

Add this at the top of the code


If Target.Column <> 3 Then Exit Sub

Thanks, that fixed it! I also modifed the code a bit:


strMyIPBMsg = InputBox("Please enter the number 1 to confirm:", "Confirmation")
           If strMyIPBMsg <> "1" Then
    MsgBox "You must enter the number 1 to continue to add a new course."
     strMyIPBMsg = InputBox("Please enter the number 1 to confirm:", "Confirmation")
     If strMyIPBMsg <> "1" Then
   MsgBox "You did not enter the number 1, this text box will close."
     
  Exit Sub
End If

    End If

tracyms,
While MartinLiss has certainly given you what you asked for, I respectfully suggest that you are not going about this in the best fashion.

The Worksheet_Change sub is triggered each time the user (or a macro) enters data (presumably on the Courses worksheet). If you have more than one ADD NEW in column C, then the user may get confused which one he is responding to. And there is no need to run the macro at all if the user is changing something that doesn't affect whether column C contains ADD NEW. For these reasons, you may want to consider testing whether a cell has been changed that might affect a cell in column C. I can help you with this, but would need to see your workbook before suggesting code changes.

The code also selects worksheets several times. This will cause an annoying flicker as the macro steps through the instructions. You can probably eliminate those statements.

Third, rather than force the user to enter a 1, why not just ask a Yes/No question? If the answer is Yes, then the code can put a 1 in your string variable.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lngLstRow As Long
Dim strMyIPBMsg As String
Dim p As Range
Dim LastRow As Long
Dim c As Range

LastRow = Cells(Rows.Count, "C").End(xlUp).Row

For Each c In Range("C1:C" & LastRow).Cells
    If UCase(c.Value) = "ADD NEW" Then
        If MsgBox("Please confirm that you want to ADD NEW", vbYesNo) = vbYes Then
            strMyIPBMsg = "1"
        Else
            MsgBox "You can't go further"
            'code to reset the value of cell c should be entered here
            Exit Sub
        End If

        With Sheets("Master")
            lngLstRow = .UsedRange.Row + .UsedRange.Rows.Count
            .Range("H" & lngLstRow).Value = strMyIPBMsg
            strMyIPBMsg = InputBox("Enter Course Number (ex. AAA-1050):", "Course Number")
            .Range("A" & lngLstRow).Value = strMyIPBMsg
            strMyIPBMsg = InputBox("Enter Course Name (ex. Basic Accountin Principles):", "Course Name")
            .Range("B" & lngLstRow).Value = strMyIPBMsg
            strMyIPBMsg = InputBox("Enter Department (ex. PEP):", "Department")
            .Range("C" & lngLstRow).Value = strMyIPBMsg
        End With
    End If
Next
End Sub

Select allOpen in new window

Brad

Yes Brad, this is very good. I can use as is. Which presents the question that if a user cancels some or all of the rest of the questions (after entering yes and putting a "1" in column H), how can I delete what they did in the Master?

One possibility is to trap the ADD NEW entry (using the Worksheet_Change sub), then display a userform with spaces for all the other data required. This userform would have an OK and a Cancel button. If the user presses Cancel, then the ADD NEW would be revoked. If the user presses OK, then the text boxes for Course Number, Name and Department would be validated. If validation passes, then the data is added to Master worksheet. If validation fails, then user is given  chance to correct (or Cancel).

Brad

You have to create user forms (which take more time and I'm not too familiar with) which is why is opted for the input boxes. Thanks for the suggestion. I can weed out the "1s" that don't have a full set of data.

I can always post another question for the user forms...:-)

MartinLiss 100 points honorable mention.

The attached workbook shows what I had in mind with the userform. When you type "add new" in column C, the userform pops up asking for additional data. If it passes Validation, then it is added to the Master worksheet. If not, then the user gets a chance to do it again. If user hits Cancel, then "add new" is erased.

Note that I have a simple Worksheet_Change sub in worksheet Courses code pane, a regular module sheet to declare a Boolean public variable and the userform (with code behind it).

Brad
ValidateUserInputQ28042458.xlsm

Wow! Fantastic! I will import your form in my spreadsheet. It's so much cleaner! I'm working on a project at work for depts to enter their regular courses and add new ones as needed. I needed a way to "flag" the new courses and the form is perfect. The users will surely be impressed! :-) Thank you Brad!!!!!!!!!!!!!!!!!!!!!!

Brad,

How can I prevent duplicates for course number? I have unique course numbers in the Master sheet Course Number column A and any new ones entered should be unique (not match the ones already there).  Below is the input box code for Course Number values to be entered:

.Range("A" & lngLstRow).Value = InputBox("Enter Course Number (ex. AAA-1050):", "Course Number")
   
I'm opening another question for this and will post soon.    

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngLstRow As Long
Dim c As Range

Set c = Intersect(Target, Range("C:C"))
If c Is Nothing Then Exit Sub
If UCase(c.Value) = "ADD NEW" Then
    If MsgBox("Please confirm that you want to ADD NEW", vbYesNo) = vbNo Then
        MsgBox "You can't go further"
        'code to reset the value of cell c should be entered here
        Exit Sub
    End If

    With Sheets("Master")
        lngLstRow = .UsedRange.Row + .UsedRange.Rows.Count
        .Range("H" & lngLstRow).Value = "1"
        .Range("A" & lngLstRow).Value = InputBox("Enter Course Number (ex. AAA-1050):", "Course Number")
        .Range("B" & lngLstRow).Value = InputBox("Enter Course Name (ex. Basic Accountin Principles):", "Course Name")
        .Range("C" & lngLstRow).Value = InputBox("Enter Department (ex. PEP):", "Department")
    End With
End If
End Sub

Select allOpen in new window

If you are using the userform, the right place to test is in the Validation function.

If you want to stick with the Worksheet_Change sub, the right approach is to return the result of the InputBox to a variable in instruction 17, then accept or reject it after testing for duplication before proceeding.

It's much cleaner to do it with the userform.

Because your new question has to do with implementation, I suggest continuing the discussion in this thread rather than opening a new question.

Brad

No, I want to use the user form, didn't know which would be easier for you. Here is the question:

https://www.experts-exchange.com/askQuestion.jsp?taid=93

Oops! Here is the right one.

https://www.experts-exchange.com/questions/28044380/No-duplicates-in-User-Form.html


Sorry, I already posted the question. I didn't want to keep asking a different question without giving  you points for it.