Fully Automatic Data Entry userform in Excel VBA || Employee Database in Excel VBA

Click here to download this file

Fully Automatic Data Entry userform in Excel VBA || Employee Database in Excel VBA

Full vba code I shared here for your references. You can follow it 

1. Option Explicit
2. ------------------------------------------------------------------
3. Private Sub UserForm_Initialize()
4. Me.ComboBox1.AddItem "EMP-ID"
5. Me.ComboBox1.AddItem "CONTACT NO"
6. End Sub
7. 
   
8. Private Sub TextBox1_Change()
9. Me.TextBox1 = UCase(Me.TextBox1)
10. End Sub
11. Private Sub TextBox2_Change()
12. Me.TextBox2 = UCase(Me.TextBox2)
13. End Sub
14. 
    
15. Private Sub TextBox3_Change()
16. Me.TextBox3 = UCase(Me.TextBox3)
17. 
    
18. End Sub
19. 
    
20. Private Sub TextBox4_AfterUpdate()
21. Me.TextBox4.Value = Format(Me.TextBox4, "dd-mmm-yy")
22. 
    
23. End Sub
24. 
    
25. 
    
26. Private Sub TextBox5_Change()
27. Me.TextBox5 = UCase(Me.TextBox5)
28. 
    
29. End Sub
30. 
    
31. Private Sub TextBox6_AfterUpdate()
32. Me.TextBox6.Text = Format(Me.TextBox6.Text, "000-000-0000")
33. End Sub
34. 
    
35. 
    
36. Private Sub TextBox8_Change()
37. Me.TextBox8 = UCase(Me.TextBox8)
38. 
    
39. End Sub
40. 
    
41. Private Sub TextBox9_AfterUpdate()
42. Me.TextBox9.Value = Format(Me.TextBox9, "dd-mmm-yy")
43. 
    
44. End Sub
45. ===============================================
46. Private Sub CommandButton1_Click()
47. ''**************CODES for Add Button****************
48. 
    
49. If Me.Image1.Picture Is Nothing Then
50. MsgBox "Please Insert Image First"
51. Exit Sub
52. End If
53. '----------DEFINE WORKSHEET WHERE YOU ARE WORKING---
54. Dim sh As Worksheet
55. Set sh = Sheets("SHEET1")
56. sh.Unprotect "121"
57. '---------------TO FIND OUT LAST ROW---------------
58. Dim i As Long
59. i = sh.Range("A" & Rows.Count).End(xlUp).Row + 1
60. 
    
61. '' ----------NOW PREVENT DUPLICATE ENTRY-----------
62. Dim m As Long
63. For m = 3 To i
64. If Application.WorksheetFunction.CountIf(sh.Range("G3", "G" & m), Me.TextBox6.Value) > 0 Then
65. MsgBox "DUPLICATE ITEM, PLEASE TRY ANOTHER"
66. Exit Sub
67. End If
68. Next m
69. '-----------------------------------------------
70. '' mandatory field
71. Dim n As Integer
72. For n = 1 To 9
73. 
    
74. If Me("TextBox" & n).Value = "" Then
75. MsgBox "All field are mandatory"
76. Exit Sub
77. End If
78. Next n
79. 
    
80. '------------TRANSFERING DATA---------------------
81. '' emp id
82. sh.Cells(i, 1) = Me.TextBox1.Value & "_00" & i
83. Dim x As Integer
84. For x = 1 To 9
85. sh.Cells(i, x + 1) = Me("TextBox" & x).Value
86. Next x
87. ''''----------Call image_transfer------------------
88. Call image_transfer
89. '-------------------------------------------------
90. sh.Protect "121"
91. 
    
92. MsgBox "DATA TRANSFERED SUCCESSFUL"
93. '------------RESET THE FORM-----------------------
94. Call CommandButton5_Click
95. 
    
96. End Sub
97. ===============================================
98. Private Sub CommandButton2_Click()
99. '***************CODES for Update*****************
100. 
     
101. Dim sh As Worksheet
102. Set sh = Sheets("Sheet1")
103. '------------------------------------------------
104. sh.Unprotect "121"
105. '----TO FIND OUT THAT ROW WHICH IS BEING EDITED------
106. Dim i As Long
107. For i = 3 To sh.Range("A" & Rows.Count).End(xlUp).Row
108. '----------------------------------------------------
109. '-----DATA WILL SEARCH BY THE VALUE OF TEXTBOX10-----
110. If Me.TextBox10.Value = sh.Cells(i, 1) Then
111. '' Here if you want to search from HSN CODE i.e Column B then 2,
112. Dim x As Integer
113. '------NOW EDIT WILL MADE----------------------------
114. For x = 1 To 9
115. sh.Cells(i, x + 1) = Me("TextBox" & x).Value
116. On Error Resume Next
117. '--------PREVIOUS PIC WILL BE DELETED----------------
118. ActiveSheet.Shapes.Range(Array("IMAGE_" & i)).Select
119. Selection.Delete
120. Next x
121. End If
122. Next i
123. '------------------------------------------------------
124. Call image_transfer 'NOW NEW PIC WILL BE PLACED
125. '------------------------------------------------------
126. sh.Protect "121"
127. 
     
128. MsgBox "EDITED SUCCESSFUL"
129. '-----------------------------------------------------
130. Call CommandButton5_Click 'RESET THE FORM
131. 
     
132. End Sub
133. ===================================================
134. Private Sub CommandButton3_Click()
135. '' FOR DELETE ROW
136. Dim sh As Worksheet
137. Set sh = Sheets("Sheet1")
138. sh.Unprotect "121"
139. 
     
140. '----TO FIND OUT THAT ROW WHICH IS BEING EDITED------
141. Dim i As Long
142. For i = 3 To sh.Range("A" & Rows.Count).End(xlUp).Row
143. '---------------DELETE SELECTED ROW------------------
144. If Me.TextBox10.Value = sh.Cells(i, 1) Then
145. sh.Cells(i, 1).EntireRow.Delete
146. '------------- TO delete image----------------------
147. On Error Resume Next
148. ActiveSheet.Shapes.Range(Array("IMAGE_" & i)).Select
149. Selection.Delete
150. End If
151. Next i
152. '--------------------------------------------------
153. sh.Protect "121"
154. 
     
155. MsgBox "DELETED SUCCESSFUL"
156. Call CommandButton5_Click 'RESET THE FORM
157. Me.TextBox10.Enabled = True
158. Me.TextBox10 = ""
159. Me.CommandButton4.Enabled = True 'ENABLE CLICK TO EDIT BUTTON
160. End Sub
161. ==============================================
162. Private Sub CommandButton4_Click()
163. '************click to Edit Button**********************
164. If Me.TextBox10 = "" Then Exit Sub
165. '---------- DISABLED ADD BUTTON---------------------
166. Me.CommandButton1.Enabled = False
167. 
     
168. '----SELECT THE ROW OF LISTBOX------------------
169. Dim r As Long
170. For r = 0 To Me.ListBox1.ListCount - 1
171. If Me.ListBox1.Selected(r) = True Then
172. 
     
173. '-------------Data Transfer from ListBox To TextBoxes----
174. Dim x As Integer
175. For x = 1 To 9
176. Me("TextBox" & x).Value = Me.ListBox1.Column(x)
177. Next x
178. End If
179. Next r
180. '------------------------------------------------------
181. On Error Resume Next
182. '-----EDIT, DELETE WILL MADE FOR TEXTBOX10'S VALUE------
183. Me.TextBox10.Value = Me.ListBox1.Column(0)
184. 
     
185. Me.TextBox10.Enabled = False
186. '----------- LOAD searchED image---------------------
187.  Dim ipath As String
188. ipath = "C:\MY FOLDER\" & Me.TextBox10 & ".jpg"
189. Me.Image1.Picture = LoadPicture(ipath)
190. Me.Image1.PictureSizeMode = fmPictureSizeModeStretch
191. '' Disable "Click To Edit" Button
192. Me.CommandButton4.Enabled = False
193. End Sub
194. ==================================================
195. Private Sub CommandButton5_Click()
196. '************RESET BUTTON********************
197. Dim x As Integer
198. For x = 1 To 10
199. Me("TextBox" & x).Value = ""
200. Next x
201. Me.CommandButton4.Enabled = True
202. On Error Resume Next
203. Me.Image1.Picture = LoadPicture(vbNullString)
204. End Sub
205. ==============================================
206. Private Sub CommandButton6_Click()
207. '*************ADD PHOTO BUTTON***************************
208. On Error Resume Next
209. Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
210. Application.FileDialog(msoFileDialogOpen).Show '' to show browse box
211. '-----------TO SELECT IMAGE SOURCE PATH---------------------
212. Dim filepath As String
213. filepath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
214. '------------LOAD FROM SOURCE PATH-------------------
215. Me.Image1.Picture = LoadPicture(filepath)
216. Me.Image1.PictureSizeMode = fmPictureSizeModeStretch
217. '------------------SUBSCRIBE MY CHANNEL-------------------
218. End Sub
219. 
     
220. Private Sub CommandButton7_Click()
221. Unload Me
222. End Sub
223. =======================================================
224. Private Sub TextBox10_Change()
225. '********* LOAD LISTBOX WHEN WE TYPING ON THIS TEXTBOX************
226. '-------FORMAT TEXTBOX AS UPPER/LOWER CASE---------------------
227. Me.TextBox10.Value = UCase(Me.TextBox10)
228. '--------CLEAR LISTBOX EVERYTIME WHEN CLICK-------------------
229. Me.ListBox1.Clear
230. '------------------------------------------------------
231. Dim SHT As Worksheet
232. Set SHT = Sheets("SHEET1")
233. Dim b, a As Integer
234. '-----TO SELECT COLUMN NUMBER--FROM COMBOBOX--------
235. If Me.ComboBox1.Value = "EMP-ID" Then b = 1
236. If Me.ComboBox1.Value = "CONTACT NO" Then b = 7
237. '--------TO FIND OUT THE ROW NUMBER----------------
238. Dim i, x As Long
239. For i = 3 To SHT.Range("A" & Rows.Count).End(xlUp).Row
240. '----SHOW MSGBOX IF COMBOBOX BLANK--------------
241. If Me.ComboBox1.Value = "" Then
242. MsgBox "SELECT CRITERIA"
243. Exit Sub
244. End If
245. '------------------------------------------------
246. '-------------TO COUNT CHARECTERS OF COLUMN'S CELL-------
247. For x = 1 To Len(SHT.Cells(i, b))
248. '------TO COUNT TEX------------------------------
249. a = Me.TextBox10.TextLength
250. '------NOW APLLY CONDITION-------------------------
251. If UCase(Mid(SHT.Cells(i, b), x, a)) = Me.TextBox10 And Me.TextBox10 <> "" Then
252. 'NOW LOAD LISBOX BASED ON ABOVE COND----------------------------
253. With Me.ListBox1
254. .AddItem SHT.Cells(i, 1)
255. .List(.ListCount - 1, 1) = SHT.Cells(i, 2)
256. .List(.ListCount - 1, 2) = SHT.Cells(i, 3)
257. .List(.ListCount - 1, 3) = SHT.Cells(i, 4)
258. .List(.ListCount - 1, 4) = SHT.Cells(i, 5)
259. .List(.ListCount - 1, 5) = SHT.Cells(i, 6)
260. .List(.ListCount - 1, 6) = SHT.Cells(i, 7)
261. .List(.ListCount - 1, 7) = SHT.Cells(i, 8)
262. .List(.ListCount - 1, 8) = SHT.Cells(i, 9)
263. .List(.ListCount - 1, 9) = SHT.Cells(i, 10)
264. End With
265. End If
266. Next x
267. Next i
268. '------------NSUTRADHAR--------------------------------
269. End Sub
270. ===========================================
271. Sub RectangleRoundedCorners1_Click()
272. Emp_Database_frm.Show
273. End Sub
274. ===========================================
275. Sub image_transfer()
276. If Emp_Database_frm.TextBox1 = "" Then Exit Sub
277. Dim sh As Worksheet
278. Set sh = Sheets("Sheet1")
279. Dim i As Long
280. i = sh.Range("A" & Rows.Count).End(xlUp).Row
281. '' to copy image from userform & paste it with resize
282. Dim filepath As String
283. filepath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
284. sh.Cells(i, "K").Select
285. sh.Range("K" & i).RowHeight = 40
286. sh.Range("K" & i).ColumnWidth = 13
287. sh.Cells(i, "K") = sh.Pictures.Insert(filepath).Select
288. Selection.ShapeRange.Name = "IMAGE_" & i
289. Selection.ShapeRange.ScaleWidth 0.3, msoFalse, msoScaleFromTopLeft
290. Selection.ShapeRange.ScaleHeight 0.3, msoFalse, msoScaleFromTopLeft
291. '' to copy the image and save it on a folder
292.  Dim ipath As String
293. ipath = "C:\MY FOLDER\" & Emp_Database_frm.TextBox1 & "_00" & i & ".jpg"
294.  FileCopy filepath, ipath
295. '' now paste the hyperlink of the image
296.  sh.Cells(i, "L").Select
297.  ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:=ipath, TextToDisplay:=Emp_Database_frm.TextBox1.Value
298. Emp_Database_frm.Image1.Picture = LoadPicture(vbNullString)
299. End Sub

For Support and FeedBack

You can share your feedback through this blogs or can contact through email if you have any queries regarding any excel related file. Thanks for visiting this blogs.