Dropdown list and Vlookup simplification

[jmcconnell]

Hi,

I've got a drop down list (SiteList) and when I select an option for that list it looks up details in a spreadsheet called AMSM and fills in a text box(SMName) on the same sheet as the drop down list. There is about 30 options in the drop down list. There is a sample of 2 below:


Private Sub SiteList_Change()
Dim ws As Worksheet
Dim SManager As String
Set ws = Sheets("AMSM")

If SiteList = "Kelburn" Then
SManager = Application.VLookup("Kelburn", ws.Range("A1:c112"), 3)
SMName = SManager
ElseIf SiteList = "Garreg Lwyd" Then
SManager = Application.VLookup("Garreg Lwyd", ws.Range("A1:c112"), 3)
SMName = SManager
End IF

Rather than having if statements as above for all 30 options, is there a way of simplifying this - Perhaps with a loop?

Thank you for your help!!!
Kind regards,
James.

 

[Fluff]

How are you populating the Combobox?

 

[jmcconnell]

Hi Fluff, Combobox is populated from a sheet within the workbook. Not the AMSM sheet though.

 

[Fluff]

But how is it populated? By code, by listfillrange, or some other method?
Also is it an ActiveX or Form Control combo?

 

[jmcconnell]

Sorry. I'm fairly new to this. It's ListFillRange. and its an Active X combo box.

 

[Fluff]

Just realised what you meant by

Not the AMSM sheet though.

Which makes things a bit more awkward. Could you use col A on the AMSM for the listfillrange?
If not will the values in the listfillrange, always be in the sme order as they are in col A of AMSM?

 

[jmcconnell]

Yes I can change it to use Column A on the AMSM sheet. That's not a major change at all.

 

[Fluff]

Ok, in that case you can use

Private Sub SiteList_Click()

   SMName = Sheets("AMSM").Range("C" & SiteList.ListIndex + 1)
End Sub

This assumes that the listfillrange starts in A1, if it starts in A2 just change the +1 to +2

 

[jmcconnell]

Hi Fluff. Thanks very much for the help. I'll have a go.

 

[Fluff]

Ok.