Improving efficiency of VBA search in worksheet_change event

I have a part of a worksheet as laid out in the snippet below.

The workbook names and worksheet names are selectable through data validation.

The data validation is updated whenever the user selects the appropriate cell.

Part of the code used to update the validation is given below.

The problem is that I have used the .find method to locate the cells containing the workbook and worksheet names. This is because both the location and the order of files may vary. And every time the selection is changed the find sequence is executed which slows down cursor movement in the worksheet.

Is there any way to do the .find at workbook_open and remember the settings throughout the session?







Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then Exit Sub
wbcol = UsedRange.Find("Workbook").Column
wscol = UsedRange.Find("Worksheet").Column
nslrow = UsedRange.Find("NSL File").Row
desrow = UsedRange.Find("Design File").Row
tplrow = UsedRange.Find("Template File").Row
parrow = UsedRange.Find("Parameters File").Row
prtrow = UsedRange.Find("Printer Name").Row
optrow = UsedRange.Find("Output paramaters").Row
outrow = UsedRange.Find("Output table file").Row
If Target.Column = wbcol Then
    If Target.Row = nslrow Or _
        Target.Row = desrow Or _
        Target.Row = tplrow Or _
        Target.Row = parrow Or _
        Target.Row = optrow Or _
        Target.Row = outrow Then
        wblist = ""
        For Each wb In Application.Workbooks
        wblist = wblist & wb.Name & ","
        'wblist = wblist & wb.Path & "\" & wb.Name & ","
        Next wb
        wblist = Left(wblist, Len(wblist) - 1)
        With Target.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=wblist
        End With
        Exit Sub
    End If
End If

If Target.Column = wscol Then
    If Target.Row = nslrow Or _
        Target.Row = desrow Or _
        Target.Row = tplrow Or _
        Target.Row = optrow Or _
        Target.Row = parrow Then
        If Target.Offset(0, wbcol - wscol) = "" Then Exit Sub
        wslist = ""
        For i = 1 To Application.Workbooks.Count
            If Workbooks(i).Name = Target.Offset(0, wbcol - wscol).Value Then w = i: Exit For
        Next i
   
        For Each ws In Application.Workbooks(w).Worksheets
            Select Case Target.Row
                Case optrow
                    If Right(ws.Name, 4) = ".out" Then wslist = wslist & ws.Name & ","
                Case parrow
                    If Right(ws.Name, 4) = ".par" Then wslist = wslist & ws.Name & ","
                Case tplrow
                    If Right(ws.Name, 4) = ".tem" Then wslist = wslist & ws.Name & ","
                Case Else
                    wslist = wslist & ws.Name & ","
            End Select
        Next ws
        wslist = Left(wslist, Len(wslist) - 1)
        With Target.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=wslist
        End With
        Exit Sub
    End If
End If
End Sub

Workbook              Worksheet
Design file         c:\prj\dgn\ch1.xls    1L2R
NSL file            c:\prj\data\ch1.xls   1L2R
Parameters file     c:\prog\bin\ch1.xls   lined.par
etc

Select allOpen in new window