multipe listboxes on 1 userform populated from 1 table list

DB73

Board Regular
Joined
Jun 7, 2022
Messages
102
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2010
  6. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
hey guys,

another question;

how do i populate 3 listboxes, from same table, but each with other columns, listboxes are on 1 userform
i mean for example;
listbox1 - show column ABC
listbox2 - show colomn DEF
listbox3 - show column GHI

columns could be mixed so in lsbx1 and lsbx3 could be the same column shown

my idea is that i want to select, for example, in lstbx3 a line, and then automaticly in lstbx2 and lstbx1 the same row would be selected.
then return the values or text , with cmdbttn, from selected, into textboxes so i can adjust them and update the table it came from(need a cmdbttn for that too i think, updatebttn)

i already made another userform that works,
-populate listbox
-select line in listbox
-add line items to textboxes
-adjust textboxes
-update the values from the textboxes in the table
but on this one theres only one listbox

how do i with 3
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Hello. Let's start from the beginning:

Why do you subdivide the information of a data range into three listboxes?... In principle it must be said that this does not make sense since it triples the work.
 
Upvote 0
Hello. Let's start from the beginning:

Why do you subdivide the information of a data range into three listboxes?... In principle it must be said that this does not make sense since it triples the work.
My table has 60 columns, as 5 parts
-date
-project
-workingtime
-travel from to
-money income

i dont wanna use all the columns in my listbox, i can hide the ones i dont use
i want for example about 10 columns per listbox as divided date/project, workingtime and 1 in travel
otherwise i have so mmuch columns in a row that my screen is to small.
yea i know i can scroll, but for a quick and good overview for me its much easyer with 3 listboxes
 
Upvote 0
The model I attached uses a single listObject to display 30 columns:

Sample_3.xlsm
\_____________________/​
Every time you change scenery:

a) The width of the columns displayed is changed.
b) Eventually, the width of the listBox is changed (if necessary).
c) Eventually, the width of the UserForm is changed (if necessary).

VBA Code:
Private Sub ComboBox1_Change()
Dim j%, Param(1 To 2)
Param(1) = ListBox1.TopIndex   'Top row of list
Param(2) = ListBox1.ListIndex  'Currently selected row

LObj.Range.Columns.AutoFit     'Perfect adjustment of the width of the columns.
ReDim mWidth(1 To LObj.ListColumns.Count) As Double

Select Case ComboBox1.Value
  Case "1"
    For j = 1 To 13
      mWidth(j) = 5 + Application.RoundUp(LObj.HeaderRowRange(j).Width, 0)
    Next
  Case "2"
    mWidth(1) = 5 + Application.RoundUp(LObj.HeaderRowRange(1).Width, 0)
    For j = 14 To 22
      mWidth(j) = 5 + Application.RoundUp(LObj.HeaderRowRange(j).Width, 0)
    Next
  Case "3"
    mWidth(1) = 5 + Application.RoundUp(LObj.HeaderRowRange(1).Width, 0)
    For j = 23 To 30
      mWidth(j) = 5 + Application.RoundUp(LObj.HeaderRowRange(j).Width, 0)
    Next
End Select

mWidth = Application.Transpose(Application.Transpose(mWidth))
With ListBox1
  .ColumnWidths = Join(mWidth, ";"): DoEvents
  .Width = Application.Max(.Width, 20 + Application.Sum(mWidth)): DoEvents
  .RowSource = LObj.Name
  .TopIndex = Param(1)
  .ListIndex = Param(2)
  Me.Width = Application.Max(Me.Width, 4 * .Left + .Width): DoEvents
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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