Show datas on a userform that were stored via a listbox in another userform

[Angel1971]

Hi everybody in the community

I'm somehow learning Excel and VBA by myself and in order to start - as I'm a complete Newbe - I did choose a project in order to support my mates and myself in the daily work routine.

Basically it's a database that we use as information system.

With one userform (Userform1) we enter the datas into the database (Database). For that we can activate this userform via a button (button1). The database has 16 rows and approx. 100 line items.

Now, I have a second button to activate/show a second userform (Userform2). In this one, there are some textboxes with these I can search the information from the database. This userform has as well a ListBox (Listbox1). There the information from the main database is showed, but only 6 rows and, the 6th row does content the number of the row in the main database (but I have made the with to 0 in order its not displayed).
the listbox is showing based on the search criterias the selection of the fitting parameters. I can now doubleclick on one of the rows in the ListBox and the next action is that we switch to the maindatabase and the first cell in the corresponding row is marked/framed.

Now to my problem:
I would like to show all the cells of that selected row in a new userform (userform3) within textboxes (16 different textboxes). So here I need your help.

I did spend a couple of nights researching but not finding a solution to my problem; just a hint in the form of next

Private Sub UserForm3_Initialize ()

Dim intSpalte as Integer
Dim lngZeile as Long
lngZeile = ListBox1.List(ListBox1.ListIndex, 6)
For intSpalte = 0 to 15
With Worksheets("Database")
UserForm3.Controls ("Textbox1" & intSpalte +1) = Cells(lngZeile, intSpalte +1)
End With
Next intSpalte
UserForm3.Show

End Sub

Can you help me with the coding and as well, tell me where I have to insert the corresponding codes?

Thanks a lot in advance for your kind support and, let me know if you need more details from me.

cheers and take care
Angel

[AlphaFrog]

I do not understand what your question is.

However, do you want the 1 in Textbox1 ?

Please Login or Register  to view this content.

I think you just want this....

Please Login or Register  to view this content.

[Angel1971]

Dear AlphaFrog,

I guess it's either nor....I include here next screenshots:

UserForm1 (for Searching the valid)
Attachment 766381
Database with content
Attachment 766382
Userform3 (For showing the content of the selected Row, and content of each cell in the corresponding line/column)
Attachment 766383

Hope this gives you the idea I'm following what I want to do from where to where....

I thank you already for your kind support and help

Best regards
Angel

[torachan]

see big yellow banner - upload your workbook - answers are easier to give if working from actual file context.
Please note 'paper clip attachment does not work' and pictures are impossible to manipulate.

[Angel1971]

Kopie von Export_Instruction Monitor_LOA(5103).xlsm

Please find attached my project

Thanks for your support

[Angel1971]

Thank you for your information torachan....I have now posted the file in the posting

[Angel1971]

Dear AlphaFrog,
I have posted now my workfile, might this helps further to understand what I try to achieve....
Thanks in advance

[torachan]

@Angel1971, I have had a preliminary look at your workbook - there are a number of suggestions/queries.
Why not just use one form - this could be dealt with quite easily - if you really desire multiple forms look at using a multipage on a single userform.
This makes it far simpler passing common variables - set out your list box and database in the same sequence order - do not re-name your textboxes etc., it then makes it far easier to loop through them saving hundreds of lines of code on a large app.
Use of 'structured tables' also makes data handling easier as they self-maintain - no maintaining of 'name manager' needed.
I will have a further look over the next couple of days and put something together.

[Angel1971]

Hi Torachan

Thank you soo much for your great suggestions and inputs. They are very appreciated and do show me, that it's still a lot to learn and in special basic stuff!
So I keep open for any further suggestion and proposals you might have as well, If you can suggest some good lecture, tutorials or similars...

Again, thanks a lot and I keep curious about further inputs from your side.

All the best and cheers

[torachan]

As promised a demo of all on one form - just a few comments to help - all variables are stated at the head of the userform module with 'Option Explicit' as first line.
This makes the common variables available across all the subs within the userform module - (if you quote variables within the subs they are only available to that sub)
To aid following through the code I have attached a PDF of the code flow - my starting point is the actual database sheet (I have left it as your layout) - I would lay it out in order of primary & secondary keys, then following the logical order of data input - this then enables you to loop through the table headers, using them as your labels,
then laying the textboxes in the same order - you will note that the comboboxes are overlayed by some of the text boxes - this makes the data choice universal - either typed in or select by default - lastly to get started before adding data the 'CLEAR' must be pressed to ensure no data in the form and activate the 'ADD' button.
To search first select an 'Option Button' this then loads the 'ComboBox' with available choices from the database and populates the 'ListBox' - select from the ListBox choices then populates the data body for Viewing/Update or Delete.
Any queries post back on this site.

[Angel1971]

Dear Torachan
I'm speechles about the reply you gave me! What a great work and I can't thank you enough.
I will check it tomorrow and let you have my feedback and Impression.
Thank you very much for that great work
Best regards and my best thanks and wishes
Angel

[jjward101]

As promised a demo of all on one form - just a few comments to help - all variables are stated at the head of the userform module with 'Option Explicit' as first line.
This makes the common variables available across all the subs within the userform module - (if you quote variables within the subs they are only available to that sub)
To aid following through the code I have attached a PDF of the code flow - my starting point is the actual database sheet (I have left it as your layout) - I would lay it out in order of primary & secondary keys, then following the logical order of data input - this then enables you to loop through the table headers, using them as your labels,
then laying the textboxes in the same order - you will note that the comboboxes are overlayed by some of the text boxes - this makes the data choice universal - either typed in or select by default - lastly to get started before adding data the 'CLEAR' must be pressed to ensure no data in the form and activate the 'ADD' button.
To search first select an 'Option Button' this then loads the 'ComboBox' with available choices from the database and populates the 'ListBox' - select from the ListBox choices then populates the data body for Viewing/Update or Delete.
Any queries post back on this site.

@Torachan,

This is truly amazing work! I have one request myself, as this seems to be very easily able to be modified for what I have been trying to accomplish myself.
It seems that the userform can only be used to search or modify an existing record.....
Could you please add a button where I can add a new record directly through the userform? This would be amazing to me if you could do this! I am also a novice with VBA, and cannot figure out how to accomplish this.

[torachan]

press 'Clear' > Add your data to lower half of UserForm > Press 'Add' > Data transfers to table > table expands dynamically.