Hi all,
I am looking for some help with VBA. What I want to have happen is this: Search for a project ID on a specific worksheet. When the value returns True all of the associated records with that Project ID populate in the UserForm listbox. There are four columns. HEre is the code I have so far but I know that it is wrong:
Please Login or Register to view this content.
Any help you can offer would be greatly appreciated!
Cheers,
Wolf
If item_in_review is Dim'd a String then:
is missing a .ValuePlease Login or Register to view this content.
And if you declaring a location in the list, I think you want to make your ranges single cell like .Range("A2").value or if its a variable based on your row then .Range("A" & row_number).Value.
This is just a quick review. I could dig deeper if you'd like.
Thanks,
Solus
Please remember the following:
1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.Highlight the code in your post and press the # button in the toolbar.2. Show appreciation to those who have helped you by clickingbelow their posts.
3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
"Slow is smooth, smooth is fast."
Thanks Solus. I need help with the code to populate the information to the listbox (and then to eventually be able to edit the information). The search function finds the Project ID. I want only the records that match the project ID to populate in the listbox. There are four columns of information. What is the best way to go about this?
The best way is probably to attach a sample workbook so we can take a look
Would you like suggestions so you can work on it yourself? Or a viable solution?
Give a man a fish... Haha I'll prepare a sample workbook.
Alright here's a workbook with aforementioned code. Thanks!
So again the idea is to search project ID and return the other 4 columns of information with only the associated Project ID in the Listbox. Might be good to add some values with the same ProjectID (so there are two or three entries with P000125 for example). Then from there I'd like to be able to make edits but we can cross that bridge when we get to it I suppose. I'll be working on this as well but my knowledge of VBA is not too strong and I've never used listboxes.
My web search didn't bring back to much helpful info either
Just to be crystal clear, there should never be two identical Project IDs?
No there should be two identical ProjectIDs. I should have included that in the sample data set. I am using the listbox so I can return multiple results with the same ProjectID.
Multiple Permit Agency, Status, Application and Permit Numbers for one project ID but not two identical project ID's?
Yeah so you could have
P000125 Agency 1 Ongoing Date Permit Number
P000125 Agency 1 Ongoing Date Permit Number
Where everything is the same except the permit number. Project ID's would be the same because this is just a list of associated permits for a given project. One project may have multiple permit values.
Hope that makes sense!
Last edited by clapforthewolfman; 07-17-2013 at 02:07 PM. Reason: Duplicate
You would really be better with SQL and creating a database. If you have not experience with this and you think you can make it work with excel then here is what I have for a start: Solus SampleWorkbook.xlsm
Next we'll work on creating new records and saving changes to current records...
I do already have a form that works for creating records. It needs to be done in Excel or else I would definitely be looking to other programs. I will take a look at what you have here thanks! The saving changes to current records I have figured out but just not with lisboxes. Let me look this over and I will get back on the forum today.
Thanks again,
Wolf
Why does it require a line break? Is there a workaround to this?
You would really be better with SQL and creating a database. If you have not experience with this and you think you can make it work with excel then here is what I have for a start: Attachment 250997
Next we'll work on creating new records and saving changes to current records...
Same thing for END OF RECORDS. This won't be a static form. Otherwise this code looks awesome! This will be the hardest part of this excel workbook so I do appreciate the help!
If you read/write/change from the userform it won't matter if its not a static database. In fact you could sent application.visible = false cause you won't even need the spreadsheet. Its simply a place to store data. All interaction could (if you chose to do so) happen through your UI.
But to you question about work arounds. I suppose you could call it that. Since the number of rows for each record is not static (you could have 1 permit in a record or 31) the function to return the number of rows included in any specific record requires that there be at least 1 row between records.
I'm trying to find a way to prevent this, but with SQL it wouldn't matter. Spreadsheets, however, operate a little differently.
Ok thanks. I have a few other little issues not sure if this can be resolved... The way you have it laid out currently there is 1 project ID and then multiple data. I want the Project ID to be in each cell where there is data. So for example:
P000125 456456 Ongoing 7/6/2013 Agency 3
P000125 456457 Ongoing 7/6/2013 Agency 4
P000125 456458 Completed 7/6/2013 Agency 5
The search function won't work if it's arranged like this. Any idea why?
Its not set up to work in that methodThats why I asked if only one record for each project number would be found or if there would be multiple, similar to the way you have above. We'll have to retool the project. Do you want all records for a project number displayed at once i.e. all three records shown above when P000125 is searched? Or do you want the records displayed one at a time i.e. search P000125 and 456456 is show, hit next record and 456457 is shown etc.
Ahh I see. Yeah all displayed at once would be ideal. Whatever will be easier for editing. The idea is to be able to make edits to the workbook purely through userforms to minimize error.
Your original code works for me, with a few tweaks.
PS Set ColumnHeaders for lbPermit to No, you can't have column headers unless you populate with a range via RowSource.Please Login or Register to view this content.
If posting code please use code tags, see here.
This is great! Thank you Norie for posting here ( I know this code was from another thread). How would I use RowSource to populate Headers? Also any idea it it would be possible to edit the information once it has been presented in the listbox? Thanks again!!
That code isn't from another thread, it's from the workbook you uploaded.
I just made some minor tweaks to make it work.
You would only use RowSource if you wanted to populate from a contiguous range.
That wouldn't be suitable if you had, say, rows 2, 20 and 30 with the same ID.
To edit the information you should set up textboxes for each field/column.
These textboxes can get populated then the user selects (double clicks?) a row in the listbox.
You could also have a button on the form that will write the values in the textbox to the appropriate row/columns of the worksheet.
To facilitate that you should add another, hidden, column to the listbox to hold the row number.
So to be clear, the search is run and the listbox generates. There is no way to just click on the listbox value and begin editing it in the listbox itself and have that edit replace the position (cell, row) where it was pulled from?Originally Posted by Norie
Sorry that's not possible with a simple listbox I'm afraid.
That's fne. I maybe will just avoid using a listbox. What I tried to use previously was a series of textboxes but I was having trouble pulling the proper data. I will start a new thread with that code and link it here. Thanks!
But you could put a textbox just under the listbox and populate it with listbox.selecteditem for editing.
Solus
SelectedItem? Listboxes don't have such a property/method.
If you want to know which row in a listbox is selected you can use ListIndex.
Oh... well it does in VB.Net
I guess I forget the objects are that different.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks