Pull data from table into userform

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
310
Office Version
  1. 2019
Platform
  1. Windows
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

<tbody>
</tbody>

Thanks for any help.

Regards
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
Thank you Garry, I'll try your code tonight and let you know any issue.

Many thanks again.

Best regards.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top