I am trying to link one of my combobox with other comboboxes using the data that i have stored in a worksheet. So to be clear, what i want is when the user clicks the combobox that consists of the "devices" combobox it will automatically shows the details of that device through using other comboboxes. For example, when i click device A in device combobox it will automatically show details of device A using other comboboxes without having me to click it.
[Blocked Image: https://i.stack.imgur.com/BEElG.png]
[Blocked Image: https://i.stack.imgur.com/4WEtd.png]
However i am stuck at the part where i cannot find a way to select the corresponding column Bi , Ci and Di and link them to the other combobox2,combobox 3 and combobox 4. For instance, when device B is click, combobox2 ,combobox 3 and combobox 4 should auto select look up and 23 and 12 respectively. Anyways here is my code.
- Option Explicit
- Dim ws As Worksheet
- Dim i As Long
- Dim j As Long
- Dim z As Long
- Dim y As Long
- '~~> Load values from the worksheet into the combobox
- Private Sub UserForm_Initialize()
- '~~> Set this to the relevant sheet
- Set ws = Sheet1
- Dim lRow As Long
- With ws
- 'name of deviceID
- lRow = .Range("A" & .Rows.Count).End(xlUp).Row
- For i = 1 To lRow
- If Len(Trim(.Range("A" & i).Value)) <> 0 Then
- ComboBox1.AddItem .Range("A" & i).Value
- End If
- Next i
- For j = 1 To lRow
- If Len(Trim(.Range("B" & j).Value)) <> 0 Then
- ComboBox2.AddItem .Range("B" & j).Value
- End If
- Next j
- For z = 1 To lRow
- If Len(Trim(.Range("C" & j).Value)) <> 0 Then
- ComboBox3.AddItem .Range("C" & z).Value
- End If
- Next z
- For y = 1 To lRow
- If Len(Trim(.Range("D" & y).Value)) <> 0 Then
- ComboBox4.AddItem .Range("D" & y).Value
- End If
- Next y
- End With
- End Sub
- '~~> Add item to combobox from textbox
- Private Sub CommandButton1_Click()
- If Len(Trim(TextBox1.Text)) <> 0 Then
- ComboBox1.AddItem TextBox1.Text
- End If
- End Sub
- Private Sub CommandButton2_Click()
- If Len(Trim(TextBox2.Text)) <> 0 Then
- ComboBox2.AddItem TextBox2.Text
- End If
- End Sub
- Private Sub CommandButton3_Click()
- If Len(Trim(TextBox3.Text)) <> 0 Then
- ComboBox3.AddItem TextBox3.Text
- End If
- End Sub
- Private Sub CommandButton4_Click()
- If Len(Trim(TextBox4.Text)) <> 0 Then
- ComboBox4.AddItem TextBox4.Text
- End If
- End Sub
- '~~> Save to worksheet
- Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
- ws.Columns(1).ClearContents
- For i = 0 To ComboBox1.ListCount - 1
- ws.Cells(i + 1, 1).Value = ComboBox1.List(i)
- Next i
- For j = 0 To ComboBox2.ListCount - 1
- ws.Cells(j + 1, 2).Value = ComboBox2.List(j)
- Next j
- For z = 0 To ComboBox3.ListCount - 1
- ws.Cells(z + 1, 3).Value = ComboBox3.List(z)
- Next z
- For y = 0 To ComboBox4.ListCount - 1
- ws.Cells(y + 1, 4).Value = ComboBox4.List(y)
- Next y
- ThisWorkbook.Save
- DoEvents
- End Sub
- Private Sub selectitems()
- With ComboBox1
- lRow = .Range("A" & .Rows.Count).End(xlUp).Row
- For i = 1 to 1Row
- 'if one of the items in combobox1 is the same as one of the items in column A
- If ComboBox1.Item = Range(1Row).Value Then
- 'then automatically click the items in Combobox2 and combobox 3 that lies in the same rows as the items selected in combobox1
- Activate.Worksheets ("Sheet1")
- Dim box1details As String
- box1details =
- End Sub