Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Pull data from table into userform

This is a discussion on Pull data from table into userform within the Excel Questions forums, part of the Question Forums category; Hello to all in forum, I have a database in sheet1 with 5 columns (A-E). I would like to have ...

  1. #1
    Board Regular
    Join Date
    Oct 2011
    Posts
    176

    Default Pull data from table into userform

    Hello to all in forum,

    I have a database in sheet1 with 5 columns (A-E). I would like to have a VBA userform that allows me to select books by genre shows me within the userform all rows for that genre and columns A,B, D and E.

    Example: Having a list of genres in the userform, select "Action", and in the userform should display 3 rows
    I really new with VBA userforms. May somebody help me sh0wing me how to pull the data based on genre and show the
    result table inside the userform.

    Genre Title Year Author Read
    Drama Drama_1 1980 John T. 130
    Romance Romance_1 1983 Mary D. 215
    Action Action_1 1986 Jane M. 300
    Romance Romance_2 1989 Mary D. 120
    Children Children_1 1992 Paul S. 381
    Action Action_2 1995 John T. 87
    Fantasy Fantasy_1 1998 Kevin W. 95
    Children Children_2 2001 Mary D. 103
    Action Action_3 2004 Jane M. 204
    Horror Horror_1 2007 Kevin W. 167

    Thanks for any help.

    Regards

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    1,599

    Default Re: Pull data from table into userform

    Hi,
    Have a look here:Create User Forms in Microsoft Excel
    & here: http://www.fontstuff.com/ebooks/free/fsUserForms.pdf

    These are just sample guides in building userforms. They are not working solutions for your need but should give you some idea what is involved.
    You can download for free the file which you can attempt modify to do what you want. If still need further assistance, post back with code you are working with.

    Hope helpful

    Dave

  3. #3
    Board Regular
    Join Date
    Oct 2011
    Posts
    176

    Default Re: Pull data from table into userform

    Hello dmt32,

    Thank you for your answer and share those links, it will help me to begin the idea of what I want to do.

    I see that the example userform you shared me does one part of what I need (to introduce data), but I want
    to pull the data from database too, and be able to visualize it inside the userform based on genre.

    I was looking userform options, but I don't know how to insert a grid area to show the data
    from the database. Which control is? a text box or another control?

    May you help me with a basic code to pull the data in "Course Bookings" sheet and visualize it within an
    userform using the sample file in the page you shared (http://www.fontstuff.com/downloads/VBA04-UserForms.xls). In this case
    could have the option to pull the data based on Department.

    Many thanks in advance.
    Last edited by Fractalis; Apr 12th, 2013 at 02:47 PM.

  4. #4
    New Member
    Join Date
    Apr 2013
    Location
    San Fernando Valley
    Posts
    43

    Default Re: Pull data from table into userform

    I'm curious if autofilter would work for you.

  5. #5
    Board Regular
    Join Date
    Oct 2011
    Posts
    176

    Default Re: Pull data from table into userform

    Hello dandungan,

    The issue is that I want to visualize the data based on one header (in this case Department) because there are several tables
    with common headers in different sheets, containing related info, but from all common headers in all sheets, I want to show
    only some headers within userform and based on one column (Department).

    Thanks in advance for any help.

  6. #6
    Board Regular
    Join Date
    Apr 2013
    Posts
    55

    Default Re: Pull data from table into userform

    Unfortunately, neither VBA nor MSO Forms provide an intrinsic 'grid' control. The easiest way (IMO) to work with (and/or display) columnar data in a userform is to use the ListBox control and specify the number of columns. You could use a ComboBox or another ListBox control for listing the genres to display in the multi-column ListBox as the genre is selected.

    I suggest dumping the entire range into an array and loading the controls from the array elements. This will be orders of magnitude faster and more efficient than reading the range cell by cell!

  7. #7
    Board Regular
    Join Date
    Oct 2011
    Posts
    176

    Default Re: Pull data from table into userform

    Hello Garry2Rs,

    Thank you for your answer.

    Actually I would like to:
    1- Pull data from differents tables in different sheets based on one header
    2- Having open the userform, let say, for "Sales" Department, be able to add new values, erase or change values for
    Sales department and those changes be updated automatically in the original databases that are in different sheets.

    I need advice if my idea to use a userform for these 2 requeriments is the best option to do this.

    If I load an array with the info in databases, I'll be able to add/erase, change values in both directions?

    Thank you for help so far.

  8. #8
    Board Regular
    Join Date
    Apr 2013
    Posts
    55

    Default Re: Pull data from table into userform

    Lets start with an example that uses your sample data from your original post. Perhaps it will help you to figure out what to do to make it work for scattered data over multiple sheets!

    This example assumes your data is in A1:E11 on Sheets("Sheet1")...

    On a userform, put 1 label and 3 listbox controls. Configure things as follows:

    Userform1
    Set Height to 180, Width to 210

    Label1
    Set Left to 6, Top to 6, Height to 12, Width to 72

    ListBox1
    Set Left to 6, Top to 18, Height to 96, Width to 72

    ListBox2
    Set Left to 84, Top to 6, Height to 12, Width to 210

    ListBox3
    Set Left to 84, Top to 18, Height to 96, Width to 210

    Copy/paste the following code into the code window behing the userform.

    Code:
    Option Explicit
    
    Dim vDataIn, vaGenreItems(), sGenreList$, n&
    Const sColWidths$ = "120 pt;60 pt;20 pt"
    
    Private Sub ListBox1_Click()
      Get_GenreItems Me.ListBox1.Value
    End Sub
    
    Private Sub Get_GenreItems(sGenre$)
      Dim lGenres&, k&
      Me.ListBox3.Clear
      lGenres = WorksheetFunction.CountA("A:A", sGenre)
      ReDim vaGenreItems(1 To lGenres, 1 To 3)
      For n = LBound(vDataIn) To UBound(vDataIn)
        If vDataIn(n, 1) = sGenre Then
          k = k + 1
          vaGenreItems(k, 1) = vDataIn(n, 2)
          vaGenreItems(k, 2) = vDataIn(n, 4)
          vaGenreItems(k, 3) = vDataIn(n, 5)
        End If
        If k = lGenres Then Exit For
      Next 'n
      Me.ListBox3.List() = vaGenreItems
    End Sub
    
    Private Sub UserForm_Initialize()
      
      'Load the data to work with
      vDataIn = Sheets("Sheet1").Cells(1).CurrentRegion
      
      For n = LBound(vDataIn) + 1 To UBound(vDataIn)
        'Get a unique list of genres
        If Not InStr(1, sGenreList, vDataIn(n, 1)) > 0 Then _
          sGenreList = sGenreList & "," & vDataIn(n, 1)
      Next 'n
      
      Me.Height = 180: Me.Width = 300
      With Me.Label1
        .Caption = "Genres": .Font.Name = "Arial": .Font.Bold = True: .Font.Size = 8
      End With
      
      With Me.ListBox1
        .Font.Name = "Arial": .Font.Size = 8
        'Load the genres list
        .List = Split(Mid$(sGenreList, 2), ",")
      End With
      
      With Me.ListBox2
        .ColumnCount = 3: .ColumnWidths = sColWidths
        .BackColor = &H80000004: .SpecialEffect = fmSpecialEffectFlat
        .Font.Name = "Arial": .Font.Bold = True: .Font.Size = 8
        'Set the column headers
        .Column() = Array(vDataIn(1, 2), vDataIn(1, 4), vDataIn(1, 5))
      End With
      
      With Me.ListBox3
        .ColumnCount = 3: .ColumnWidths = sColWidths: .Font.Name = "Arial": .Font.Size = 8
      End With
    End Sub
    Run the userform and select genres
    Last edited by Garry2Rs; Apr 12th, 2013 at 06:33 PM.

  9. #9
    Board Regular
    Join Date
    Oct 2011
    Posts
    176

    Default Re: Pull data from table into userform

    Thank you Garry, I'll try your code tonight and let you know any issue.

    Many thanks again.

    Best regards.

  10. #10
    Board Regular
    Join Date
    Oct 2011
    Posts
    176

    Default Re: Pull data from table into userform

    Hello Garry,

    I've tested your code and tried to understand it, and yes, is exactly on the road of the idea I want to implement!.

    Now I want to extend it to be able to add/erase or change the records listed in Listbox3 and that changes could be updated
    automatically in Sheet1. For example.

    If I select genre "Children" and I want to ammend an author name, be able to do it and the author name changes too
    in the corresponding row/column of sheet1.

    If I select genre "Children", be able to select one or more rows within Listbox3 and once selected, erase them in the userform
    and automatically should be erased in sheet1 too.

    If I want to add one or more titles, authors, etc, be able to do it in userform and that the sheet1 updates too (automatically or click a button).

    Thanks for your great help.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com