Automatically check checkboxes when .Search vba in userform?

So I have the following code below: and what i want to focus on is the Private Sub Search_Click(). Currently when I search someone's surname it populate the textboxes automatically. Is it possible to have a search box that populates the checkboxes automatically? so for example, if the person belongs to 6/8 checkboxes, and i click search 6/8 checkboxes would be checkmarked? and is it possible to do the same thing with ListBox1_Click()? so when i click on person's name from listbox, it also popoulates the checkbocxes automatically depending on which checkbox that person belongs to?

EDIT

Private Sub Search_Click() 'only searches in master tab right now need to search from all worksheets
Dim Name As String
Dim f As Range
Dim r As Long
Dim ws As Worksheet
Dim s As Integer
Dim FirstAddress As String

Name = surname.Value

For Each ws In ActiveWorkbook.Sheets
With ws
  Set f = Range("A:A").Find(what:=Name, LookIn:=xlValues)
 If Not f Is Nothing Then
    If cb.Name = ws.Name Then
        cb.Value = True
    End If
Next
 With Me
    firstname.Value = f.Offset(0, 1).Value
    tod.Value = f.Offset(0, 2).Value
    program.Value = f.Offset(0, 3).Value
    email.Value = f.Offset(0, 4).Text
    officenumber.Value = f.Offset(0, 5).Text
    cellnumber.Value = f.Offset(0, 6).Text

Is this what it would look like...? I can't get it to work though?

Private Sub CommandButton1_Click()
MsgBox "Directorate has been added", vbOKOnly

 Dim ctrl As control
   For Each ctrl In UserForm1.Controls
     If TypeName(ctrl) = "CheckBox" Then
       'Pass this CheckBox to the subroutine below:
     TransferValues ctrl
     End If
   Next
TransferMasterValue
End Sub

Sub TransferValues(cb As MSForms.CheckBox)
 Dim ws As Worksheet 
 Dim emptyRow As Long

If cb Then
   'Define the worksheet based on the CheckBox.Name property:
    Set ws = Sheets(Left(cb.Name, 15))
    emptyRow = WorksheetFunction.CountA(ws.range("A:A")) + 1
       With ws
           .Cells(emptyRow, 1).Value = surname.Value
           .Cells(emptyRow, 2).Value = firstname.Value
           .Cells(emptyRow, 3).Value = tod.Value
           .Cells(emptyRow, 4).Value = program.Value
           .Cells(emptyRow, 5).Value = email.Value
           .Cells(emptyRow, 6).Value = officenumber.Value
           .Cells(emptyRow, 7).Value = cellnumber.Value
        End With

  End If
End Sub

Sub TransferMasterValue()
 Dim allChecks As String
 Dim ws As Worksheet
   'Iterate through the checkboxes concatenating a string of all names
 For Each ctrl In UserForm1.Controls
   If TypeName(ctrl) = "CheckBox" Then
    If ctrl Then
        allChecks = allChecks & ctrl.Name & ""

    End If
   End If
 Next

'If you have at least one transfer to the Master sheet
  If Len(allChecks) > 0 Then
    Set ws1 = Sheets("Master")
    emptyRow = WorksheetFunction.CountA(range("A:A")) + 1

    With ws1
        .Cells(emptyRow, 1).Value = surname.Value
        .Cells(emptyRow, 2).Value = firstname.Value
        .Cells(emptyRow, 3).Value = tod.Value
        .Cells(emptyRow, 4).Value = program.Value
        .Cells(emptyRow, 5).Value = email.Value
        .Cells(emptyRow, 7).Value = officenumber.Value
        .Cells(emptyRow, 8).Value = cellnumber.Value
        .Cells(emptyRow, 6).Value = Left(allChecks, Len(allChecks) - 1)
    End With
  End If
End Sub

Private Sub CommandButton2_Click()
 Unload UserForm1
End Sub

Private Sub CommandButton3_Click()
 surname.Value = ""
 firstname.Value = ""
 tod.Value = ""
 program.Value = ""
 email.Value = ""
 officenumber.Value = ""
 cellnumber.Value = ""
 PACT.Value = False
 PrinceRupert.Value = False
 WPM.Value = False
 Montreal.Value = False
 TET.Value = False
 TC.Value = False
 US.Value = False
 Other.Value = False
End Sub

Private Sub ListBox1_Click()
 Dim r As Long
 With Me.ListBox1

  With Me
    .surname.Value = .ListBox1.List(.ListBox1.ListIndex, 0)
    .firstname.Value = .ListBox1.List(.ListBox1.ListIndex, 1)
    .tod.Value = .ListBox1.List(.ListBox1.ListIndex, 2)
    .program.Value = .ListBox1.List(.ListBox1.ListIndex, 3)
    .email.Value = .ListBox1.List(.ListBox1.ListIndex, 4)
    .officenumber.Value = .ListBox1.List(.ListBox1.ListIndex, 5)
    .cellnumber.Value = .ListBox1.List(.ListBox1.ListIndex, 6)
  End With
 End With
End Sub

Private Sub Search_Click() 'only searches in master tab right now need to search from all worksheets
 Dim Name As String
 Dim f As range
 Dim r As Long
 Dim ws As Worksheet
 Dim s As Integer
 Dim FirstAddress As String

   Name = surname.Value

     With ws
        Set f = range("A:A").Find(what:=Name, LookIn:=xlValues)
       If Not f Is Nothing Then
     With Me
        firstname.Value = f.Offset(0, 1).Value
        tod.Value = f.Offset(0, 2).Value
        program.Value = f.Offset(0, 3).Value
        email.Value = f.Offset(0, 4).Text
        officenumber.Value = f.Offset(0, 5).Text
        cellnumber.Value = f.Offset(0, 6).Text
     End With
   findnext
        FirstAddress = f.Address
Do
    s = s + 1
    Set f = range("A:A").findnext(f)
            Loop While Not f Is Nothing And f.Address <> FirstAddress
    If s > 1 Then
       Select Case MsgBox("There are " & s & " instances of " & Name, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")

       Case vbOK
            findnext
        Case vbCancel
       End Select

    End If

Else: MsgBox Name & "Not Listed"
End If
End With

End Sub

Sub findnext()
 Dim Name As String
 Dim f As range
 Dim ws As Worksheet
 Dim s As Integer
 Dim findnext As range

   Name = surname.Value
   Me.ListBox1.Clear
     Set f = range("A:A").Find(what:=Name, LookIn:=xlValues)
     Set findnext = f

      With ListBox1
     Do
      Debug.Print findnext.Address
      Set findnext = range("A:A").findnext(findnext)
       .AddItem findnext.Value
       .List(0, 1) = findnext.Offset(0, 1).Value
       .List(0, 2) = findnext.Offset(0, 2).Value
       .List(0, 3) = findnext.Offset(0, 3).Value
       .List(0, 4) = findnext.Offset(0, 4).Value
       .List(0, 5) = findnext.Offset(0, 5).Value
       .List(0, 6) = findnext.Offset(0, 6).Value
       .List(0, 7) = findnext.Offset(0, 6).Value
   Loop While findnext.Address <> f.Address
       End With

End Sub

Answers


It doesnt seem like a coding problem, I saw the code where you set the textboxes text. Basically, instead of setting textboxes text, you'd be setting the checkbox. the code to to that is ChkCheck.value = true.

leave a comment if theres a deeper issue and ill make an edit

edit

To search all sheets,

I would put a loop in there like

for each ws in ActiveWorkbook.Sheets

and then put your search in there.

and then, after the if f is not nothing then

loop through all controls, and check to see if the controls name = the sheets name. -

if ctrl.name = ws.name then
    ctrl.value = true
end if

like that

so then each time the sheets loop runs, if it finds the specific name, the checkbox accosiated with the particular sheet will check.


Need Your Help

How would one do dependency injection in scala?

scala dependency-injection

I'm still at the beginning in learning scala in addition to java and i didn't get it how is one supposed to do DI there? can or should i use an existing DI library, should it be done manually or is...

How to use sgi hash_table in VS2005?

c++ stl hashmap sgi

I wrote a C++ project in VS2005, and used lots of STL container with its plus-in STL. However, I found STL in VS2005 does not have a hash_map in it, I want to use SGI hash_map. How can I change my