How to automatically select and show other comboboxes values by clicking another combobox

unknown9345 · Jan 21st 2020

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.

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

Display More

royUK · Jan 21st 2020

An example workbook would be useful

unknown9345 · Jan 22nd 2020

Hi royUK,

Maybe my previous code is abit too long . But to break it down,

After saving my combobox values into the worksheet, i am trying to link my combobox to the worksheet. For instance, when the user selects one of the combobox value eg device a that is stored in the worksheet,how to select the corresponding values for the row that matches with one of the values in column A and link them with other comboboxes? Below is my code.

[Blocked Image: https://i.stack.imgur.com/TWNfC.png]

Code

Private Sub selectitems()
With ComboBox1
Activate.Worksheets ("Newdevice")
Dim Y As Long
Y = Worksheets("Newdevice").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To Y
If Worksheets("Newdevice").Cells(i, 1).Value = ComboBox1.Value Then
'if any one of the value in combobox1 is = to columnA of worksheet("newdevice")
'stuck here
'select the columns that has the same row as the one selected in columnA
ComboBox1.Value=
ComboBox2.Value=
ComboBox3.Value=
ComboBox4.Value=
End If
Next i
End Sub

Display More

unknown9345 · Jan 22nd 2020

royUK this is the link to the example workbook.

https://i.stack.imgur.com/TWNfC.png]

royUK · Jan 22nd 2020

Please attach on the Forum

How to automatically select and show other comboboxes values by clicking another combobox

Share

Tags