VBA to return offset cell value based on data validated dropdown list

Question

New to VBA...

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

I'm looking for help with returning the value in the Sheet3!A2:A4 based on the dropdown selection (Sheet3!B2:B4) in order to save a copy of the file with the offset values from the dropdown.

This is what I have so far based on search results, but I know I'm missing something somewhere

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

I don't know how to 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