If JRY selected from Picklist then a input box will appear. Entry stored on seperate sheet

I have a pick list of various codes, when user selects only JRY I would like an input box to come up requesting user input "Jury ID", the value would be stored on a seperate sheet along with a Date and Employee Needing Coverage name from the same line were JRY appears. This is a form so JRY could appear 2 - 3 times in a month on any line in the range of C15 - C84. .  There are various other codes in the picklist but only "JRY" will trigger the input box, then when inputed box would go away. User would remain on "Form" sheet.
Walter WilliamsConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
It should be possible to set up a Worksheet_Change event to capture something like this, but there is an issue with the process.

As I was replicating the example workbook, I note that it's quite possible that the JRY replacement code will be selected BEFORE the name for the Employee Needing Coverage is entered (since most western forms tend to enter data from left to right).  This causes a problem:  if JRY is selected before the Date and/or Employee Needing Coverage fields are complete, no data can be copied to the separate Jury data sheet.

If a process were to wait for all three values to be complete, then the additional message box wouldn't appear until after the last value was complete.

How would you expect this to behave?

Walter WilliamsConsultantAuthor Commented:

You bring up a great point, thanks for the question..  For a quick fix, I have moved column C "Replacement Code" to far right between column G and G is now H. This would make selecting JRY one of the last fields they should select.  I am working on trying to force an order of entry to insure Date, Employee Needing Coverage and Replacement Code are filled out first in that order.
Glenn RayExcel VBA DeveloperCommented:
That would work.  Additionally, some extra control in a Worksheet_Change event could prevent a selection of a Replacement Code unless the Date and Employee Needing Coverage fields were complete (not blank) for that row.

I'll see what I can whip up.

Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Walter WilliamsConsultantAuthor Commented:
ok, thank you very very much...
Walter WilliamsConsultantAuthor Commented:
Hi Glenn,

Just checking back to see if you had any luck? Were you able to come up with anything.

Thank you..
Glenn RayExcel VBA DeveloperCommented:
Sorry; I knew I forgot to check on something over the weekend.  I'll take a look at this and post back shortly.
Walter WilliamsConsultantAuthor Commented:
lol..  Thanks Glenn, much appreciated...
Glenn RayExcel VBA DeveloperCommented:
Sorry this took so long.  I ran into a technical issue with Excel that I couldn't resolve (and may have to post my own question here @ EE if I can't find an explanation).  But, I created a workaround that behaves almost exactly the same.

There is a Worksheet_Change event that checks the value for the Replacement Code; if "JRY", an dialog box appears prompting for a required Jury ID.
Option Explicit
Dim rng As Range
Dim cl As Object
Dim strJuryID, strEmpNeed As String
Dim dtReqDate As Date
Dim r As Integer
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("$G$15:$G$84")) Is Nothing Then
        If Target.Value <> "" And _
            ((Target.Offset(0, -6) = "") Or (Target.Offset(0, -3) = "")) Then
            Target.Value = ""
            MsgBox "Please complete previous fields before" & vbLf & _
                   "selecting a replacement code.", vbExclamation + vbOKOnly, "Additional Data Required"
        ElseIf Target.Value = "JRY" Then
            strJuryID = frmJuryID.txtJuryID
            dtReqDate = Target.Offset(0, -6).Value
            strEmpNeed = Target.Offset(0, -3).Value
            'determine if this data already exists, if so, do not update
                Set rng = Sheets("JuryData").Range("A2", Sheets("JuryData").Range("A20000").End(xlUp))
                For Each cl In rng
                    If (cl.Value = dtReqDate) And (cl.Offset(0, 1).Value = strEmpNeed) And _
                       (cl.Offset(0, 2).Text = strJuryID) Then
                       Exit Sub  'because line already exists
                    End If
                Next cl
            'otherwise locate last available row
            r = Sheets("JuryData").Range("A20000").End(xlUp).Offset(1, 0).Row
            With Sheets("JuryData")
                .Cells(r, 1) = dtReqDate
                .Cells(r, 2) = strEmpNeed
                .Cells(r, 3) = strJuryID
            End With
        End If
    End If
End Sub

Open in new window

If no value is supplied, the data is not duplicated to the separate "JuryData" sheet.  The code checks to see if an entry with the same date, name, and jury ID already exists.  If so, nothing happens; otherwise, the values are added to the table.  It DOES NOT CHECK or handle any processing if the values are changed or deleted on the main sheet; that is a significant addition in work with all the possible variable changes.

See the attached workbook for an example.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Walter WilliamsConsultantAuthor Commented:
Hey Glenn,  

This is awesome.. you the man!!  very impressed..     It is doing exactly what I wanted it to do..

The form will be recycled each month so basically everything deleted in the fields and re filled out for the next month. I get an error when I try to delete what is currently on the form and it throws me into a run-time error screen.   Any suggestions. I have attached the final file so you can see how it works.
Glenn RayExcel VBA DeveloperCommented:
Add this line of code to the top of the Worksheet_Change event (in the "Form" sheet object in VBA)
If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then Exit Sub

Open in new window

If Not Intersect(Target, Range("$G$15:$G$324")) Is Nothing Then

This will avoid the JRY check if you select multiple cells to delete.

Walter WilliamsConsultantAuthor Commented:
Perfect...   That did it....   Thank you again this is awesome and exactly what I was needing.
Glenn RayExcel VBA DeveloperCommented:
You're welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.