Here is the full code for the form.
Option Explicit
Dim rownumber As Integer
Private Sub cboNameAddress_Change()
lblLicence2.Caption = Sheets("Client Details").Cells(cboNameAddress.ListIndex + 1, 2)
txtImportExport.SetFocus
End Sub
Private Sub cmdNextPage_Click()
On Error GoTo 1
activesheet.Next.Select
End
1: Sheets(2).Select
End Sub
Private Sub cmdPreviousPage_Click()
Dim page As Integer
page = activesheet.Index
If page = 1 Then
page = 40
Else
page = page + 1
End If
End Sub
Private Sub cmdAddRecord_Click()
Range("L8:O8").Select
Selection.Copy
Cells(rownumber, 12).Select 'selects row and cell no 12
activesheet.Paste
Range("T8:Z8").Select
Selection.Copy
Cells(rownumber, 20).Select
activesheet.Paste
Cells(rownumber, 1).Value = txtDate
Cells(rownumber, 1) = Format((txtDate.Text), "m/d/yyyy")
Cells(rownumber, 1).HorizontalAlignment = xlCenter
Cells(rownumber, 1).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 2).Value = txtMale
Cells(rownumber, 2) = Format((txtMale.Text), "@")
Cells(rownumber, 2).HorizontalAlignment = xlCenter
Cells(rownumber, 2).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 3).Value = txtFemale
Cells(rownumber, 3) = Format((txtFemale.Text), "@")
Cells(rownumber, 3).HorizontalAlignment = xlCenter
Cells(rownumber, 3).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 4).Value = txtUnknown
Cells(rownumber, 4) = Format((txtUnknown.Text), "@")
Cells(rownumber, 4).HorizontalAlignment = xlCenter
Cells(rownumber, 4).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 5).Value = cboMethod
Cells(rownumber, 5) = Format((cboMethod.Text), "@")
Cells(rownumber, 5).HorizontalAlignment = xlCenter
Cells(rownumber, 5).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 6).Value = cboNameAddress
Cells(rownumber, 6) = Format((cboNameAddress.Text), "@")
Cells(rownumber, 6).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 7).Value = lblLicence2
Cells(rownumber, 7).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber,
.Value = txtImportExport
Cells(rownumber,
.Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 9).Value = txtSaleNumber
Cells(rownumber, 9).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 10).Value = txtFaunaIn
Cells(rownumber, 10) = Format((txtFaunaIn.Text), "@")
Cells(rownumber, 10).HorizontalAlignment = xlCenter
Cells(rownumber, 10).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 11).Value = txtFaunaOut
Cells(rownumber, 11) = Format((txtFaunaOut.Text), "@")
Cells(rownumber, 11).HorizontalAlignment = xlCenter
Cells(rownumber, 11).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 12).HorizontalAlignment = xlCenter
Cells(rownumber, 12).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 13).Select
Cells(rownumber, 13).HorizontalAlignment = xlCenter
Cells(rownumber, 13).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 14).Select
Cells(rownumber, 14).HorizontalAlignment = xlCenter
Cells(rownumber, 14).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 15).Select
Cells(rownumber, 15).HorizontalAlignment = xlCenter
Cells(rownumber, 15).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 16).Value = txtRecordNo
Cells(rownumber, 16).Select
Cells(rownumber, 16).HorizontalAlignment = xlCenter
Cells(rownumber, 16).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 17).Value = txtRecordYear
Cells(rownumber, 17) = Format((txtRecordYear.Text), "@")
Cells(rownumber, 17).Select
Cells(rownumber, 17).HorizontalAlignment = xlCenter
Cells(rownumber, 17).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 18).Value = txtOtherComments
Cells(rownumber, 18).Select
Cells(rownumber, 18).HorizontalAlignment = xlCenter
Cells(rownumber, 18).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells(rownumber, 19).Value = txtPrice
Cells(rownumber, 19) = Format((txtPrice.Text), "$#,##0.00")
Cells(rownumber, 19).HorizontalAlignment = xlCenter
Cells(rownumber, 19).Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub
Private Sub cmdExit_Click()
Unload frmBirdDetails
End Sub
Private Sub cmdNextRecord_Click()
rownumber = rownumber + 1
txtDate.Value = ""
txtImportExport.Value = ""
txtOtherComments.Value = ""
txtRecordNo.Value = ""
txtRecordYear.Value = ""
txtOtherComments.Value = ""
cboMethod.Value = ""
txtPrice.Value = ""
txtSaleNumber.Value = ""
txtMale.Value = ""
txtFemale.Value = ""
txtUnknown.Value = ""
txtFaunaIn.Value = ""
txtFaunaOut.Value = ""
txtDate.SetFocus
End Sub
Private Sub cmdAddRecord2_Click()
Sheets("Client Details").Select
Cells(rownumber, 1).Value = txtNameAddress
Cells(rownumber, 2).Value = txtLicence2
Cells(rownumber, 3).Value = cboAreaCode
Cells(rownumber, 4).Value = txtPhoneNo
Range("A2:D40").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("G8").Select
End Sub
Private Sub cmdExit2_Click()
Unload frmBirdDetails
End Sub
Private Sub cmdNext2_Click()
rownumber = rownumber + 1
txtLicence2.Value = ""
txtNameAddress2.Value = ""
txtPhoneNo2.Value = ""
End Sub
Private Sub txtDate_Change()
If IsNumeric(txtDate) Then
' Date Ok
Else
MsgBox ("Date must be DD/MM/YYYY"), vbOKOnly, ("Incorrect Date")
txtDate = 0
End If
End Sub
Private Sub txtFaunaIn_Change()
If IsNumeric(txtFaunaIn) Then
' Qty Ok
Else
MsgBox ("Qty must be Numeric"), vbOKOnly, ("Text Entered")
txtFaunaIn = 0
End If
End Sub
Private Sub txtFaunaOut_Change()
If IsNumeric(txtFaunaOut) Then
' Qty Ok
Else
MsgBox ("Qty must be Numeric"), vbOKOnly, ("Text Entered")
txtFaunaOut = 0
End If
End Sub
Private Sub txtFemale_Change()
If IsNumeric(txtFemale) Then
' Qty Ok
Else
MsgBox ("Qty must be Numeric"), vbOKOnly, ("Text Entered")
txtFemale = 0
End If
End Sub
Private Sub txtMale_Change()
If IsNumeric(txtMale) Then
' Qty Ok
Else
MsgBox ("Qty must be Numeric"), vbOKOnly, ("Text Entered")
txtMale = 0
End If
End Sub
Private Sub txtPrice_Change()
If IsNumeric(txtPrice) Then
' QTY Ok
Else
MsgBox ("Amount must be Numeric"), vbOKOnly, ("Text Entered")
txtPrice = 0
End If
End Sub
Private Sub txtRecordYear_Change()
If IsNumeric(txtRecordYear) Then
' Qty Ok
Else
MsgBox ("Year must be YYYY"), vbOKOnly, ("Text Entered")
txtRecordYear = 0
End If
End Sub
Private Sub txtUnknown_Change()
If IsNumeric(txtUnknown) Then
' Qty Ok
Else
MsgBox ("Qty must be Numeric"), vbOKOnly, ("Text Entered")
txtUnknown = 0
End If
End Sub
Private Sub UserForm_Initialize()
rownumber = [A65536].End(xlUp).Row
rownumber = rownumber + 1
Dim address As Range
For Each address In Range("'Client Details'!A2:A40")
cboNameAddress.AddItem (address.Value)
Next
Dim licence As Range
For Each licence In Range("'Client Details'!F1:F6")
cboMethod.AddItem (licence.Value)
Next
Dim code As Range
For Each code In Range("'Client Details'!F12:F15")
cboAreaCode.AddItem (code.Value)
Next
lblTitle = Range("F1").Value
txtDate.SetFocus
End Sub