Thread: Putting a table into a userform

Hey there,

I have been tasked with introducing userforms into an excel sheet and tbh I'm quite amazed that excel has this capability of adding userforms to excel sheets.

Anyway, I have 2 columns of data in an excel sheet and I wish to add this to a userform so that the userform displays the 2 columns beside each other with headings, like a table. The user should then be able to select a particular row and insert it into the specified cell.

I would also like the user to select a row on the table and then be able to bring up another table depending on the row selected...basically so that the user can draw deeper into the information that they require.

I have an example excel sheet where I have 2 sheets. One sheet is the user entry sheet called User Entry Screen. the next sheet is the tables sheet where my tables are held. Once the user selects the cell shown in the example sheet, it should then bring up the user form. the user then, depending on which item clicked, then gets shown the next window with a table and info on it. then user should then be able to select an item and the cells on the user entry screen would then get populated.

Personally I think this is a really tricky challenge and any help with doing this would be extremely appreciated.

I'll post up further comments as I am trying to work my way through it!

Thanks,

Jag

Originally Posted by therealjag

Hey guys I dont think anyone was going to reply but for the record I managed to do it anyway.

Just message me if you want the solution on how I did it, can't be bothered explaining!

God Bless EXCEL!!!!

Very helpful to others. In future hopefully others won't be bothered to answer your questions

Very helpful to others. In future hopefully others won't be bothered to answer your questions

Ditto the sentiment.

Future solutions for therealjag should be along the lines of . . .

Here's your answer:
Sorry we can't be bothered to post the details or an explanation

Lol ok sorry guys I get the idea...right basically the steps I followed to get my solution was probably the simplest route I could find. The steps were:

1) Create the list of items I needed and then concatenate the coloumns if there was more than one list using the =$[cell reference]&" "&$[cell reference] concatenator. That way the "table" that I wanted to put into the listform can be displayed easily.
2) Then name the concatenated list under Insert -> Name -> Define
3) Then for each list that I had I created a userform and then a listform inside each userform and set the rowsource property to "=[defined name]" for the listform to populate the list.
4) I then placed the following code inside each userform so that it could link to the next userform as I had multiple useforms to display:

Code:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Sheet2.[C5].Value = ListBox1.Value
    UserForm1.Hide
End Sub

5) Then I placed the following code inside the user entry input screen sheet so that whenever the user clicked the cell C5 the userform would display.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.Address(False, False) = "C5" Then
    UserForm1.Show
   End If
   
   If Cells(5, 3).Value = "[value of field]" Then
      UserForm1.Hide
      UserForm2.Show

6)Then in order for the user to enter the information from the list into the cells C5 and C6, the final userform had the following code. It contained 2 functions, one to enter the text into cells C5 and C6 to hide the data and a split_text function to help split the text back up in the listform, using 3 spaces as the seperator:

Code:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Sheet2.[C5].Value = split_text_optimised(ListBox1.Value, 1)
    Sheet2.[C6].Value = split_text_optimised(ListBox1.Value, 2)
    UserForm3.Hide
End Sub

Function split_text_optimised(text As String, returnValue As Integer)
Dim x, y As String      'x holds the string that is split by the comma.
                        'y holds the string that contains the address of where to paste the seperated values
                        
Dim i As Long
Dim phrase As Variant

phrase = Array(1, 2, 3)
returnValue = returnValue - 1

    x = Split(text, "   ")
    'split the cell value via the comma
    If UBound(x) < 1 Then Exit Function
    'if the cell is empty then just exit the sub
    For i = 0 To UBound(x)
        phrase(i) = x(i)
    Next i
split_text_optimised = phrase(returnValue)
End Function

And that was it. I hope that all makes sense. Due to the nature of the spreadsheet containing sensitive data I am unable to attach it to this post to show everyone what I did, but i hope the steps above showed what I did. I'm also very hungover at the moment so if some of it (or all of it), doesnt make sense then let me know. I'm also very appreciative of all the help received so far in this excel forum.

Cheers,

Jag

Thanks for having a decent attitude about the friendly jabs about posting the solution and thanks for posting it. It's what this Forum is about.