Change cell contents depending on selection from 2 list boxes on a form

[jmcconnell]

I have a form on one spreadsheet but the form uses data from another spreadsheet.
The form has 2 listboxes - (OpenSites and OpenTurbines), a text box (Textbox1), a radio button (Closed) and has a button (Submit)

The listboxes are populated from a sheet called "RTS Tracker" using .additem. Sample of the spreadsheet is below:

The Opensites listbox is populated from Column 1 and duplicate entries are removed. Selecting an option from this listbox shows the corresponding numbers in OpenTurbines listbox. (eg, choosing Dyffryn Brodyn from 'OpenSites' would show 9, 6 & 10 in the second listbox 'Open Turbines'.

I'm struggling to achieve 2 things. I want to be able to make a selection from the 2 listboxes then add some text to Textbox1 so when I press 'Submit' the text is added to the corresponding row on the spreadsheet. The second thing is I want to be able to do is select the radio button and when I press submit, it would update the spreadsheet status to closed and then remove corresponding option from the 2nd listbox.

Here is my code so far - This only populates the list boxes and filters out duplicates in the first listbox.

Code for OpenSites listbox
Private Sub UserForm_activate()
Dim Lastrow As Long
Dim RTSWind As Workbook
Dim RTSTracker As Worksheet

Dim Test As New Collection
Dim rng As Variant, temp() As Variant
Dim Value As Variant, I As Single


Set RTSWind = Workbooks.Open("https://Path to the file/RTS testing.xlsm")
Set RTSTracker = RTSWind.Sheets("RTS Tracker")

'Identify range

rng = RTSTracker.Range("A3:A" & _
Sheets("RTS Tracker").Columns("A").Find("*", _
SearchOrder:=xlRows, SearchDirection:=xlPrevious, _
LookIn:=xlValues).Row)


'Filter unique values
On Error Resume Next
For Each Value In rng
If Len(Value) > 0 Then
Test.Add Value, CStr(Value)
End If
Next Value
On Error GoTo 0

ReDim temp(1 To Test.Count)
For I = 1 To Test.Count
temp(I) = Test(I)
Next I

'SelectionSort temp
For Each Value In temp
OpenSites.AddItem Value
Next Value

OpenSites.ListIndex = 0

Set Test = Nothing
End Sub

Code for OpenTurbines listbox

Dim RTSWind As Workbook
Dim RTSTracker As Worksheet
Set RTSWind = Workbooks.Open("https://Path to the file/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

Any help on this would be much appreciated.

Thank you,
James.

 

[jmcconnell]

Can anyone assist with this?

Basically im looking to select an option from one list box and then select a corresponding option from the next list box and select that row so I can read any data in that row.

Thank you.