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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
No having the part number and part name in different columns is no problem.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

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
Back
Top