Member Avatar for setogirl

Question
I want a drop down list, where each item would have a separate value and when one item is selected, the corresponding value shall be posted in a separate cell.

For example, say the items to appear on the list are A, B, C, D and the corresponding values are, 10, 20, 30, 40. Say, when the user selects B, the corresponding value 20 is posted in another cell.

  • 3 Contributors
  • 2 Replies
  • 99 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by AEA
Member Avatar for cguan_77

hi, please check attached file. see if this is what you're trying to do.
below is the code. Hope it helps :()

Private Sub ComboBox1_Change()

Select Case ComboBox1.Value

   Case "A"
   
       Cells(1, 2).Value = "10"
   
   Case "B"
   
     Cells(1, 2).Value = "20"
    
   Case "C"
   
     Cells(1, 2).Value = "30"
    
   Case "D"
   
     Cells(1, 2).Value = "40"

End Select

End Sub


Private Sub Workbook_Open()

With Sheet1.ComboBox1

  .AddItem "A"
  .AddItem "B"
  .AdItem "C"
  .AddItem "D"

End With

End Sub
select_data.zip (14.8 KB)
Edited by cguan_77 because: n/a
Member Avatar for AEA

how about a nested if statement: if (cell1="a",1, if(cell1="b", 2, if...
or a vlookup table

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.