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
