none
Auto-linking cells in different sheets RRS feed

  • 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!
    Friday, February 6, 2015 11:44 AM

Answers

  • 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

    • Marked as answer by Daniel Joachim Thursday, February 12, 2015 3:48 PM
    Thursday, February 12, 2015 12:40 PM
  • 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

    • Marked as answer by Daniel Joachim Thursday, February 12, 2015 3:49 PM
    Wednesday, February 11, 2015 10:32 AM

All replies


  • 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

    Friday, February 6, 2015 2:01 PM
  • 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

    Wednesday, February 11, 2015 12:14 AM
  • 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

    Wednesday, February 11, 2015 8:24 AM
  • 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?
    Wednesday, February 11, 2015 9:51 AM
  • 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

    • Marked as answer by Daniel Joachim Thursday, February 12, 2015 3:49 PM
    Wednesday, February 11, 2015 10:32 AM
  • Thank you, Felipe. I still can't get the code to function properly. Here is the workbook:

    http://www.filedropper.com/dummy

    Wednesday, February 11, 2015 11:37 AM
  • Could we also add a box for inserting the date into the corresponding column?
    Wednesday, February 11, 2015 11:38 AM
  • Wednesday, February 11, 2015 11:50 AM
  • 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

    Thursday, February 12, 2015 11:02 AM
  • 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

    • Marked as answer by Daniel Joachim Thursday, February 12, 2015 3:48 PM
    Thursday, February 12, 2015 12:40 PM
  • Excellent. Thank you so much!
    Thursday, February 12, 2015 3:48 PM