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

[Ayugma]

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!

 

[Ayugma]

I went back and read your original post again and after seeing your image I did the script incorrectly.
I thought we were searching for part numbers.
I will Rewrite script

Sorry again. I am very new to this VBA stuff so sorry if I explained it incorrectly.

 

[My Aswer Is This]

Try this:

Private Sub CheckBox1_Click()
'Modified  11/20/2020  2:49:24 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("A39:A45").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  2:49:24 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("A39:A45").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

 

[My Aswer Is This]

But again I only wrote script for two of your CheckBoxes
You should be able to do others.
Just Change CheckBox1.Caption to CheckBox3.Caption and so on

And you said Range 39 to 45 your image looks like a longer range

 

[Ayugma]

Everything is fine with the code and it works great thanks!

The only thing is if a person unchekcs and checks multiple times it creates a list multiple times as well as shown below:

Is there a way to fix that so that the list is cleared of that selection once it gets unchecked?

 

[My Aswer Is This]

Most people would start there range in A2 and go down.
but you must have your reasons

 

[Ayugma]

Most people would start there range in A2 and go down.
but you must have your reasons

Yes definitely. This sheet has certain specifications that I'm told shouldn't be adjusted. Any help on that clearing issue would be greatly appreciated!

 

[My Aswer Is This]

Please restate what your wanting about clearing.
Are you wanting all the checkboxes unchecked or the values in the Combobox cleared.
And you want all check boxes unchecked all at once or each one as they are checked
So you select checkbox which puts a checkmark in the checkbox then value is added to combobox and checkbox check is removed

 

[Ayugma]

Please restate what your wanting about clearing.
Are you wanting all the checkboxes unchecked or the values in the Combobox cleared.
And you want all check boxes unchecked all at once or each one as they are checked
So you select checkbox which puts a checkmark in the checkbox then value is added to combobox and checkbox check is removed

Hi so basically this:
User checks the checkbox, then the part number gets added to the list.
However if the user unchecks that box again, then that part number is removed from the list.

 

[My Aswer Is This]

Hi so basically this:
User checks the checkbox, then the part number gets added to the list.
However if the user unchecks that box again, then that part number is removed from the list.

I'm not sure how to do that. I will have to get back to you later on how to do that. I will be offline for next several hours

 

[My Aswer Is This]

Try this:

Private Sub CheckBox1_Click()
'Modified  11/21/2020  2:31:09 AM  EST
Dim SearchString As String
Dim SearchRange As Range
Dim ans As String
Dim i As Long
If CheckBox1.Value = True Then
    SearchString = CheckBox1.Caption
    Set SearchRange = Sheets("Table of Part Numbers").Range("A39:A45").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 If


If CheckBox1.Value = False Then
    SearchString = CheckBox1.Caption
    Set SearchRange = Sheets("Table of Part Numbers").Range("A39:A45").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
    If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
    ans = SearchRange.Offset(0, 1).Value

For i = ComboBox1.ListCount - 1 To 0 Step -1
  If ComboBox1.List(i) = ans Then
    ComboBox1.RemoveItem (i)
  End If
Next
End If
End Sub

Private Sub CheckBox2_Click()
'Modified  11/20/2020  3:12:46 PM  EST
Dim i As Long
Dim SearchString As String
Dim SearchRange As Range
Dim ans As String

If CheckBox2.Value = True Then
SearchString = CheckBox2.Caption
Set SearchRange = Sheets("Table of Part Numbers").Range("A39:A45").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 If


If CheckBox2.Value = False Then
    SearchString = CheckBox2.Caption
    Set SearchRange = Sheets("Table of Part Numbers").Range("A39:A45").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
    If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
    ans = SearchRange.Offset(0, 1).Value

For i = ComboBox1.ListCount - 1 To 0 Step -1
  If ComboBox1.List(i) = ans Then
    ComboBox1.RemoveItem (i)
  End If
Next
End If
End Sub