On Oct 19, 11:35*am, "Rick Rothstein"
wrote:
If you don't mind the name list being on the same worksheet as the table
assignments, I think you may like the following event code setup's
functionality. Put your name list in Column A. Next, decide on which columns
you will use for your table assignments and assign them to the FirstTable
and LastTable constant (the Const) statements at the top of the code. Now,
right click the tab at the bottom of the worksheet where your names are,
select View Code from the popup list that appears and copy paste all the
code below into the code window that opened up.

Once you have done that, go back to the worksheet, select a name (it will
highlight in a color to show you it is selected) and then double click a
cell in one of the table columns... the name will be moved from the list to
the cell you double clicked. If you make a mistake or change your mind about
an assignment, just double click a filled in cell in a table column and that
name will be moved back into the first empty slot in the name column... plus
it will remain the selected name so that you can simply double click into a
different table cell to place it there instead. Anyway, give this a try (on
a test sheet) to see if you like it or not.

'**************** START OF CODE ****************
Dim SelectedName As String
Dim SelectedAddress As String
Const FirstTable As Long = 2
Const LastTable As Long = 14

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
* * * * * * * * * * * * * * * * * * * * Cancel As Boolean)
* Dim UnusedCell As Range
* If Target.Column = FirstTable And Target.Column <= LastTable Then
* * Cancel = True
* * If Target.Value = "" Then
* * * Target.Value = SelectedName
* * * Range(SelectedAddress).Value = ""
* * * Range(SelectedAddress).Interior.ColorIndex = 0
* * * SelectedName = ""
* * Else
* * * Set UnusedCell = Columns("A").Find("", After:=Cells(Rows.Count, "A"))
* * * SelectedName = Target.Value
* * * SelectedAddress = UnusedCell.Address
* * * UnusedCell.Value = Target.Value
* * * UnusedCell.Interior.ColorIndex = 4
* * * UnusedCell.Select
* * * Target.Value = ""
* * End If
* End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
* If Target.Count 1 Then Exit Sub
* If Target.Column = 1 Then
* * Intersect(ActiveSheet.UsedRange, Columns("A")).Interior.ColorIndex = 0
* * SelectedName = Target.Value
* * SelectedAddress = Target.Address
* * Target.Interior.ColorIndex = 4
* End If
End Sub
'**************** END OF CODE ****************

--
Rick (MVP - Excel)

"Scott Halper" wrote in message

...



I'm trying to create a macro to use for setting table assignments at
my wedding. What I'm trying to do is have a box on the left that
contains every single person's name who is invited (on a different
tab). *Then I have colums that represent the different tables and when
I type someone's name in that column I want the person's name in the
large full list to disappear so that the large list only shows people
that are not assigned to a table yet.

Thanks in advance for the help.

Scott- Hide quoted text -

- Show quoted text -

Rick,
That works, thanks for your help.

Scott