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!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
How are you populating the 2nd listbox?
 

jmcconnell

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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"
 

jmcconnell

New Member
Joined
Feb 2, 2019
Messages
35

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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"
 
Solution

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