Dim ws As Worksheet
Dim r As Long, LastRow As Long
Const FirstRow As Long = 2
Dim EventEnable As Boolean, IsNewCustomer As Boolean
'***************************************************************************************
'******************************NAVIGATE BUTTONS*****************************************
Private Sub FirstRecord_Click()
Navigate Direction:=xlFirst
End Sub
Private Sub LastRecord_Click()
Navigate Direction:=xlLastCell
End Sub
Private Sub NextRecord_Click()
Navigate Direction:=xlNext
End Sub
Private Sub PrevRecord_Click()
Navigate Direction:=xlPrevious
End Sub
'***************************************************************************************
'*********************************New Record Button*************************************
Private Sub NewRecord_Click()
Dim i As Integer
IsNewCustomer = CBool(Me.NewRecord.Caption = Me.NewRecord.Tag)
If IsNewCustomer Then Me.RowNumber.Text = FormatNumber(LastRow + 1, 0): Me.CustomerID.SetFocus
ResetButtons IsNewCustomer
Navigate Direction:=IIf(IsNewCustomer, xlNone, xlRowItem)
End Sub
'***************************************************************************************
'*********************************Save Record Button*************************************
Private Sub SaveRecord_Click()
Dim i As Integer
Dim msg As String
'check all fields entered
If Not IsComplete(Form:=Me) Then Exit Sub
If IsNewCustomer Then
NewCustomer Me, ws, FirstRow, r, LastRow
ResetButtons Not IsNewCustomer
msg = "New Customer Added"
Else
AddUpdateRecord Form:=Me, sh:=ws, RecordRow:=r
msg = "Record Updated"
End If
'tell user what happened
MsgBox msg, 48, msg
IsNewCustomer = False
End Sub
'***************************************************************************************
'*********************************Cancel Button*************************************
Private Sub Cancel_Click()
Unload Me
End Sub
'***************************************************************************************
'*********************************Customer ID ComboBox**********************************
Private Sub CustomerID_Change()
If Not EventEnable Or IsNewCustomer Then Exit Sub
r = Me.CustomerID.ListIndex + FirstRow
Navigate Direction:=xlRowItem
End Sub
'***************************************************************************************
'*********************************Row Number TextBox************************************
Private Sub RowNumber_Change()
If Not EventEnable Or IsNewCustomer Then Exit Sub
If IsNumeric(RowNumber.Text) Then
r = Val(RowNumber.Text)
If r >= FirstRow Then Navigate Direction:=xlRowItem
Else
Navigate Direction:=xlNone
End If
End Sub
'***************************************************************************************
'*********************************Navigation********************************************
Sub Navigate(ByVal Direction As XlSearchDirection)
Dim i As Integer
Dim ClearForm As Boolean
EventEnable = False
Select Case Direction
Case xlFirst
r = FirstRow
Case xlPrevious
r = r - 1
Case xlNext
r = r + xlNext
Case xlLastCell
r = LastRow
Case xlRowItem
r = r
Case xlNone
ClearForm = True
End Select
'ensure value of r stays within data range
If r < FirstRow Then r = FirstRow
If r > LastRow Then r = LastRow
'get record
For i = 1 To UBound(ControlNames)
Me.Controls(ControlNames(i)).Text = IIf(ClearForm, "", ws.Cells(r, i).Text)
Next i
NavigationButtonsEnable ClearForm
If Not ClearForm Then Me.RowNumber.Text = FormatNumber(r, 0)
EventEnable = True
End Sub
'***************************************************************************************
'*********************************Button Settings***************************************
Sub NavigationButtonsEnable(Optional ByVal ClearForm As Boolean)
'set enabled status of next previous buttons
Me.NextRecord.Enabled = IIf(ClearForm, False, r < LastRow)
Me.PrevRecord.Enabled = IIf(ClearForm, False, r > FirstRow)
Me.LastRecord.Enabled = Me.NextRecord.Enabled
Me.FirstRecord.Enabled = Me.PrevRecord.Enabled
End Sub
Sub ResetButtons(ByVal Status As Boolean)
With Me.NewRecord
.Caption = IIf(Status, "Cancel", .Tag)
.BackColor = IIf(Status, &HFF&, &H8000000F)
.ForeColor = IIf(Status, &HFFFFFF, &H0&)
End With
With Me.SaveRecord
.Caption = IIf(Status, "Add New Customer", .Tag)
.WordWrap = Status
If .Height < 30 Then .Height = 30
.BackColor = IIf(Status, &HFF00&, &H8000000F)
.ForeColor = IIf(Status, &HFFFFFF, &H0&)
End With
Me.CustomerID.ShowDropButtonWhen = IIf(Status, fmShowDropButtonWhenNever, fmShowDropButtonWhenAlways)
Me.RowNumber.Locked = Status
NavigationButtonsEnable
End Sub
'***************************************************************************************
'**********************************Start Up*********************************************
Private Sub UserForm_Initialize()
Set ws = ThisWorkbook.Worksheets("Customers")
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Me.SaveRecord.Tag = Me.SaveRecord.Caption
Me.NewRecord.Tag = Me.NewRecord.Caption
With Me.CustomerID
.RowSource = ""
.List = ws.Range("A" & FirstRow & ":A" & LastRow).Value
End With
'start at first record
Navigate Direction:=xlFirst
End Sub