VBA form - Add data to a row determined by listbox choices

jmcconnell

New Member
Joined
Feb 2, 2019
Messages
35
I've got a userform with 2 listboxes and some text boxes.

The user selects an item from the first listbox which then populates choices in a second listbox:

1608218990945.png


So for example, a user selects Dyffryn Brodyn which would be in listbox 1. Listbox 2 would then show 9,6,10 & 5. If the user then selects number 10 from this listbox then I want somehow select that row to be able add data to the corresponding cell in column C using a textbox on the form. I have the listboxes populating ok.

This is what I have so far:

VBA Code:
Private Sub Submit_Click()
Dim RTSWind As Workbook
Dim RTSTracker As Worksheet
Dim matchFormula As String, Lb1 As String, Lb2 As String


Set RTSWind = Workbooks.Open("Path to workbook goes here /RTS testing.xlsm")
Set RTSTracker = RTSWind.Sheets("RTS Tracker")
  
    Lb1 = OpenSites.Text
    Lb2 = OpenTurbines.Text
    matchFormula = "match(" & Chr(34) & Lb1 & Lb2 & Chr(34) & ",A:A&B:B,0)"


End Sub

How do I then use this to be able to add to the specific row (determined by the 2 listbox choices)?

Thank you for your help!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
How are you populating the 2nd listbox?
 
Upvote 0
Hi Norrie,

Listbox1 is called 'OpenSites' and 2nd listbox is called 'openTurbines'

OpenTurbines is populated using the code below (I couldn't use a named range as the form is on a different workbook to the spreadsheet containing the data):

VBA Code:
Private Sub OpenSites_Click()
Dim RTSWind As Workbook
Dim RTSTracker As Worksheet
Set RTSWind = Workbooks.Open("https://reshive.sharepoint.com/sites/ControlCentre/shared documents/5 core services/monitoring & escalation/control centre assistant/RTS testing.xlsm")
Set RTSTracker = RTSWind.Sheets("RTS Tracker")
lastrow = Cells(Rows.Count, "A").End(xlUp).Row


Me.OpenTurbines.Clear
curVal = Me.OpenSites.Value
For x = 2 To lastrow
    If RTSTracker.Cells(x, "a") = curVal Then
    Me.OpenTurbines.AddItem RTSTracker.Cells(x, "b")
    End If
Next x


End Sub

Thank you for having a look at this for me!
 
Upvote 0
How about this?

Add another, hidden, column to the 2nd listbox, use it to store the row number and have that column as the bound column of the listbox.

Setup:
VBA Code:
With Me.OpenTurbine
    .BoundColumn = 2
    .ColumnCount = 2
    .ColumnWidths = "50px;0
End With

Populate 2nd listbox:
VBA Code:
    If RTSTracker.Cells(x, "a") = curVal Then
        With Me.OpenTurbines
            .AddItem RTSTracker.Cells(x, "b")
            List(.ListCount-1, 1) = x
        End With
    End If

Submit:
VBA Code:
lngRow = Me.OpenTurbines.Value
RTSTracker.Range("C" & lngRow).Value = "Fault Description"
 
Upvote 0
Thanks for the help Norie. I'll have a go with your code and let you know how I get on. Thank you very much!
 
Upvote 0
No problem, note there was a typo in the code I posted but I fixed it below.
Setup:
VBA Code:
With Me.OpenTurbine
    .BoundColumn = 2
    .ColumnCount = 2
    .ColumnWidths = "50px;0
End With

Populate 2nd listbox:
VBA Code:
    If RTSTracker.Cells(x, "a") = curVal Then
        With Me.OpenTurbines
            .AddItem RTSTracker.Cells(x, "b")
            .List(.ListCount-1, 1) = x
        End With
    End If

Submit:
VBA Code:
lngRow = Me.OpenTurbines.Value
RTSTracker.Range("C" & lngRow).Value = "Fault Description"
 
Upvote 0
Solution

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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