Hi,
Firstly, make a BACKUP of your workbook.
Starting with your UserForm:
You will need FOUR buttons named as follows:
·
NextRecord
·
PrevRecord
·
UpdateRecord
.
NewRecord
Captions for NewRecord & UpdateRecord buttons will be applied by code – do not attempt to change them.
Place ALL following code in your
UserForms Code Page ensuring that you first delete any existing code with that has the same name.
Code:
Dim ws As WorksheetDim r As Long
Const StartRow As Long = 6
Private Sub NewRecord_Click()
Dim i As Integer
Dim IsNewCustomer As Boolean
IsNewCustomer = CBool(Me.NewRecord.Caption = "New Customer")
ResetButtons IsNewCustomer
Navigate Direction:=IIf(IsNewCustomer, xlNone, xlPrevious)
'if new customer, add new customer number & Date
If IsNewCustomer Then
Me.txtCustomer.Text = Application.Max(ws.Columns(1)) + 1
Me.txtJobDate.Text = Format(Date, "dd/mm/yyyy")
End If
End Sub
Private Sub NextRecord_Click()
Navigate Direction:=xlNext
End Sub
Private Sub PrevRecord_Click()
Navigate Direction:=xlPrevious
End Sub
Private Sub UpdateRecord_Click()
Dim i As Integer
Dim IsNewCustomer As Boolean
Dim msg As String
IsNewCustomer = CBool(Me.UpdateRecord.Caption = "Add Record")
msg = "Record Updated"
If IsNewCustomer Then
'New record - check all fields entered
If Not IsComplete(Form:=Me) Then Exit Sub
r = StartRow
msg = "New Customer Added"
ws.Range("A6").EntireRow.Insert
ResetButtons IsNewCustomer
End If
On Error GoTo myerror
Application.EnableEvents = False
'Add / Update Record
For i = 1 To UBound(ControlNames)
With Me.Controls(ControlNames(i))
'check if date value
If IsDate(.Text) Then
ws.Cells(r, i).Value = DateValue(.Text)
Else
ws.Cells(r, i).Value = .Text
End If
End With
Next i
'tell user what happened
MsgBox msg, 48, msg
myerror:
Application.EnableEvents = True
'something went wrong tell user
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub
Sub ResetButtons(ByVal Status As Boolean)
With Me.NewRecord
.Caption = IIf(Status, "Cancel", "New Customer")
.BackColor = IIf(Status, &HFF&, &H8000000F)
.ForeColor = IIf(Status, &HFFFFFF, &H0&)
End With
Me.UpdateRecord.Caption = IIf(Status, "Add Record", "Update Record")
Me.NextRecord.Enabled = Status
End Sub
Private Sub UserForm_Initialize()
Set ws = ThisWorkbook.Worksheets("Database")
Me.UpdateRecord.Caption = "Update Record"
Me.NewRecord.Caption = "New Customer"
'start at first record
Navigate Direction:=xlFirst
End Sub
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
'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)
End Sub
Place following code In
a STANDARD Module ensuring that you first delete any existing code with that has the same name:
Code:
Option Base 1
Function ControlNames() As Variant
ControlNames = Array("txtCustomer", "txtRegistrationNumber", "txtBlankUsed", "txtVehicle", _
"txtButtons", "txtKeySupplied", "txtTransponderChip", "txtJobAction", _
"txtProgrammerCloner", "txtKeyCode", "txtBiting", "txtChassisNumber", _
"txtJobDate", "txtVehicleYear", "txtPaid")
End Function
Function IsComplete(ByVal Form As Object) As Boolean
Dim i As Integer
For i = 1 To UBound(ControlNames)
IsComplete = CBool(Len(Form.Controls(ControlNames(i)).Text) > 0)
If Not IsComplete Then
MsgBox "Please Complete All Fields", 16, "Entry Required"
Form.Controls(ControlNames(i)).SetFocus
Exit Function
End If
Next i
End Function
Note Option Base 1 statement – This
MUST sit at the
TOP of the Module – do not move or delete it.
When you open the form the first record will be displayed with following:
- PrevRecord Button disabled.
- NextRecord Button Enabled
- UpdateRecord Button with Caption “Update Record”
- NewRecord Button with Caption “New Customer”
Pressing the NewRecord Button will:
- Clear all Textboxes
- Change Caption to “Cancel”
- -Change Backcolor to RED
- Change UpdateRecord Caption to “New Record”
- Add Next Customer Number to txtCustomer TextBox
- Add Date to txtJobDate TextBox
- Disable NextRecord & PrevRecord Buttons
When you complete all other Fields, Press Button displaying “Add Record” which will:
- Insert In Database worksheet, EntireRow in Row 6
- Add New Record to Row 6
- Change NewRecord Button Caption to “New Customer”
- Change UpdateRecord Button Caption “Update Record” & BackColor to ButtonFace
- Enable NextRecord Button
If when entering a new record, any fields are left blank, a msgbox will be displayed informing user that all fields must be completed. The entry will be cancelled & focus placed to the empty textbox.
If all ok, you should now have a New Record in your database.
Must confess, although I robbed much of this code from a project I helped another with, it turned out bit more involved than I first anticipated to just add this additional facility.
To answer your other question regarding msgbox symbols – I know of only those symbols provided in VBA – unless another knows of some trick than can be applied, can only suggest that if you want to use than image, you replace the msgbox prompt with a custom UserForm.
Finally, I note that you were getting a great deal of assistance from another here, even though may not be using that solution, its always appreciated when any assistance give is acknowledged.
Dave
