VBA code to populate userform with table data

Question

I have hours into researching and experimenting but I cannot seem to get this right and I don't believe it is very complex. I have a worksheet named "Lists" with several tables of data. One table is called RollingAssetTable and includes 39 columns and 21 rows including header. Each row in the table contains data on a particular vehicle. Each vehicle has an ID number which is column 1 in the table. In the same book I have a sheet titled "Fleet" and on this sheet I want the user to be able to select a value from column 1 of the table via a combobox ("Combobox2"). The objective is to select a vehicle by its unit ID number and press a command button to fetch certain text from columns (not all columns) from the table for the chosen vehicle. I managed to get to the point where I select a unit number from the combobox and it works fine displaying some data on on the spread sheet. (Its just the first few columns on the table to allow the user to confirm they have the vehicle they want). I did this without VBA by setting Combobox ListFillRange to a named table range (took me a while to figure out you cannot reference a table name directly).

Once the user selects and confirms the vehicle they want, they select one of 6 command buttons to get further information (from the same table). Since this information is several columns I would like it to open a userform for a nicer user experience rather than try to list it all on the spreadsheet. I assume I will need a userform for each of the categories of data I want each command button to call. Some of the forms would be read only (such as viewing specifications) and some are input forms, such as enter new service record. I have created command buttons for, "New Svc Record", "View Svc Records", "Service Data", "Specifications" "Images", and "Documents". I haven't yet created any tables for data input. One thing at a time I guess...

My stumbling point is:

1. I need the usereform to: extract and display the correct record based on the vehicle ID chosen by the user... shouldn't be complex but I cannot get it right
2. Code for a userform to send data to a table (Ive done this elsewhere in the book, but only for data entry, not data viewing)

This shouldn't be complicated but I cannot get it.... Maybe I am I going about this all wrong?

I am very new to VBA although I have learned a lot by trial and error. I have hours into scouring the web trying to adapt other's code for my use with no luck. What complicates things for me is that there are so many ways to accomplish something. Another thing I struggle with is WHERE to put code because many experts assume a certain level of knowledge and just say ... "enter xxx.." or "you need to do this or that" but don't say "how" to do this or that. I have very limited knowledge but am willing to do the work to learn. So if anyone is gracious enough to help me by providing code please include all the specifics that can... including exactly where to put the code. Thanks so much!

Answer 1

Dear respected,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

As per your description, it seems that, you want using VBA code to populate userform with table data. We would love to help you on your query about VBA code, however, our team focuses on general query, for example, installation and activation issue of Excel application. The situation you mentioned is related to VBA code, according to the official article Office VBA support and feedback | Microsoft Learn, we suggest you please post a question to Office Development - Microsoft Q&A for expert help.

At the same time, we will also keep this thread open, so other Community members and Experts can also share their suggestions and inputs.

Appreciate your patience and understanding and thank you for your time and cooperation.

Sincerely,

Mia

Answer 2

Hi,

data source table on data3 worksheet

pic

.

split data range columns in 2 groups:

group-1 visible columns 1-2-3-4-5

group-2 visible columns 1-2-6-7-8-9

pic

.

.

'=================

UserForm3

'

group-1

.

group-2

.

.====================

Step1

right click on Userform

and paste in

[Update-2.... 08-02-2024]

Option Explicit 'START

Dim ws As Worksheet

Dim r As Range

Dim nCol, x

Dim lo As Object

Dim vArr() 'new-new

'

Private Sub UserForm_Initialize()

Set ws = Sheets("data3") 'change source sheet name

Set lo = ws.ListObjects(1)

nCol = lo.ListColumns.Count

Me.ListBox1.ColumnCount = nCol

Me.ComboBox1.Font.Size = 15

Me.ComboBox2.Font.Size = 15

Me.ListBox1.Font.Size = 13

Me.Label1.Font.Size = 15

Me.Label2.Font.Size = 15

'

Me.ComboBox1.List = lo.ListColumns(1).DataBodyRange.Value 'new-new

'

vArr = Array("group-1", "group-2") 'new-new

Me.ComboBox2.List = vArr() 'new-new

'

Private Sub ComboBox1_Change()

Me.ComboBox2.Value = ""

Me.ListBox1.Clear

End Sub

'

Private Sub ComboBox2_Change()

If Me.ComboBox1.ListIndex = -1 Then 'new

Me.ComboBox1.SetFocus 'new

GoTo qaz 'new

End If 'new

Application.Run "z99_FilterData"

Select Case Me.ComboBox2.Value

Case vArr(0)'new-new

With Me.ListBox1

.ColumnWidths = "55;60;55;55;55;0;0;0;0" 'visible columns 1-2-3-4-5

End With

Case vArr(1) 'new-new

With Me.ListBox1

.ColumnWidths = "55;60;0;0;0;55;55;55;55" 'visible columns 1-2-6-7-8-9

End With

End Select

Me.ListBox1.Clear

With Me.ListBox1

For Each r In lo.ListColumns(1).Range

.AddItem r.Value

For x = 1 To nCol - 1

.List(.ListCount - 1, x) = r.Offset(, x).Value

Next x

Next r

End With

lo.Range.AutoFilter Field:=1'new

qaz: 'new

End Sub

'

Private Sub CommandButton1_Click()

lo.Range.AutoFilter Field:=1

Unload Me

End Sub 'END

'===================

Step2

in a regular Modue paste in the below 2 macros

Sub z99_FilterData()

Set ws = Sheets("data3") 'change sheet name as needed

Set lo = ws.ListObjects(1)

lo.ShowAutoFilterDropDown = False

lo.ShowAutoFilterDropDown = True

lo.Range.AutoFilter Field:=1, Criteria1:=UserForm3.ComboBox1.Value 'change userform name

End Sub

'add a button and assign the below macro:

Sub z99_Load_UserForm3()

UserForm3.Show

End Sub

'====================