Search and Report multiple values using userforms

[Vaticus]

Hello all,

I have a workbook for tracking patient's medical exams which consists of 3 sheets of data. I would like to use a userform to search all these sheets for a patient's name and then generate a report of the status of all their open physicals in another userform (or msgbox if that would work). I have done alot of looking around the web to try and find something like this and haven't found anything, so I'm unsure where to even begin.

In the attached example you can see how my book is laid out. The sheet names themselves represent the "status". In the code I have the userform for searching, and the userform for reporting. I want to be able to put a name in the textbox on the search userform and click the report button. I would then like for the code to search for all occurences of that patient across the workbook and populate the report userform with the values of cells in the found row (as the Report userform is laid out). The "status" column of the textboxes should populate the name of the sheet in which that string of information was found.

So, my 2 main questions are: is this even possible? and what functions would perform it?

Thanks.

[jaslake]

Hi Vaticus

You have three(3) sheets of data ("Pending Submittal", "Pending Stamp" & "2YR Hold"). I understand that any individual Patient Name CAN appear on each individual sheet BUT, will any individual Patient Name appear MORE than once on any individual sheet?

If NO why do you have five(5) Status Textboxes in the Report UserForm.

If YES what if the individual Patient Name appears MORE than five(5) times in those data sheets?

Why are you searching on Name rather than SSN Number?

Do you not have a Patient List to use as a source? This CAN be built from the three (3) sheets of data but I'd assume it's already available to you.

I'd use a ComboBox rather than a TextBox for the Patient Report search form and populate the ComboBox with the Patient List allowing for duplicate last names and the MatchEntry feature of the ComboBox.

Some thoughts for you to ponder...I'll work on this with you...let me know your thoughts.

[Vaticus]

Thanks for the reply.

Yes, it is possible for patients to appear multiple times on each sheet. There should be no occasions in which a patient is listed more than 5 times because this is a yearly physical tracker. So each year the patient comes in and starts a physical, which puts them on "Pending Submittal" sheet. Then, once they have been submitted, they move to "Pending Stamp". Once stamped they become a "2YR Hold". The "2YR Hold" sheet automatically deletetes any patient who has been on it for 2 years. So I chose the number five because that allows a patient to have 2 occurences in the 2YR hold (will happen regularly), and then 3 more occurences between the other 2 sheets (usually is just 1, rarely 2, and almost never 3).

I am searching by name because 90% of the time, that is the information I have to search for them. It isn't usually the patient themselves checking on the status of the physical, but their leaders, so it's more time efficient to be able to search by name rather than have to look up each SSN in our online personnel system.

I do not have a Patient List, but as you said I could build one. Honestly I hadn't thought of using combo boxes, but I will definitely do that now.

[watersev]

hi Vaticus, please check attachment

[watersev]

@Vaticus

can I ask you to check file in post #3?

[Vaticus]

Watersev,

That is perfect. Thank you. Only problem I am having is when I put it in my live tracker it isn't populating the combox with all the patient names. I am looking at the code right now to try and figure it out to reset the range that populates it. But maybe you can help me identify the part. On the live tracker I would need it to pull every patient's name no matter what sheet it is on. There are alot of patient's on the "2YR Hold" sheet that are not on the other sheets and so on. Also, on my live tracker there is another sheet in front of the "Patient Input" sheet which I am sure is affecting it.

Edit:

Okay, was able to fix that. Ran into one more problem, but I want to take a crack at finding and fixing it myself. I'll post if I am unable. Thank you guys again.

[Vaticus]

Okay, after playing with it some I realized that the change I made in code in order to populate all patient names is what caused the second problem to occur. In my live work book "Pending Submittal" is sheet 3, "Pending Stamp" is sheet 4, and "2YR Hold" is sheet 5. So of course the code from the test book only pulls up the patients from sheets 3 and 4. When I changed the code to refer to the proper sheets, two problems came up:

1. The ComboBox populates all the names from all the sheets, but it does so in sheet order. So there are duplicate names in the combobox where each one only reports the data for the sheet that name occurs on.

2. When I click the report button the resulting userform does not fill in the Name and SSN boxes.

I am sure it's a user error on my part when I changed the code, but I am not sure how.

Also, if multiple occurrences of the name are on one sheet, it is only reporting the first one. I would like for it to report all of them.

[jaslake]

Hi Vaticus
I anticipated this issue

So there are duplicate names in the combobox where each one only reports the data for the sheet that name occurs on.

and is the reason I asked this question

Do you not have a Patient List to use as a source?

and further suggested this

Do you not have a Patient List to use as a source? This CAN be built from the three (3) sheets of data

For the moment I'll leave this to you and Watersev...if you don't get it resolved let me know.

[watersev]

please check attachment

The sheet names has been recorded in the code so in case of sheet name change you will need to amend sheet name in the code accordingly. The order of the sheets in the workbook does not matter any more.

[Vaticus]

@Watersev

That solved the issue of not populating all the names, however when I put the code in my live tracker this problem still occurs.

1. The ComboBox populates all the names from all the sheets, but it does so in sheet order. So there are duplicate names in the combobox where each one only reports the data for the sheet that name occurs on.

So, I tried Jaslake's advice and made a patient list. It is on the sheet named "Patient List". Then I tried changing this portion of code in order to populate the ComboBox from that list.

Private Sub UserForm_Initialize()

Dim i As Integer, lrow As Long, rng As Range, cl, istr As String

Me.ComboBox1.Text = ""

For Each nm In Array("Patient List")
    With Sheets(nm)
        lrow = .Cells(Rows.Count, 1).End(xlUp).Row
        If lrow > 1 Then
            Set rng = .Range("a2", .Cells(lrow, 1))
            For Each cl In rng
                If InStr(istr, cl) = 0 Then
                    If istr = "" Then istr = cl Else istr = istr & "|" & cl
                End If
            Next
        End If
    End With
Next

Me.ComboBox1.List = Split(istr, "|")

End Sub

That resolved the issue of duplicating names in the ComboBox, but when I select a name which I know has multiple occurences on different sheets, it only displays one result. For example, if "Washington, George" were on "Pending Stamp" and "2YR Hold", it would only report the occurence on "Pending Stamp". The strange thing is that the original code works perfectly in the Search Test workbook, no matter how I re arrange the names between sheets and how many duplicates there are. All the sheet names, locations etc are the same in my live tracker as they are in the test, so I'm not sure why it would do that. I've uploaded another version of the Search Test workbook that includes a "Patient List" sheet. Do I need to change something in the other parts of the code to make the report work properly when the combobox is populated by this list?

Edit: Nevermind, I figured out what was wrong. Got it working off the Patient List now. Thank you very much for your guys help.

[watersev]

@Watersev

That solved the issue of not populating all the names, however when I put the code in my live tracker this problem still occurs.

1. The ComboBox populates all the names from all the sheets, but it does so in sheet order. So there are duplicate names in the combobox where each one only reports the data for the sheet that name occurs on.

I do not understand what's the problem with the combobox populating names. The code inputs unique names only.

Any chance to have a look at your real-life workbook or a mock up workbook that has the same structure and data without sensitive information?

The only suggestion for now is that you have them written differently. If that is the case it can be easily solved without any patient list. We can can search patient by its number.

[Vaticus]

Thanks,

Yeah it was a period at the end of one of the name occurences that was throwing the whole thing off.

[watersev]

please check attachment, I've added additional initials for two people on Pending Stamp to replicate your conditions