-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathData Entry (Update time).txt
More file actions
136 lines (109 loc) · 3.94 KB
/
Data Entry (Update time).txt
File metadata and controls
136 lines (109 loc) · 3.94 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
Private Sub cmdAdd_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim last_Row As Long
last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
sh.Range("A" & last_Row + 1).Value = "=Row()+1"
sh.Range("B" & last_Row + 1).Value = Me.ComboBox1.Value
sh.Range("C" & last_Row + 1).Value = Me.TextBox1.Value
sh.Range("D" & last_Row + 1).Value = Me.TextBox2.Value
sh.Range("E" & last_Row + 1).Value = Me.TextBox3.Value
sh.Range("F" & last_Row + 1).Value = Now
Me.ComboBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Call Refresh_Data
End Sub
'____________________________________________________________________________________
Private Sub cmdClear_Click()
Me.ComboBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
End Sub
'____________________________________________________________________________________
Private Sub cmdDelete_Click()
If Me.TextBox4.Value = "" Then
MsgBox "Select the record to delete"
Exit Sub
End If
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim Selected_Row As Long
Selected_Row = Application.WorksheetFunction.Match(CLng(Me.TextBox4.Value), sh.Range("A:A"), 0)
'----------------------------------------------
sh.Range("A" & Selected_Row).EntireRow.Delete
'----------------------------------------------
Me.ComboBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Call Refresh_Data
End Sub
'____________________________________________________________________________________
Private Sub cmdSave_Click()
ThisWorkbook.Save
MsgBox " Data Saved"
End Sub
'____________________________________________________________________________________
Private Sub cmdUpdate_Click()
If Me.TextBox4.Value = "" Then
MsgBox "Select the record to update"
Exit Sub
End If
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim Selected_Row As Long
Selected_Row = Application.WorksheetFunction.Match(CLng(Me.TextBox4.Value), sh.Range("A:A"), 0)
sh.Range("B" & Selected_Row).Value = Me.ComboBox1.Value
sh.Range("C" & Selected_Row).Value = Me.TextBox1.Value
sh.Range("D" & Selected_Row).Value = Me.TextBox2.Value
sh.Range("E" & Selected_Row).Value = Me.TextBox3.Value
sh.Range("F" & Selected_Row).Value = Now
Me.ComboBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Call Refresh_Data
End Sub
'____________________________________________________________________________________
'--------------By doble clock on a row, it will bring it up ----------------
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Me.TextBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
Me.ComboBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
Me.TextBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)
'---------------------------------------------------------------------------
End Sub
'____________________________________________________________________________________
Private Sub UserForm_Activate()
With Me.ComboBox1
.Clear
.AddItem ""
.AddItem "Mr."
.AddItem "Mrs."
End With
Call Refresh_Data
End Sub
Sub Refresh_Data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim last_Row As Long
last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
With Me.ListBox1
.ColumnHeads = True
.ColumnCount = 6
.ColumnWidths = "30,40,100,110,70,90"
If last_Row = 1 Then
.RowSource = "Data!A2:F2"
Else
.RowSource = "Data!A2:F" & last_Row
End If
End With
End Sub
'____________________________________________________________________________________