SOLVED

Creating a Roster

%3CLINGO-SUB%20id%3D%22lingo-sub-2280611%22%20slang%3D%22en-US%22%3ECreating%20a%20Roster%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2280611%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20automate%20building%20a%20roster%20(Roster%20worksheet)%20from%20the%20Drop%20Down%20list%20created%20(from%20Sheet1).%20I%20now%20cut%20and%20paste%20each%20name%20and%20place%20it%20in%20the%20cell%20as%20I%20select%20it.%20What%20I%20would%20like%20to%20do%20is%20when%20I%20select%20my%20first%20name%20from%20the%20drop%20down%20list%2C%20it%20automatically%20goes%20into%20Cell%20B6%2C%20the%20next%20name%20I%20select%20would%20go%20into%20B7%2C%20etc.%20The%20spreadsheet%20is%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2280611%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2280938%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Roster%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2280938%22%20slang%3D%22en-US%22%3EThis%20is%20fantastic.%20I%20just%20started%20taking%20a%20comprehesive%20course%20on%20Excel.%20But%20I%20never%20would%20have%20been%20able%20to%20do%20this.%20Many%20thanks.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2280866%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Roster%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2280866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1002259%22%20target%3D%22_blank%22%3E%40marty007%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20code%20should%20go%20into%20the%20worksheet%20module%20of%20Sheet2%2C%20since%20the%20dropdown%20is%20on%20that%20sheet.%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%20If%20you%20get%20a%20warning%20that%20macros%20have%20been%20disabled%2C%20click%20to%20allow%20them.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2280709%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Roster%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2280709%22%20slang%3D%22en-US%22%3EI've%20never%20done%20anything%20like%20this%20before.%20I%20entered%20the%20code%20in%20Sheet1.%20I%20believe%20I've%20done%20everything%20through%20saving%20the%20workbook.%20I'm%20not%20sure%20how%20to%20allow%20macros.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2280660%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Roster%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2280660%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1002259%22%20target%3D%22_blank%22%3E%40marty007%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERight-click%20the%20sheet%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20'View%20Code'%20from%20the%20context%20menu.%3C%2FP%3E%0A%3CP%3ECopy%20the%20following%20code%20into%20the%20worksheet%20module%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20If%20Not%20Intersect(Range(%22S5%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20If%20Range(%22S5%22).Value%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20r%20%3D%20Range(%22B%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%20%2B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20r%20%26gt%3B%3D%2031%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20MsgBox%20%22Roster%20is%20full!%22%2C%20vbExclamation%0A%20%20%20%20%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Range(%22B%22%20%26amp%3B%20r).Value%20%3D%20Range(%22S5%22).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESwitch%20back%20to%20Excel.%3C%2FP%3E%0A%3CP%3ESave%20the%20workbook%20as%20a%20macro-enabled%20workbook%20(*.xlsm).%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20you%20allow%20macros%20when%20you%20open%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2299464%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Roster%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2299464%22%20slang%3D%22en-US%22%3EHello%20Hans%2C%3CBR%20%2F%3EI%20noticed%20in%20my%20Roster%2C%20when%20it%20adds%20a%20name%20to%20the%20Roster%2C%20the%20Drop%20Down%20list%20is%20still%20associated%20with%20the%20name%20(I%20see%20the%20Drop%20Down%20arrow%20in%20the%20cell).%20Is%20it%20possible%20to%20just%20have%20the%20name%20appear%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2299553%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Roster%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2299553%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1002259%22%20target%3D%22_blank%22%3E%40marty007%3C%2FA%3E%20That's%20an%20artefact.%20Simply%20remove%20the%20data%20validation%20rule%20from%20column%20B.%20The%20code%20doesn't%20add%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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 to do is when I select my first name from the drop down list, it automatically goes into Cell B6, the next name I select would go into B7, etc. The spreadsheet is attached.

7 Replies

@marty007 

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 Sub

Switch 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.

@marty007 

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.

best response confirmed by marty007 (Contributor)
Solution
This is fantastic. I just started taking a comprehesive course on Excel. But I never would have been able to do this. Many thanks.
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.

That did it, thanks again.