Return multiple values to a UserForm using one search term

Hi all,

I am trying to debug this code. I am using a search function to retrieve values from a workbook and return them to multiple textboxes in my userform. So each record is assigned a projectID. Multiple records may have the same ProjectID to a maximum of 4 so there are 4 different sets of textboxes. The code below works but there are some problems:

1. It doesn't return every value
2. the values are returned in the wrong order (it should grab the values and return them in order from top to bottom, currently it has reversed this order)

Any help would be greatly appreciated!

Please Login or Register  to view this content.

6StingJazzer,

Thank you for the feedback and response. I am very very new to VBA and am attempting to learn by doing. I will prepare a workbook with this code and post it to the forum at some point tomorrow. I will attempt to explain what I am hoping to accomplish then. I understand what is here is vague but I am unsure how to explain the problems I am running into due to my lack of experience with code and VBA.

Thanks again for the response!

Wolfman

Hey Jeff I've prepared a workbook with the code I posted here before. The idea is that there are multiple permits to one project ID. I want to be able to call upon all of the available permit information using the projectID (max of 4 permit requirements per ProjectID). I then would like to be able to edit them from the UserForm (which is the reason I am not using a Listbox). Let me know what you think!

Wolf

Wolf,

Here is your workbook [clapforthewolfman=SampleWorkbook- Search and Edit Debug.xlsm] with the code I posted above replacing your original code. The only change I made was to add variable declarations, and code to blank out the fields on the form to initialize if you do a search for another project.

BTW cool username. I was in high school when that song came out.

Thanks for this! Search function works very well. Now what can be done to make edits to the data retrieved?

And yeah the GUess Who are awesome!

Originally Posted by clapforthewolfman

Thanks for this! Search function works very well. Now what can be done to make edits to the data retrieved?

I can give you the strategy, not sure how much help you need coding it.

The most straightforward way is to keep an array (or four variables) that keep track of what row each match was found on. These need to be declared globally, above the first Sub. You would set these values in each of the four blocks where you identify a match. Then create a Sub for each control (you have 16 of them) to handle the Change event. The Change event will take the new value in the control and then update the corresponding cell in the worksheet. It will look up the row in the array (or four variables), and each control always corresponds to the same column.

This is a very strategic explanation. I think you will get more out of this if you try to implement it yourself but I can provide the code if you need it. It will just take a little time to get to it.

Originally Posted by 6StringJazzer

I can give you the strategy, not sure how much help you need coding it.

The most straightforward way is to keep an array (or four variables) that keep track of what row each match was found on. These need to be declared globally, above the first Sub. You would set these values in each of the four blocks where you identify a match. Then create a Sub for each control (you have 16 of them) to handle the Change event. The Change event will take the new value in the control and then update the corresponding cell in the worksheet. It will look up the row in the array (or four variables), and each control always corresponds to the same column.

This is a very strategic explanation. I think you will get more out of this if you try to implement it yourself but I can provide the code if you need it. It will just take a little time to get to it.

I appreciate the help! I am going to try to use this as a guide but my VBA knowledge is rather limited. If you could send code for this it would be greatly appreciated. If I find a way between now and when you have a chance to get to this I will post it on this thread.

Thanks!

Here is a modification. [clapforthewolfman=SampleWorkbook- Search and Edit Debug.xlsm]

I also noticed that you are getting data from columns with headings that don't match the labels on the text boxes on your form. I modified the code so they match.

I also set up another sheet called Status Values with a list of possible status values to populate the comboboxes. You can change this list to be whatever you want, but it must start in A1.