+ Reply to Thread
Results 1 to 7 of 7

Putting a table into a userform

  1. #1
    Registered User
    Join Date
    09-08-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 SP3
    Posts
    45

    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
    Attached Files Attached Files
    Last edited by therealjag; 02-18-2010 at 01:00 PM.

  2. #2
    Registered User
    Join Date
    09-08-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 SP3
    Posts
    45

    Re: Putting a table into a userform

    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!!!!

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,205

    Re: Putting a table into a userform

    Quote Originally Posted by therealjag View Post
    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
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Putting a table into a userform

    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
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  5. #5
    Registered User
    Join Date
    09-08-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 SP3
    Posts
    45

    Wink Re: Putting a table into a userform

    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:
    Please Login or Register  to view this content.
    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.

    Please Login or Register  to view this content.
    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:

    Please Login or Register  to view this content.
    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
    Last edited by therealjag; 02-19-2010 at 05:23 AM. Reason: Mistake in post

  6. #6
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Putting a table into a userform

    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.

  7. #7
    Registered User
    Join Date
    05-01-2012
    Location
    MONK, dedlio
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Putting a table into a userform

    Hi All,

    As you explained that we can show a table in a userform. I am have become fan of you guys and this forum.

    I have implemented this method to my project. Now I need to create a userform from that I want user to drag & drop fields of the data & make pivot reports. Like we create pivot tables.

    Can I make userform like pivot table fields so that one can make summary of data without creating a pivot table in worksheet.

    Appreciate your support.

    Thanks
    Karan

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1