activate checkbox if value is in a list

[tybalt]

Hi,

I have a table with "Class1" to "Class 4" as headings and then some values under each one

I want to make a user-form that has a text-box and 4 check boxes "Class1"..."Class 4"

When the userform opens if the value in the text-box is in the table under any of the class column headings the corresponding check box is checked, if it is not, the check box for that class is empty.

the last part (probably the most difficult) is that When you click one of the check boxes it will add that text to the first empty row under that column heading. AND if you un check the textbox it will delete that value from the list and then shift the rest of the column's values up to keep from having an empty cell.

I'm really struggling with the coding, and a complete beginning so I would REALLY REALLY appreciate any help! Thanks!

 

[tybalt]

Thoughts? suggestions? for any part of this? I can't seem to figure out how to locate specific cells when the range is dynamic. Help would be very much appreciated!!

Thanks in advance

 

[Marcelo Branco]

Hi,

Suggestions for the 1st part

Assuming
1. dynamic-range name = theRange
2. you are looking for strings inside this range (names for example)

Adjust to suit.

Create a public function like this

Public Function findName(name As String) As String
    Dim rngFound As Range
 
    Set rngFound = Range("theRange").Find(What:=name)
    If Not rngFound Is Nothing Then findName = rngFound.Column - Range("theRange").Column + 1
 
End Function

(This function returns 1, 2 , 3 or 4 accordingly with the column matched)

Then, supposing your checkboxes names are:
CheckBox1
CheckBox2
CheckBox3
CheckBox4

and the TextBox name is TextBox1

In the UserForm_initialize something like

Private Sub UserForm_Initialize()
 
    TextBox1.Value = "Bob"
    If findName(TextBox1.Value) <> "" Then _
        Me.Controls("Checkbox" & findName(TextBox1.Value)).Value = True
 
End Sub

HTH

M.