Forum Discussion
Solved
Creating a Roster
Is there a way to automate building a roster (Roster worksheet) from the Drop Down list created (from Sheet1). I now cut and paste each name and place it in the cell as I select it. What I would like...
- This is fantastic. I just started taking a comprehesive course on Excel. But I never would have been able to do this. Many thanks.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
If Not Intersect(Range("S5"), Target) Is Nothing Then
If Range("S5").Value <> "" Then
r = Range("B" & Rows.Count).End(xlUp).Row + 1
If r >= 31 Then
MsgBox "Roster is full!", vbExclamation
Else
Application.EnableEvents = False
Range("B" & r).Value = Range("S5").Value
Application.EnableEvents = True
End If
End If
End If
End SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
I've never done anything like this before. I entered the code in Sheet1. I believe I've done everything through saving the workbook. I'm not sure how to allow macros.
The code should go into the worksheet module of Sheet2, since the dropdown is on that sheet.
See the attached version. If you get a warning that macros have been disabled, click to allow them.
- Hello Hans,
I noticed in my Roster, when it adds a name to the Roster, the Drop Down list is still associated with the name (I see the Drop Down arrow in the cell). Is it possible to just have the name appear?marty007 That's an artefact. Simply remove the data validation rule from column B. The code doesn't add it.
- This is fantastic. I just started taking a comprehesive course on Excel. But I never would have been able to do this. Many thanks.