VBA - help creating drop down box from table to insert on another sheet

[JTTrickett]

Hello,

this is my first post!

I have this table below, over multiple columns and rows.

Basically this list is my data and on a separate sheet I want to be able to select for example (Almonds, Banana etc) using a drop down box. I have then created, albeit not perfect, A V lookup to carry over the remaining rows (numbers)).

It would be amazing to have the code automatically update the dropdown box options if i add new food items.





[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 605"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Type[/TD]
[TD]Weight(g) / Number[/TD]
[TD]Fats[/TD]
[TD]Carbs[/TD]
[TD]Protein[/TD]
[TD]Sugar[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]Milk - Almond - Unsweetened[/TD]
[TD]100[/TD]
[TD]1.2[/TD]
[TD]0.4[/TD]
[TD]0.5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Almonds[/TD]
[TD]100[/TD]
[TD]55[/TD]
[TD]20[/TD]
[TD]22[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Asparagus[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Avocado[/TD]
[TD]100[/TD]
[TD]15[/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Bagel - Wholemeal - NewYorkBagel[/TD]
[TD]1[/TD]
[TD]2.5[/TD]
[TD]40.8[/TD]
[TD]11[/TD]
[TD]4.3[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Beef - Sliced - ALDI[/TD]
[TD]100[/TD]
[TD]3[/TD]
[TD]0.5[/TD]
[TD]27[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]Beef - Brisket - Musclefood[/TD]
[TD]100[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]21[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Beef - Hache Steak - Musclefood[/TD]
[TD]100[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]21[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Beef - Sliced Beef - Musclefood[/TD]
[TD]100[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Biscuit - Lotus[/TD]
[TD]1[/TD]
[TD]1.5[/TD]
[TD]5.7[/TD]
[TD]0.4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Brocolli[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Alan_P]

Hi JTTrickett,

Welcome to the forum!

You can just use a data validation list and have the source extend past the current last line and tick the "ignore blanks" box?

But if you really want a VBA solution...

Right click on the sheet tab you want to have the drop down on, select view code - paste below

Private Sub Worksheet_Activate()

LastRow = Sheets("[COLOR="#FF0000"]Sheet1[/COLOR]").Range("A" & Rows.Count).End(xlUp).Row

ValRange = "=[COLOR="#FF0000"]Sheet1[/COLOR]!$A$2:$A$" & LastRow

    With Sheets("[COLOR="#FF0000"]Sheet2[/COLOR]").Range("[COLOR="#0000FF"]A1[/COLOR]").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=ValRange
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With

End Sub

Change the red sheet names as appropriate, this will put the dropdown in Cell A1 of the second sheet (Change blue to required cell)

Hope this helps,
Cheers,
Alan.

 

[JTTrickett]

Hi Alan,

Thank you very much for this. I am trying to teach myself all things VBA so trying to use it wherever possible.

When i try to enter the code it says it needs debugging between: .Add Type .... Forumla1 : = valrange.

However, due to my lack of knowledge ! I am not sure what needs correcting. For more information:

"sheet1" ( where the list is ) is called Food Macros and "sheet2* (destination) is called Daily Planner. I intend to have dropdowns from A3 - A42.

Many thanks!

Josh

 

[Alan_P]

Hi josh,

My apologies, I left out the Apostrophes when defining the ValRange...

I've added in a loop to apply it to cells A3:A42

I'm trying to teach myself VBA too, I've found that your 2 best friends are the Macro Recorder and this forum!

Private Sub Worksheet_Activate()

LastRow = Sheets("Food Macros").Range("A" & Rows.Count).End(xlUp).Row

ValRange = "=[COLOR="#FF0000"]'[/COLOR]Food Macros[COLOR="#FF0000"]'[/COLOR]!$A$2:$A$" & LastRow

For i = 3 To 42
    With Sheets("Daily Planner").Range("A" & i).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=ValRange
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
Next

End Sub

Please feel free to ask any further questions.

Hope this helps,
Cheers,
Alan.