How do I get combobox to get a cell number then link it to a selection from a checkbox in Excel?

Ayugma

New Member
Joined
Nov 20, 2020
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
I have no idea how to really implement this. So I have 5 checkboxes, all of which are parts to a machine. So, a user will be able to select as many parts as they want. All the parts have part numbers in another worksheet.

For example, if three are selected, I want a combobox to list the part numbers of those three parts. I'm not sure how to implement this in VBA and some guidance would be greatly appreciated. Thanks!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,247
Office Version
  1. 2013
Platform
  1. Windows
I have no idea how to really implement this. So I have 5 checkboxes, all of which are parts to a machine. So, a user will be able to select as many parts as they want. All the parts have part numbers in another worksheet.

For example, if three are selected, I want a combobox to list the part numbers of those three parts. I'm not sure how to implement this in VBA and some guidance would be greatly appreciated. Thanks!
I see your new to the forum. Specifics are always important.
You said part numbers are in another sheet. We need to know the name of the other sheet.
And what column are the part names in and what column are the Part numbers in.
And what is the name of the ComboBox and what sheet is the Combobox on.
 

Ayugma

New Member
Joined
Nov 20, 2020
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
Sorry for not including the details!

So the name of the 1st sheet where everything is configured is known as "Main Configurator" and the 2nd sheet containing data is known as "Table of Part Numbers".

The column in which the part numbers are from B39-B45, while the corresponding part names are from A39-A45

The 5 checkboxes and the 1 combo boxes are known as:
CheckBox1, CheckBox2, CheckBox3, CheckBox4, CheckBox5 and ComboBox1

The 5 checkboxes and the single combobox is located in the "Main Configurator" sheet.

Thank you for your assistance
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,247
Office Version
  1. 2013
Platform
  1. Windows
So we will be searching for the Caption of the CheckBox correct.
So if Caption of Checkbox1 is "Alpha" we be searching for part named "Alpha"
And the script will be run when you click on the CheckBox correct?

And these are activex Checkboxes correct.
 

Ayugma

New Member
Joined
Nov 20, 2020
Messages
39
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Yep, that's what my intention was!
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,247
Office Version
  1. 2013
Platform
  1. Windows
I have another question.
How do you plan to clear the Combobox?
And if you select the checkbox with caption "546789"
And the Combobox now shows "Alpha" as the Part Name
How will you remember which part name goes with each part number
Or do you want to clear the Combobox each time. So only 1 part name will ever be in the ComboBox
 

Ayugma

New Member
Joined
Nov 20, 2020
Messages
39
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I'm okay with the combobox never clearing as long as it updates as different checkboxes are clicked. It won't be hard to remember the part number and its associated name, as I work with them quite frequently.

I guess the difficulty would be making reference to two different cells, so I would make it easier by putting the part name and number in one cell separated by a comma. An example of what I am looking for is shown below:
sample_checkbox.png

sample_combobox.png
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,247
Office Version
  1. 2013
Platform
  1. Windows
No having the part number and part name in different columns is no problem.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,247
Office Version
  1. 2013
Platform
  1. Windows
I wrote script for two of your checkboxes.
You can write other ones.
Be sure and modify.
SearchString = CheckBox2.Caption
On each script.
And here is a script to clear the Combobox if needed

Rich (BB code):
Private Sub CheckBox1_Click()
'Modified  11/20/2020  1:42:23 PM  EST
Dim SearchString As String
Dim SearchRange As Range
SearchString = CheckBox1.Caption
Dim ans As String
Set SearchRange = Sheets("Table of Part Numbers").Range("B39:B45").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
ans = SearchRange.Offset(0, -1).Value
ComboBox1.AddItem ans
End Sub
Private Sub CheckBox2_Click()
'Modified  11/20/2020  1:42:23 PM  EST
Dim SearchString As String
Dim SearchRange As Range
SearchString = CheckBox2.Caption
Dim ans As String
Set SearchRange = Sheets("Table of Part Numbers").Range("B39:B45").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
ans = SearchRange.Offset(0, -1).Value
ComboBox1.AddItem ans
End Sub
Private Sub CommandButton1_Click()
'Modified  11/20/2020  1:42:23 PM  EST
ComboBox1.Clear
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,785
Messages
5,574,255
Members
412,581
Latest member
Rami100
Top