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

jmcconnell

New Member
Joined
Feb 2, 2019
Messages
35
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:

1607438598321.png

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

VBA Code:
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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

jmcconnell

New Member
Joined
Feb 2, 2019
Messages
35
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,246
Messages
5,595,044
Members
413,963
Latest member
teggl97

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