Excel VBA: Define a named range.... [solved]

[wpearsall]

hey, i have made a sub, to insert a new invoice record into my "flat file db" ETC, now, i have got it all working, except, i dont know how to 'include' the new item in the FFDB list..

i have got this code so far:

Option Explicit
Sub MakeNewRecord()
 
' MakeNewRecord Macro
' Created by Wayne Pearsall to insert a new invoice record
' in the invoice list when entering a booking query.
 
    If MsgBox("Are you sure you wish to create a new invoice record?" & vbCrLf & _
              "If you have made changes to a record, these changes will be lost!", _
              vbQuestion + vbYesNo, "Insert Invoice Record?") = vbNo Then Exit Sub
 
    ' Set the application's cursor to the hourglass
    ' (so as they know a process is going on)
    Application.Cursor = xlWait
 
    ' Create the variables which are going to hold the
    ' temporary data within this macro
    ' lRecordToInsertAt: The row number that is going to be used as
    '                    row in the cell to select
    ' sCellToInsertAt:   The cell reference which will be selected
    '                    before inserting a new row
    ' lRecordRange:      The limit to select to, before setting the
    '                    invoice_list's named range.
    ' lRecordCount:      The number of records now in the list.
    Dim sCellToInsertAt As String, _
        lRecordToInsertAt As Long, _
        lRecordRange As Long, _
        lRecordCount As Long
        
    ' Get the record count, and add three
    ' (since the list starts AFTER row two)
    lRecordToInsertAt = CLng(Range("record_count").Value) + 3
    ' Put together the cell reference using the row number
    sCellToInsertAt = "'Invoice List'!A" & lRecordToInsertAt
    
    ' insert a row (Which is set to the bottom of the list)
    Range(sCellToInsertAt).EntireRow.Insert
    
    ' Get the new record count
    lRecordCount = CLng(Range("record_count").Value) + 1
    
    ' set the new record count into the record_count cell
    Range("record_count").Value = lRecordCount
    
    ' set the record count in the new invoice record to
    ' the record count (as the invoice #)
    ' (This will be the same cell as we inserted the new line at)
    Range(sCellToInsertAt).Value = lRecordCount
 
    ' calculate the row count
    lRecordRange = lRecordToInsertAt + 1
    
    ' Select the list range
    'Range("A3:A" & lRecordRange).Select
    ' Set the cell naming
    
    ' ----------------------------------------------------------------------
    '
    '
    '   This is where i need to set the selected range's name to "invoice_list"
    '
    '
    ' ----------------------------------------------------------------------
    
    MsgBox "Need to setup the re-setting the named range for ""invoice_list"""
    
    ' Set the receive date of the query into the database sheet
    Range("'Invoice List'!B" & lRecordToInsertAt).Value = Date
    ' set the recieve date to todays date
    Range("C10").Value = Date
    
    With Range("C9")
        ' and then select the pick list
        .Select
        ' and set it to the new invoice record
        .Value = lRecordCount
    End With
    
    ' Set the application's cursor back to the default
    ' (This lets them know that the process is complete)
    Application.Cursor = xlDefault
    
End Sub

i recorded this macro, thinking i could b able to edit the code of it, but i cannot figure out where it sets the cell reference....

'BTW: this refers to the cell:
' ='Invoice Form'!$D$4
 
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 25/10/2003 by Wayne Pearsall
'
'
    ActiveWorkbook.Names.Add Name:="new", RefersToR1C1:="='Invoice Form'!R4C4"
End Sub

can anybody help with this please?

tnx.

[Granty]

ActiveWorkbook.Names.Add Name:="new", RefersToR1C1:="='Invoice Form'!R4C4"

'Invoice Form' is your worksheet and R4C4 (Row 4, Column 4) is the cell address.

[wpearsall]

thanks dood.

dat fixed ma problem