Drop down list selection changes cells

joevda · Mar 2, 2019

Hello,

I'm trying to make a spreadsheet for the family reunion that can be shared and answered by the recipients.
I have a list of names, and a box where they can select which food they want. What I wanted is when I click a name(that has already been filled in by that user, that the box with the food selection will change to whatever they selected, and then if I select another name, then it well show what they ordered instead. I'm not sure if this is possible (although I would imagine it would be)

Thanks in advance,
Joseph

My Aswer Is This · Mar 2, 2019

I always think it's best to give specific details.
You said:

I have a list of names, and a box

You did not say where this list of names are
And you said I have a box

What is a Box?

Excel has a Activex and Form Control TextBox

Excel has a ListBox and a ComboBox.

Please be specific and maybe I could help you.

Excel does not have something named a Box

joevda · Mar 2, 2019

So basically, there is a data validation list, with the names of all the people, and what I meant by "box" is basically a table (Just a bunch of cells with borders around them) where the user can type the amount of a certain meal they want.

And what I wanted is for example, if Steve clicks on his name in the data validation list, and he fills in what he wants to order, then it will save those values to his name, so whenever I open the sheet and select Steve's name, it will show what he wants.

Sorry not being specific enough.

Thanks,
Joseph

footoo · Mar 2, 2019

What cell contains the drop down list?

Re the "box" - more info required than "Just a bunch of cells" :
What cells make up the box?
Any heading?
What data is in the box and where is it?
What is entered to make a selection and where?

Perpa · Mar 3, 2019

Joseph,
Welcome to the Forum.
Below is an example of a setup where the drop down for the 'Name' is in C3 and the List of Names is in column L.
I used Forum Tools 'Copy Range' to Generate the BB Code to show my setup.
The drop down for the 'Meals Chosen' is in F3 and the List of Meals Available is in column P.
After the Name and the Meal have been selected, the Meal Chosen is copied to column M on the same row as the Name in Column L.

Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][th]

F

[/th][th]

G

[/th][th]

H

[/th][th]

I

[/th][th]

J

[/th][th]

K

[/th][th]

L

[/th][th]

M

[/th][th]

N

[/th][th]

O

[/th][th]

P

[/th][th]

Q

[/th][th]

R

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td][/td][td][/td][td]Name:[/td][td][/td][td][/td][td]Meal Chosen:[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td][/td][td][/td][td]Pete[/td][td][/td][td][/td][td]Kielbasa and Kraut[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Names[/td][td]Meal Selected[/td][td][/td][td][/td][td]Meals Available[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Pete[/td][td]Kielbasa and Kraut[/td][td][/td][td][/td][td]Hamburger n Fries[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Mike[/td][td][/td][td][/td][td][/td][td]Pork n Beans[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Joe[/td][td][/td][td][/td][td][/td][td]Kielbasa and Kraut[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Pat[/td][td]Hamburger n Fries[/td][td][/td][td][/td][td]Enchiladas and Salad[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Henry[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Marsha[/td][td]Pork n Beans[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Lowen[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Put the macro in a standard module and save the file as macro enabled, then
After you select the Name and Meal Chosen, run the following macro to place the Meal Chosen in Column M in the same row as the Name in column L.
I included code to catch if either the Name or Meal Chosen are omitted. Try it and you'll see how it works fairly quickly.
I used a Form Control Button to run the macro. That can be changed if required to run if either the Name or Meal Chosen are changed (using a Worksheet_Change Event format).

Code:

Sub NamesMeals()
Dim x As Range
Dim LookupRange As Range
Dim LastLRow As Long


Select a Name and Meal Chosen from  drop down lists then run the macro
LastLRow = Range("L" & Rows.Count).End(xlUp).Row


If Cells(3, "C") = "" Then
    MsgBox "No Name Entered!"
    Exit Sub
End If


   Set LookupRange = Range("L5:L" & LastLRow)


    For Each x In LookupRange
        If InStr(1, Cells(3, "C"), x.Value, vbTextCompare) > 0 Then
            If Cells(x.Row, "M") = "" And Cells(3, "F") = "" Then
                MsgBox "No Meal selected"
                Exit Sub
            End If
            
            If Cells(x.Row, "M") = "" And Cells(3, "F") <> "" Then
                Cells(x.Row, "M") = Cells(3, "F")
                Exit Sub
            End If
            
            If Cells(x.Row, "M") <> "" And Cells(3, "F") <> "" And Cells(x.Row, "M") <> Cells(3, "F") Then
                Cells(x.Row, "M") = Cells(3, "F")
                Exit Sub
            End If
            
            If Cells(x.Row, "M") <> "" And Cells(3, "F") = "" Then
                Cells(3, "F") = Cells(x.Row, "M")
                Exit Sub
            End If


        End If
    Next x
End Sub

Lastly, I noticed in post #3 that you mentioned a "...the amount of a certain meal they want". I have not included that, but it could be easily added, maybe in column I and copied to column O in my example setup. The location of the dropdowns, List of Names, List of Meals Available, and Quantity might be different in your setup, so I haven't added the Quantity in this post.
Let me know if the above is helpful.
Perpa

joevda · Mar 3, 2019

https://ibb.co/F4RLSHD

that link is to a picture of basically what I was trying to achieve. In the table with beside the meals, they select what they want, and when I change the name at the top, I want it to change the table to show what the other person has ordered.

footoo · Mar 3, 2019

Assumptions :
• The drop down is in H3
• The meals are in column A with the first meal (Steak) in A8
• The 3 columns for quantities are D:F

Set-up :
• Add a new sheet and name it Sheet2 (the sheet can be hidden if desired)
• Put a button on the original sheet for the purpose of confirming quantities entered
• Assign this macro to the button :

Code:

Sub ConfirmSelection()
Dim rws%, n As Range
rws = Cells(Rows.Count, "A").End(xlUp).Row - 7
With Sheets("Sheet2")
    Set n = .Rows(1).Find([H3])
    If n Is Nothing Then
        Set n = .Cells(1, Columns.Count).End(xlToLeft)(1, 2)
        n.Resize(, 3) = [H3]
    End If
    .Cells(2, n.Column).Resize(rws, 3) = [D8].Resize(rws, 3).Value
End With
End Sub

• Put this in the original sheet module :

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sRng As Range, n As Range
If Target.Address = "$H$3" Then
Application.EnableEvents = False
Set sRng = Range("D8:F" & Cells(Rows.Count, "A").End(xlUp).Row)
With Sheets("Sheet2")
    Set n = .Rows(1).Find([H3], LookAt:=xlWhole)
    If n Is Nothing Then
        sRng.ClearContents
    Else
        sRng = .Cells(2, n.Column).Resize(sRng.Rows.Count, 3).Value
    End If
End With
Application.EnableEvents = True
End If
End Sub

Notes :
• After entering quantities for the name per H3, click the button to confirm and save
• Meals can be added to the bottom of the meals list and the code automatically handles it.
• If meals are inserted or deleted, or if the sequence is changed, the quantities for all names will have to be re-input to account for the changes
• The spaghetti Bolognaise seems a bit expensive

joevda · Mar 3, 2019

Thanks for your help everyone

footoo · Mar 3, 2019

joevda said:
Hello,

thanks for you help so far on this https://www.mrexcel.com/forum/excel...ction-changes-cells.html?posted=1#post5236229.

However I have 1 more question.

The macro you posted writes the name 3 times, https://ibb.co/K5TV1gT

If possible I'd want it to look like this, https://ibb.co/QfVwyJ2

And also the price for the spaghetti is not right lol, I just typed random numbers as an example.

Thanks again,
Joseph

Since Sheet2 is just a working sheet which stores the saved selections, the format is not really important.
But to format per your post :

Code:

Sub ConfirmSelection()
Dim rws%, n As Range
rws = Cells(Rows.Count, "A").End(xlUp).Row - 7
With Sheets("Sheet2")
    Set n = .Rows(1).Find([H3])
    If n Is Nothing Then
        Set n = .Cells(1, Columns.Count).End(xlToLeft)(1, 2)
        n = [H3]
        n.Resize(, 3).HorizontalAlignment = xlCenterAcrossSelection
        n(2) = "Adult"
        n(2, 2) = "Child"
        n(2, 3) = "-3"
    End If
    .Cells(3, n.Column).Resize(rws, 3) = [D8].Resize(rws, 3).Value
End With
End Sub

footoo · Mar 4, 2019

Code:

Sub ConfirmSelection()
Dim rws%, n As Range
rws = Cells(Rows.Count, "A").End(xlUp).Row - 7
With Sheets("Sheet2")
    Set n = .Rows(1).Find([H3])
    If n Is Nothing Then
        If .Cells(2, Columns.Count).End(xlToLeft).Column = 1 Then
            Set n = .[B1]
        Else: Set n = .Cells(2, Columns.Count).End(xlToLeft)(0, 2)
        End If
        n = [H3]
        n.Resize(, 3).HorizontalAlignment = xlCenterAcrossSelection
        n(2) = "Adult"
        n(2, 2) = "Child"
        n(2, 3) = "-3"
    End If
    .Cells(3, n.Column).Resize(rws, 3) = [D8].Resize(rws, 3).Value
End With
End Sub

Drop down list selection changes cells

joevda

New Member

Excel Facts

My Aswer Is This

Well-known Member

joevda

New Member

footoo

Well-known Member

Perpa

Well-known Member

joevda

New Member

footoo

Well-known Member

joevda

New Member

footoo

Well-known Member

footoo

Well-known Member

Similar threads

Forum statistics

Share this page

Drop down list selection changes cells

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock