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.

  • How many items are in your dropdown list? There are a number of possible approaches here, getting a sense of size will help. – user2800 Mar 22 '19 at 18:18

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 = ""
        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 | |
  • 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 S Mar 23 '19 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 Mar 23 '19 at 5:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.