Answered by:
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!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.nzWednesday, 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
-
https://onedrive.live.com/redir?resid=FB206A2D510E0661!91072&authkey=!ANOP4CcAS0aPjqU&ithint=file%2cxlsm
Felipe Costa Gualberto - http://www.ambienteoffice.com.br
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