0

I am very new at vba and am interested in creating a program that does the following. When a item is selected from a drop down menu it will paste certain text into the cells that are next to the drop down list selected.

For example: My drop down list contains in column A COMMENT MOVE

When I select "COMMENT" I would like to populate in column B "ENTER COMMENT" When I select "MOVE" I would like to populate in Column B "ENTER MOVE TYPE" and in Column C "ENTER MOVE SPEED"

I would only like these to change upon selecting the drop down menu as to I am using this data in cells other cells based upon which drop down menu item is selected.

Improve this question
1
  • How many items are in your dropdown list? There are a number of possible approaches here, getting a sense of size will help.
    – user2800
    Commented Mar 22, 2019 at 18:18

1 Answer 1

Reset to default
2

If I understand correctly, you could do this with either VBA or just a function. In my example, I have a list in A1 and in A4 Both containing "COMMENT" and "MOVE".

enter image description here

In B1 I have the function =IF(A1="MOVE","ENTER MOVE TYPE",IF(A1="COMMENT","ENTER COMMENT"," "))

And in C1 the function =IF(A1="MOVE","ENTER MOVE SPEED","")

For the list in A4 I'm using VBA on the worksheet (right click the sheet and select "show code") 

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range(Target.Address), Range("A4")) Is Nothing And Target.Count = 1 Then
    If Target.Value2 = "MOVE" Then
        Target.Offset(, 1).Value = "ENTER MOVE TYPE"
        Target.Offset(, 2).Value = "ENTER MOVE SPEED"
    ElseIf Target.Value2 = "COMMENT" Then
        Target.Offset(, 1).Value = "ENTER COMMENT"
        Target.Offset(, 2).Value = ""
    Else
        Target.Offset(, 1).Value = ""
        Target.Offset(, 2).Value = ""
    End If
End If
End Sub

I'm using a relative position of the text in B and C with the help of .Offset so less code needs to be changed depending on list location.

Improve this answer
2
  • You have almost achieve OP's need but I think the whole should be restrict to one Cell in Column A instead of A1 & A4.
    – Rajesh Sinha
    Commented Mar 23, 2019 at 4:58
  • @RajeshS It's just to showcase two different ways of doing it at once. Doesn't really work to have both on the same cell, so he should pick one and use it in whatever cell he needs.
    – Christofer Weber
    Commented Mar 23, 2019 at 5:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .