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

[jmcconnell]

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:

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:

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!

 

[Norie]

How are you populating the 2nd listbox?

 

[jmcconnell]

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):

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!

 

[Norie]

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:

With Me.OpenTurbine
    .BoundColumn = 2
    .ColumnCount = 2
    .ColumnWidths = "50px;0
End With

Populate 2nd listbox:

    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:

lngRow = Me.OpenTurbines.Value
RTSTracker.Range("C" & lngRow).Value = "Fault Description"

 

[jmcconnell]

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!

 

[Norie]

No problem, note there was a typo in the code I posted but I fixed it below.
Setup:

With Me.OpenTurbine
    .BoundColumn = 2
    .ColumnCount = 2
    .ColumnWidths = "50px;0
End With

Populate 2nd listbox:

    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:

lngRow = Me.OpenTurbines.Value
RTSTracker.Range("C" & lngRow).Value = "Fault Description"

 

[jmcconnell]

Thanks Norie. That works perfectly!