Option Explicit
Public gTextBox As Byte
'maximise screen for window
Private Sub UserForm_Initialize()
With Application
.WindowState = xlMaximized
Zoom = Int(.Width / Me.Width * 80)
Width = .Width
Height = .Height
End With
End Sub
'unload form
Private Sub CancelButton_Click()
Unload UserForm1
End Sub
'only one of checkbox 1,2,3, or 4 at one time
Private Sub CheckBox1_Click()
If CheckBox1.value = True Then CheckBox2.value = False
If CheckBox1.value = True Then CheckBox3.value = False
If CheckBox1.value = True Then CheckBox4.value = False
End Sub
Private Sub CheckBox2_Click()
If CheckBox2.value = True Then CheckBox1.value = False
If CheckBox2.value = True Then CheckBox3.value = False
If CheckBox2.value = True Then CheckBox4.value = False
End Sub
Private Sub CheckBox3_Click()
If CheckBox3.value = True Then CheckBox1.value = False
If CheckBox3.value = True Then CheckBox2.value = False
If CheckBox3.value = True Then CheckBox4.value = False
End Sub
Private Sub CheckBox4_Click()
If CheckBox4.value = True Then CheckBox1.value = False
If CheckBox4.value = True Then CheckBox2.value = False
If CheckBox4.value = True Then CheckBox3.value = False
End Sub
'only one of checkbox 6,7, or 8 chosen
Private Sub CheckBox6_Click()
If CheckBox6.value = True Then CheckBox7.value = False
If CheckBox6.value = True Then CheckBox8.value = False
End Sub
Private Sub CheckBox7_Click()
If CheckBox7.value = True Then CheckBox6.value = False
If CheckBox7.value = True Then CheckBox8.value = False
If CheckBox6.value = False And CheckBox8.value = False Then CheckBox7.value = True
End Sub
Private Sub CheckBox8_Click()
If CheckBox8.value = True Then CheckBox6.value = False
If CheckBox8.value = True Then CheckBox7.value = False
End Sub
'clear form
Private Sub CommandButton1_Click()
Dim Z As Control, Y As Control
For Each Z In UserForm1.Controls
If TypeName(Z) = "TextBox" Then
Z.value = ""
End If
Next Z
For Each Y In UserForm1.Controls
If TypeName(Y) = "CheckBox" Then
Y.value = False
End If
Next Y
TextBox1.SetFocus
End Sub
Private Sub CommandButton2_Click()
If TextBox7.Text <> "" Then ActiveWorkbook.Sheets(TextBox7.Text).Activate
Unload UserForm1
End Sub
Private Sub CommandButton10_Click()
Addvalue 9
End Sub
Private Sub CommandButton11_Click()
Addvalue 7
End Sub
Private Sub CommandButton12_Click()
Addvalue 8
End Sub
Private Sub CommandButton13_Click()
Addvalue 5
End Sub
Private Sub CommandButton14_Click()
TextBox10.Text = TextBox10.Text & "."
End Sub
Private Sub CommandButton15_Click()
TextBox1.Text = ""
TextBox1.Text = TextBox1.Text & "IE"
End Sub
Private Sub CommandButton17_Click()
TextBox1.Text = ""
TextBox1.Text = TextBox1.Text & "UK"
End Sub
Private Sub CommandButton3_Click()
'save button with data validation and clear after save
Dim lRow As Long, myDate As Date
Dim Z As Control, Y As Control
Dim res As VbMsgBoxResult
Dim length As Integer
Dim length1 As Integer
Dim length2 As Integer
If CheckBox1 = True And TextBox10.value = vbNullString Then
res = MsgBox("You haven't entered a cheque amount, Do you still want to save?", vbYesNo + vbQuestion)
If res = vbNo Then Exit Sub
End If
If TextBox3.value = vbNullString And TextBox5.value = vbNullString And CheckBox1 = False And CheckBox2 = False And CheckBox3 = False And CheckBox4 = False Then
res = MsgBox("No payment method entered, Do you still want to save?", vbYesNo + vbQuestion)
If res = vbNo Then Exit Sub
End If
If TextBox2.value = vbNullString Then
res = MsgBox("No herd number entered, Do you still want to save?", vbYesNo + vbQuestion)
If res = vbNo Then Exit Sub
End If
If TextBox9.value = vbNullString Then
res = MsgBox("No sample number entered, Do you still want to save?", vbYesNo + vbQuestion)
If res = vbNo Then Exit Sub
End If
length = Len(TextBox9.value)
If length = 1 Then TextBox9.value = "0" + TextBox9.value
length1 = Len(TextBox6.value)
If length1 = 1 Then TextBox6.value = "0" + TextBox6.value
length2 = Len(TextBox1.value)
If length2 <> 14 Then res = MsgBox("You haven't entered a complete tag number, Do you still want to save?", vbYesNo + vbQuestion)
If res = vbNo Then Exit Sub
If TextBox9.value > TextBox6.value And TextBox6.value <> vbNullString Then
res = MsgBox("Sample number greater than prepay balance, Do you still want to save?", vbYesNo + vbQuestion)
If res = vbNo Then Exit Sub
End If
With Workbooks("Batch Payment Record").Worksheets("Sheet1")
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(lRow + 1, "A") = Now()
.Cells(lRow + 1, "B") = TextBox2.Text
.Cells(lRow + 1, "C") = TextBox1.Text
.Cells(lRow + 1, "D") = TextBox9.Text
.Cells(lRow + 1, "E") = TextBox3.Text
.Cells(lRow + 1, "F") = TextBox6.Text
.Cells(lRow + 1, "G") = TextBox4.Text
.Cells(lRow + 1, "H") = TextBox5.Text
.Cells(lRow + 1, "I") = TextBox7.Text
.Cells(lRow + 1, "K") = TextBox10.Text
.Cells(lRow + 1, "L") = TextBox11.Text
If CheckBox1.value = True Then .Cells(lRow + 1, "J") = "Cheque"
If CheckBox2.value = True Then .Cells(lRow + 1, "J") = "Online"
If CheckBox3.value = True Then .Cells(lRow + 1, "J") = "Card"
If CheckBox4.value = True Then .Cells(lRow + 1, "J") = "Cash"
If CheckBox5.value = True Then .Cells(lRow + 1, "M") = "Blood"
End With
For Each Z In UserForm1.Controls
If TypeName(Z) = "TextBox" Then
Z.value = ""
End If
Next Z
For Each Y In UserForm1.Controls
If TypeName(Y) = "CheckBox" Then
Y.value = False
End If
Next Y
TextBox1.SetFocus
End Sub
Private Sub Image1_Click()
End Sub
Private Sub Image2_Click()
End Sub
Private Sub Image3_Click()
End Sub
Private Sub CommandButton4_Click()
Addvalue 1
End Sub
Private Sub CommandButton5_Click()
Addvalue 6
End Sub
Private Sub CommandButton6_Click()
Addvalue 3
End Sub
Private Sub CommandButton7_Click()
Addvalue 4
End Sub
Private Sub CommandButton8_Click()
Addvalue 0
End Sub
Private Sub CommandButton9_Click()
Addvalue 2
End Sub
Private Sub Frame3_Click()
End Sub
Private Sub Label10_Click()
End Sub
Private Sub Label11_Click()
End Sub
Private Sub Label13_Click()
End Sub
Private Sub Label3_Click()
End Sub
Private Sub OKButton_Click()
End Sub
Private Sub Label7_Click()
End Sub
Private Sub Label8_Click()
End Sub
Private Sub Label9_Click()
End Sub
'sarch excel form for data
Private Sub TextBox1_Change()
Dim x As Variant, Y As Variant, CompareRange1 As Variant, CompareRange2 As Variant
With Workbooks("Prepayment File").Worksheets("Summary")
TextBox1.value = LTrim(TextBox1.value)
TextBox2.value = Left(TextBox1.value, 9)
Set CompareRange1 = Range("A2:A2000")
For Each x In CompareRange1
If TextBox2.Text = x Then TextBox3 = "Prepaid Account"
If TextBox2.Text = x Then TextBox7 = x.Offset(0, 2)
If TextBox2.Text = x Then TextBox6.value = x.Offset(0, 1)
If TextBox1.Text = "" Then TextBox3.Text = ""
If TextBox1.Text = "" Then TextBox6.Text = ""
Next x
Set CompareRange2 = Range("I2:I10000")
For Each Y In CompareRange2
If TextBox2.Text = Y Then TextBox4.Text = Y.Offset(0, 1)
If TextBox2.Text = Y Then TextBox5.Text = Y.Offset(0, 2)
Next Y
End With
gTextBox = 1
End Sub
Private Sub TextBox10_Change()
End Sub
Private Sub TextBox11_Change()
End Sub
Private Sub TextBox2_Change()
End Sub
Private Sub TextBox3_Change()
End Sub
Private Sub TextBox4_Change()
End Sub
Private Sub TextBox5_Change()
End Sub
Private Sub TextBox6_Change()
Dim entry As Integer
If TextBox6.Text <> vbNullString Then
entry = Int(TextBox6.value)
End If
If TextBox6.value <= 0 Then
TextBox6.ForeColor = &H80000008
TextBox6.BackColor = &HFF&
Else
TextBox6.ForeColor = &H80000008
TextBox6.BackColor = &HFF00&
End If
End Sub
Private Sub TextBox7_Change()
End Sub
Private Sub TextBox8_Change()
End Sub
Private Sub TextBox9_Change()
Dim entry As Integer
If TextBox9.Text <> vbNullString Then
entry = Int(TextBox9.Text)
End If
Option Explicit
gTextBox = 9
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub TextBox9_Enter()
gTextBox = 9
End Sub
Private Sub TextBox10_Enter()
gTextBox = 10
End Sub
Sub Addvalue(ByVal bButtonNumber As Byte)
Select Case gTextBox
Case 1
TextBox1.value = Val(TextBox1.value) + bButtonNumber
Case 2
TextBox9.value = Val(TextBox9.value) + bButtonNumber
Case 3
TextBox10.value = Val(TextBox10.value) + bButtonNumber
End Select
End Sub