Return offset cell value based on data validated dropdown list

Question

I have an invoice template with a data validation dropdown in Sheet1!A10 with the list location in Sheet3!B2:B4.

I want to return the value in the Sheet3!A2:A4 based on the dropdown selection (Sheet3!B2:B4) to save a copy of the file with the offset values from the dropdown.

Sub FileSaveAs()
    Dim custlist As String
    Dim custname As String
    Dim path As String
    Dim filename As String

    custlist = Sheets(1).DropDowns(Range("A10:C10"))
    custname = Application.WorksheetFunction.VLookup(custlist, Sheet3.Range("a:b"), 1, False)
    path = "C:\Users\files" 
    filename = custname 

    Sheet1.Copy

    With ActiveWorkbook
        .Sheets(1).Name = "Invoice"
        .SaveAs filename:=path & filename, FileFormat:=51
        .Close
    End With
End Sub

How do I set the the value of custname based on the dropdown selection custlist?

Answer 1

• Read the value of a cell with a data validation dropdown as a normal one.

Microsoft documentation:

StrComp function

Option Explicit

Sub FileSaveAs()
    Dim custList As String
    Dim custName As String
    Dim sPath As String
    custList = Sheet1.Range("A10").Value
    sPath = "C:\Users\files\"
    If Len(custList) > 0 Then
        For Each c In Sheet3.Range("B2:B4")
            If StrComp(c.Value, custList, vbTextCompare) = 0 Then
                custName = c.Offset(0, -1).Value
            End If
        Next
    End If
    If Len(custList) > 0 Then
        Sheet1.Copy
        With ActiveWorkbook
            .Sheets(1).Name = "Invoice"
            .SaveAs filename:=sPath & custName, FileFormat:=51
            .Close
        End With
    End If
End Sub