Multiple Listboxes showing ranges from active sheets

UnbrokenLogic

New Member
Joined
Mar 28, 2021
Messages
1
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hi Everyone, This is my First Ever Post:

I am creating a user form that will function similarly to a school management system.

One of the elements that are part of it is a Pupil Profile, which shows all information relating to the specific child.

Currently, each pupil has its own sheet. When searching for the child, the sheet matching the name of the child will be set as the active sheet.

This sheet contains sets of columns that store data about Parents meetings, rewards and sanctions, concerns and meetings with key staff members.

1616971774703.png


I have a multipage setup with 4 tabs, each tab has a list box that I want to show the data from the set of columns on the active sheet.

I don't know a great deal about VBA, so I followed some tutorials and managed to make one of the sets of columns work perfectly. But to be honest, I don't understand how the code works, and I can't make it work for the other rows:

-----------------------------------------------------------------------------------------------------

Private Sub cmdSearch_Click()
Dim X As Long
Dim Y As Long
Dim i As Long, G As Integer
Dim C As Integer, M As Integer

For G = 2 To Sheets.Count
If Sheets(G).Name = txtSearch Then
For i = 2 To Sheets(G).Range("A2000").End(xlUp).Row


Me.ListBox1.AddItem
For C = 0 To 6
Me.ListBox1.List(ListBox1.ListCount - 1, C) = Sheets(G).Cells(i, C + 1)
Next C
Next i
End If
Next G

Private Sub AddParentMeeting_Click()
TgtSheet = txtSearch.Value
If TgtSheet = "" Then
Exit Sub
End If
Worksheets(TgtSheet).Activate
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Cells(LastRow + 1, 1).Value = PM1.Value
ActiveSheet.Cells(LastRow + 1, 2).Value = PM2.Value
ActiveSheet.Cells(LastRow + 1, 3).Value = PM3.Value
ActiveSheet.Cells(LastRow + 1, 4).Value = PM4.Value
ActiveSheet.Cells(LastRow + 1, 5).Value = PM5.Value
ActiveSheet.Cells(LastRow + 1, 6).Value = PM6.Value
End Sub

-----------------------------------------------------------------------------------------------------

Basically, my two key issues are:

1) How do I populate list boxes from a range of data on the active sheet so that it changes with each new profile search? E.g

ListBox1 shows A2:F2000 on active sheet
ListBox2 shows H2:K2000 on active sheet
ListBox3 shows M2:Q2000 on active sheet
ListBox4 shows S2:X2000 on active sheet

2) How do I add information to just those columns without it adding the information to the next fully clear row? e.g)

I don't want this to happen:
1616972433735.png


I want it to look like this:
1616972486149.png


Sorry for the lengthy post, and any questions or help in the right direction would be really appreciated
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

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
Top