Advice to move/edit array from module to worksheet

[ipbr21054]

Hi,
Spent ages looking for an issue of which ive now sorted so because of that i wish to move/edit the array from its module hiding place to the sheet itself.

Here is the code on the sheet.

Sub Navigate(ByVal Direction As XlSearchDirection)
    Dim i As Integer
    Dim lastRow As Long
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    r = IIf(Direction = xlPrevious, r - 1, r + xlNext)
    
    'ensure value of r stays within data range
    If r < startRow Then r = startRow
    If r > lastRow Then r = lastRow
    
    'get record
    For i = 1 To UBound(ControlNames)
         Me.Controls(ControlNames(i)).Text = IIf(Direction = xlNone, "", ws.Cells(r, i).Text)
         
    Next i
    
    Me.Caption = "Database"
    
    'set enabled status of next previous buttons
    Me.NextRecord.Enabled = IIf(Direction = xlNone, False, r < lastRow)
    Me.PrevRecord.Enabled = IIf(Direction = xlNone, False, r > startRow)
    
    EventsEnable = False
    Me.ComboBoxCustomersNames.ListIndex = IIf(Direction = xlNone, -1, r - startRow)
    EventsEnable = True

End Sub

In the above code the line in Red calls a module.

Function ControlNames() As Variant
ControlNames = Array("txtCustomer", "txtRegistrationNumber", "txtBlankUsed", "txtVehicle", _
                    "txtButtons", "txtKeySupplied", "txtTransponderChip", "txtJobAction", _
                    "txtProgrammerCloner", "txtKeyCode", "txtBiting", "txtChassisNumber", _
                    "txtJobDate", "txtVehicleYear", "txtPaid", "txtInvoiceNumber", "txtNotes", _
                    "txt1Address", "txt2Address", "txt3Address", "txt4Address", "txtPostCode", _
                    "txtTelNumber", "txtSecurityCode", "txtManual", "txtInfo", "txtSupplier", "txtPartNumber", "txtPaymentType", "txtPinCode")
End Function

Im looking to have the code on the sheet & not the module.
Please advise so i can take it from there.

 

[Kyle123]

Like this:

Function ControlNames() As Variant
    ControlNames = Application.Transpose(Sheets("sheetName").Range("a1:a5").Value2)
End Function

 

[ipbr21054]

Would that be on the worksheet with the code or in a module

 

[Kyle123]

In the module, it's a direct replacement for your existing ControlNames function.

In the example, you'd have the control names in range a1 to A5

 

[ipbr21054]

Ok
My issue is I dont want to use all columns from sheet.

Example using your advice is A1 to A20 but I don’t want A6 & A16 to be part of it.

 

[Kyle123]

Then you'll need to loop through the range and build the array from the values you want to include

 

[ipbr21054]

Then you'll need to loop through the range and build the array from the values you want to include

Thats what i need the help with.

Can it not be like a list on the sheet like so.
Me.txtNotes.Value = ws.Range("Q" & rw).Value

The followed by the others i wish to use