Auto-linking cells in different sheets

Question

I have a sheet 1 with an overview of a large number of people with some information, such as "name", "date", "attendance", and so on.

1. Can I now make a small registration sheet 2, where I start to insert the name (e.g. as a auto-complete search field or a drop down menu), and this is immediately linked to the cell of the corresponding name from the list in sheet 1?
2. Once I've linked the names together - can I then continue in sheet 2 to insert e.g. date and attendance, and in sheet 1 this is then automatically added to the horizontal cells next to the name?

I would really appreciate your input on this issue. Thank you!

Answer 1

Suppose you have this workskeet:

It's better for you to build an userform called Registration with some controls like this:

And use this code:

'Where your data begins?
Const mclFirstDataRow As Long = 2

Private Enum c
    NameCol = 1
    DataCol
    AttendanceCol
End Enum
    
Dim moSheet As Excel.Worksheet

Private Sub UserForm_Initialize()
    Dim lRow As Long
    
    'You can set these properties at design time, if you want:
    Me.cboName.Style = fmStyleDropDownList
    Me.btnQuit.Cancel = True
    
    'Change worksheet name to suit:
    Set moSheet = ThisWorkbook.Worksheets("Sheet1")

    'Fill combobox
    With moSheet
        For lRow = mclFirstDataRow To .Cells(.Rows.Count, c.NameCol).End(xlUp).Row
            Me.cboName.AddItem .Cells(lRow, c.NameCol)
        Next lRow
    End With
End Sub

Private Sub cboName_Change()
    Dim lRow As Long
    
    If Not isValidNameSelection Then Exit Sub
    
    lRow = getColumn
    If moSheet.Cells(lRow, c.AttendanceCol) = "Yes" Then
        Me.ckbAttended = True
    Else
        Me.ckbAttended = False
    End If
End Sub

Private Sub btnSave_Click()
    Dim lRow As Long
    Dim bAttended As Boolean
    
    If Not isValidNameSelection Then
        MsgBox "The combobox is empty!", vbExclamation
        Exit Sub
    End If
    
    lRow = getColumn
    
    bAttended = Me.ckbAttended
    If bAttended Then
        moSheet.Cells(lRow, c.AttendanceCol) = "Yes"
    Else
        moSheet.Cells(lRow, c.AttendanceCol).ClearContents
    End If

    MsgBox "Saved successfully.", vbInformation
End Sub

Private Sub btnQuit_Click()
    Unload Me
End Sub

'--- Auxiliar functions ---
Private Function getColumn() As Long
    getColumn = Me.cboName.ListIndex + mclFirstDataRow
End Function

Private Function isValidNameSelection() As Boolean
    If Me.cboName.ListIndex <> -1 Then
        isValidNameSelection = True
    End If
End Function

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 2

Thank you. That helps a lot.

I've tried to edit the commands to fit my sheet, but it only display errors. Do you have any tips for making this work in this sheet, using the form to search for a name among 1.000 in the B4, B5....Bx-1...Bx column, and matching it with the corresponding J column? (I've deleted all other content, and the inserted content are only for explanatory purpose).

Illustration here:

http://i.imgur.com/47BcWJF.jpg

Answer 3

You can also use the function VLOOKUP, see help for details.

---

Rod Gill
Author of the one and only Project VBA Book
www.project-systems.co.nz

Answer 4

But VLOOKUP doesn't help me to make a form to insert data into this sheet. It only helps me to look up certain values in a separate, that would instantly disappear when I look up another one?

Answer 5

Code below. Remeber you also have to create the userform, and the code must go inside UserForm module:

'Where your data begins?
Const mclFirstDataRow As Long = 4

Private Enum c
    NameCol = 2
    DataCol = 3
    AttendanceCol = 10
End Enum
    
Dim moSheet As Excel.Worksheet

Private Sub UserForm_Initialize()
    Dim lRow As Long
    
    'You can set these properties at design time, if you want:
    Me.cboName.Style = fmStyleDropDownList
    Me.btnQuit.Cancel = True
    
    'Change worksheet name to suit:
    Set moSheet = ThisWorkbook.Worksheets("Participation and follow-up")

    'Fill combobox
    With moSheet
        For lRow = mclFirstDataRow To .Cells(.Rows.Count, c.NameCol).End(xlUp).Row
            Me.cboName.AddItem .Cells(lRow, c.NameCol)
        Next lRow
    End With
End Sub

Private Sub cboName_Change()
    Dim lRow As Long
    
    If Not isValidNameSelection Then Exit Sub
    
    lRow = getRow
    If moSheet.Cells(lRow, c.AttendanceCol) = "Yes" Then
        Me.ckbAttended = True
    Else
        Me.ckbAttended = False
    End If
End Sub

Private Sub btnSave_Click()
    Dim lRow As Long
    Dim bAttended As Boolean
    
    If Not isValidNameSelection Then
        MsgBox "The combobox is empty!", vbExclamation
        Exit Sub
    End If
    
    lRow = getRow
    
    bAttended = Me.ckbAttended
    If bAttended Then
        moSheet.Cells(lRow, c.AttendanceCol) = "Yes"
    Else
        moSheet.Cells(lRow, c.AttendanceCol).ClearContents
    End If

    MsgBox "Saved successfully.", vbInformation
End Sub

Private Sub btnQuit_Click()
    Unload Me
End Sub

'--- Auxiliar functions ---
Private Function getRow() As Long
    getRow = Me.cboName.ListIndex + mclFirstDataRow
End Function

Private Function isValidNameSelection() As Boolean
    If Me.cboName.ListIndex <> -1 Then
        isValidNameSelection = True
    End If
End Function

If you can't make it work, share your workbook.

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 6

Thank you, Felipe. I still can't get the code to function properly. Here is the workbook:

http://www.filedropper.com/dummy

Answer 7

Could we also add a box for inserting the date into the corresponding column?

Answer 8

https://onedrive.live.com/redir?resid=FB206A2D510E0661!91072&authkey=!ANOP4CcAS0aPjqU&ithint=file%2cxlsm

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 9

Thank you. This is very good.

One final question then: If I were to insert the date into the C column, either from a textbox (preferred) or from a combobox?

Like this.

http://www.filedropper.com/dummy2

Answer 10

Put the date into a TextBox, but put a validation to check if the TextBox contains a valid date:

Private Sub btnSave_Click()
    Dim lRow As Long
    Dim bAttended As Boolean
    
    If Not isValidNameSelection Then
        MsgBox "The combobox is empty!", vbExclamation
        Exit Sub
    End If

    If Not IsDate(Me.txtDate) Then
        MsgBox "Put a valid date!", vbExclamation
        Exit Sub
    End If
    
    lRow = getRow
    
    bAttended = Me.ckbAttended
    If bAttended Then
        moSheet.Cells(lRow, c.AttendanceCol) = "Yes"
    Else
        moSheet.Cells(lRow, c.AttendanceCol).ClearContents
    End If
    moSheet.Cells(lRow, "C") = CLng(CDate(Me.txtDate))

    MsgBox "Saved successfully.", vbInformation
End Sub

Don't forget to format column C as Date.

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 11

Excellent. Thank you so much!