0

I am trying to figure out how to make a drop down list in a cell in Excel that a formula in VBA will reference.

For instance, this is my code:

    Worksheets("Input").Range("name").Select
    ActiveCell = Application.Xlookup(Worksheets("Input").Range("phone"), Worksheets("MASTER").Range("S:S"), Worksheets("MASTER").Range("T:T"))
End Sub

Sub bizlookup()
    Worksheets("Input").Range("company").Select
    ActiveCell = Application.Xlookup(Worksheets("Input").Range("phone"), Worksheets("MASTER").Range("S:S"), Worksheets("MASTER").Range("U:U"))
End Sub

Sub emaillookup()
    Worksheets("Input").Range("email").Select
    ActiveCell = Application.Xlookup(Worksheets("Input").Range("phone"), Worksheets("MASTER").Range("S:S"), Worksheets("MASTER").Range("V:V"))
End Sub

Sub addresslookup()
    Worksheets("Input").Range("address").Select
    ActiveCell = Application.Xlookup(Worksheets("Input").Range("phone"), Worksheets("MASTER").Range("S:S"), Worksheets("MASTER").Range("W:W"))
End Sub

Sub cszlookup()
    Worksheets("Input").Range("csz").Select
    ActiveCell = Application.Xlookup(Worksheets("Input").Range("phone"), Worksheets("MASTER").Range("S:S"), Worksheets("MASTER").Range("X:X"))
End Sub

Instead of "Worksheets("Master"), I want that to be a variable that changes the sheet depending on the text in a cell. Any ideas?

This is a quick mockup of my frontend form

This is a quick mockup of my frontend form

braX
  • 11,506
  • 5
  • 20
  • 33
Ahoycaptain10234
  • 7
  • 7
  • Worksheets(Range("A1").Value)..... where cell A1 can have any value. Better is to give a name at A1 (named range) e.g "THE_WS" and call: Worksheets(Range("THE_WS").Value)..... – ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ Feb 24 '23 at 22:18
  • Side note: it's a good idea to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. – cybernetic.nomad Feb 24 '23 at 22:27

0 Answers0