Search and Report multiple values using userforms

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.

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.

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.

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

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.

Please Login or Register  to view this content.

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.

Originally Posted by 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.

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.