Option Explicit
Public iWidth As Integer
Putestblic iHeight As Integer
Public iLeft As Integer
Public iTop As Integer
Public bState As Boolean
Sub Reset()
Dim iRow As Long
iRow = [Counta(Database!A:A)] ' idetifying the last row
With frmForm
.[Link] = ""
.[Link] = ""
.[Link] = False
.[Link] = False
'Default Color
.[Link] = vbWhite
.[Link] = vbWhite
.[Link] = vbWhite
.[Link] = vbWhite
.[Link] = vbWhite
'--------------------------------
.[Link]
'Creating a dynamic name for department
[Link]("A2", [Link]("A" & [Link]).End(xlUp)).Name =
"Dynamic"
.[Link] = "Dynamic"
.[Link] = ""
.[Link] = ""
.[Link] = ""
.[Link] = ""
'Below code are associated with Search Feature - Part 3
Call Add_SearchColumn
[Link]("Database").AutoFilterMode = False
[Link]("SearchData").AutoFilterMode = False
[Link]("SearchData").[Link]
'-----------------------------------------------
.[Link] = 9
.[Link] = True
.[Link] = "30,60,75,40,60,45,55,70,70"
If iRow > 1 Then
.[Link] = "Database!A2:I" & iRow
Else
.[Link] = "Database!A2:I2"
End If
End With
End Sub
Sub Submit()
Dim sh As Worksheet
Dim iRow As Long
Set sh = [Link]("Database")
If [Link] = "" Then
iRow = [Counta(Database!A:A)] + 1
Else
iRow = [Link]
End If
With sh
.Cells(iRow, 1) = "=Row()-1" 'Dynamic Serial Number
.Cells(iRow, 2) = [Link]
.Cells(iRow, 3) = [Link]
.Cells(iRow, 4) = IIf([Link] = True, "Female", "Male")
.Cells(iRow, 5) = [Link]
.Cells(iRow, 6) = [Link]
.Cells(iRow, 7) = [Link]
.Cells(iRow, 8) = [Link]
.Cells(iRow, 9) = [Text(Now(), "DD-MM-YYYY HH:MM:SS")]
End With
End Sub
Sub Show_Form()
[Link]
End Sub
Function Selected_List() As Long
Dim i As Long
Selected_List = 0
For i = 0 To [Link] - 1
If [Link](i) = True Then
Selected_List = i + 1
Exit For
End If
Next i
End Function
Sub Add_SearchColumn()
[Link] = False
With [Link]
.Clear
.AddItem "All"
.AddItem "Employee Id"
.AddItem "Employee Name"
.AddItem "Gender"
.AddItem "Department"
.AddItem "City"
.AddItem "Country"
.AddItem "Submitted By"
.AddItem "Submitted On"
.Value = "All"
End With
[Link] = True
[Link] = ""
[Link] = False
[Link] = False
End Sub
Sub SearchData()
[Link] = False
Dim shDatabase As Worksheet ' Database sheet
Dim shSearchData As Worksheet 'SearchData sheet
Dim iColumn As Integer 'To hold the selected column number in Database sheet
Dim iDatabaseRow As Long 'To store the last non-blank row number available in Database sheet
Dim iSearchRow As Long 'To hold the last non-blank row number available in SearachData sheet
Dim sColumn As String 'To store the column selection
Dim sValue As String 'To hold the search text value
Set shDatabase = [Link]("Database")
Set shSearchData = [Link]("SearchData")
iDatabaseRow = [Link]("Database").Range("A" &
[Link]).End(xlUp).Row
sColumn = [Link]
sValue = [Link]
iColumn = [Link](sColumn, [Link]("A1:I1"), 0)
'Remove filter from Database worksheet
If [Link] = True Then
[Link] = False
End If
'Apply filter on Database worksheet
If [Link] = "Employee Id" Then
[Link]("A1:I" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue
Else
[Link]("A1:I" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="*" & sValue &
"*"
End If
If [Link](3, [Link]("C:C")) >= 2 Then
'Code to remove the previous data from SearchData worksheet
[Link]
[Link] [Link]("A1")
[Link] = False
iSearchRow = [Link]("A" & [Link]).End(xlUp).Row
[Link] = 9
[Link] = "30, 60, 75, 40, 60, 45, 55, 70, 70"
If iSearchRow > 1 Then
[Link] = "SearchData!A2:I" & iSearchRow
MsgBox "Records found."
End If
Else
MsgBox "No record found."
End If
[Link] = False
[Link] = True
End Sub
Function ValidateEntries() As Boolean
ValidateEntries = True
Dim iEmployeeID As Variant
Dim sh As Worksheet
Set sh = [Link]("Print")
iEmployeeID = [Link]
With frmForm
'Default Color
.[Link] = vbWhite
.[Link] = vbWhite
.[Link] = vbWhite
.[Link] = vbWhite
.[Link] = vbWhite
'--------------------------------
If Trim(.[Link]) = "" Then
MsgBox "Please enter Employee ID.", vbOKOnly + vbInformation, "Emp ID"
ValidateEntries = False
.[Link] = vbRed
.[Link]
Exit Function
End If
'Validating Duplicate Entries
If Not [Link]("B:B").Find(what:=iEmployeeID, lookat:=xlWhole) Is Nothing Then
MsgBox "Duplicate Employee ID found.", vbOKOnly + vbInformation, "Emp ID"
ValidateEntries = False
.[Link] = vbRed
.[Link]
Exit Function
End If
If Trim(.[Link]) = "" Then
MsgBox "Please enter Employee Name.", vbOKOnly + vbInformation, "Emp Name"
ValidateEntries = False
.[Link] = vbRed
.[Link]
Exit Function
End If
'Validating Gender
If .[Link] = False And .[Link] = False Then
MsgBox "Please select gender.", vbOKOnly + vbInformation, "Gender"
ValidateEntries = False
Exit Function
End If
If Trim(.[Link]) = "" Then
MsgBox "Please select department name from drop-down.", vbOKOnly + vbInformation,
"Dpartment"
ValidateEntries = False
.[Link] = vbRed
.[Link]
Exit Function
End If
If Trim(.[Link]) = "" Then
MsgBox "Please enter City Name.", vbOKOnly + vbInformation, "City Name"
ValidateEntries = False
.[Link] = vbRed
.[Link]
Exit Function
End If
If Trim(.[Link]) = "" Then
MsgBox "Please enter Country Name.", vbOKOnly + vbInformation, "Country Name"
ValidateEntries = False
.[Link] = vbRed
.[Link]
Exit Function
End If
End With
End Function
Function ValidatePrintDetails() As Boolean
ValidatePrintDetails = True
Dim iEmployeeID As Variant
Dim sh As Worksheet
Set sh = [Link]("Print")
iEmployeeID = [Link]
With frmForm
'Default Color
.[Link] = vbWhite
.[Link] = vbWhite
.[Link] = vbWhite
.[Link] = vbWhite
.[Link] = vbWhite
'--------------------------------
If Trim(.[Link]) = "" Then
MsgBox "Please enter Employee ID.", vbOKOnly + vbInformation, "Emp ID"
ValidatePrintDetails = False
.[Link] = vbRed
.[Link]
Exit Function
End If
If Trim(.[Link]) = "" Then
MsgBox "Please enter Employee Name.", vbOKOnly + vbInformation, "Emp Name"
ValidatePrintDetails = False
.[Link] = vbRed
.[Link]
Exit Function
End If
'Validating Gender
If .[Link] = False And .[Link] = False Then
MsgBox "Please select gender.", vbOKOnly + vbInformation, "Gender"
ValidatePrintDetails = False
Exit Function
End If
If Trim(.[Link]) = "" Then
MsgBox "Please select department name from drop-down.", vbOKOnly + vbInformation,
"Dpartment"
ValidatePrintDetails = False
.[Link] = vbRed
.[Link]
Exit Function
End If
If Trim(.[Link]) = "" Then
MsgBox "Please enter City Name.", vbOKOnly + vbInformation, "City Name"
ValidatePrintDetails = False
.[Link] = vbRed
.[Link]
Exit Function
End If
If Trim(.[Link]) = "" Then
MsgBox "Please enter Country Name.", vbOKOnly + vbInformation, "Country Name"
ValidatePrintDetails = False
.[Link] = vbRed
.[Link]
Exit Function
End If
End With
End Function
Sub Print_Form()
[Link] = False
[Link] = False
Dim sh As Worksheet
Set sh = [Link]("Print")
With frmForm
[Link]("E5").Value = .[Link]
[Link]("E7").Value = .[Link]
[Link]("E9").Value = IIf(.[Link] = True, "Female", "Male")
[Link]("E11").Value = .[Link]
[Link]("E13").Value = .[Link]
[Link]("E15").Value = .[Link]
End With
'Code to Print the form or Export to PDF
[Link] = "$B$2:$I$17"
'[Link] copies:=1, IgnorePrintAreas:=False
[Link] xlTypePDF, [Link] & [Link] &
[Link] & ".pdf"
MsgBox "Employee details have been printed.", vbOKOnly + vbInformation, "Print"
[Link] = True
[Link] = True
End Sub
Sub Maximize_Restore()
If Not bState = True Then
iWidth = [Link]
iHeight = [Link]
iTop = [Link]
iLeft = [Link]
'Code for full screen
With Application
.WindowState = xlMaximized
[Link] = Int(.Width / [Link] * 100)
[Link] = 0
[Link] = .Left
[Link] = .Top
[Link] = .Width
[Link] = .Height
End With
[Link] = "Restore"
bState = True
Else
With Application
.WindowState = xlNormal
[Link] = 100
[Link] = 0
[Link] = iLeft
[Link] = iWidth
[Link] = iHeight
[Link] = iTop
End With
[Link] = "Full Screen"
bState = False
End If
End Sub
FORMS
Option Explicit
Public EnableEvents As Boolean
Private Sub cmbSearchColumn_Change()
If [Link] = False Then Exit Sub
If [Link] = "All" Then
Call Reset
Else
[Link] = ""
[Link] = True
[Link] = True
End If
End Sub
Private Sub cmdDelete_Click()
Dim iRow As Long
If Selected_List = 0 Then
MsgBox "No row is selected.", vbOKOnly + vbInformation, "Delete"
Exit Sub
End If
Dim i As VbMsgBoxResult
i = MsgBox("Do you want to delete the selected record?", vbYesNo + vbQuestion, "Confirmation")
If i = vbNo Then Exit Sub
iRow = [Link]([Link]([Link], 0), _
[Link]("Database").Range("A:A"), 0)
[Link]("Database").Rows(iRow).Delete
Call Reset
MsgBox "Selected record has been deleted.", vbOKOnly + vbInformation, "Deleted"
End Sub
Private Sub cmdEdit_Click()
If Selected_List = 0 Then
MsgBox "No row is selected.", vbOKOnly + vbInformation, "Edit"
Exit Sub
End If
'Code to update the value to respective controls
Dim sGender As String
[Link] =
[Link]([Link]([Link], 0), _
[Link]("Database").Range("A:A"), 0)
[Link] = [Link]([Link], 1)
[Link] = [Link]([Link], 2)
sGender = [Link]([Link], 3)
If sGender = "Female" Then
[Link] = True
Else
[Link] = True
End If
[Link] = [Link]([Link], 4)
[Link] = [Link]([Link], 5)
[Link] = [Link]([Link], 6)
MsgBox "Please make the required changes and click on 'Save' button to update.", vbOKOnly +
vbInformation, "Edit"
End Sub
Private Sub cmdFullScreen_Click()
Call Maximize_Restore
End Sub
Private Sub cmdPrint_Click()
Dim msgValue As VbMsgBoxResult
msgValue = MsgBox("Do you want to print the employee details?", vbYesNo + vbInformation,
"Print")
If msgValue = vbNo Then Exit Sub
If ValidatePrintDetails() = True Then
Call Print_Form
End If
End Sub
Private Sub cmdReset_Click()
Dim msgValue As VbMsgBoxResult
msgValue = MsgBox("Do you want to reset the form?", vbYesNo + vbInformation, "Confirmation")
If msgValue = vbNo Then Exit Sub
Call Reset
End Sub
Private Sub cmdSave_Click()
Dim msgValue As VbMsgBoxResult
msgValue = MsgBox("Do you want to save the data?", vbYesNo + vbInformation, "Confirmation")
If msgValue = vbNo Then Exit Sub
If ValidateEntries() = True Then
Call Submit
Call Reset
End If
End Sub
Private Sub cmdSearch_Click()
If [Link] = "" Then
MsgBox "PLease enter the search value.", vbOKOnly + vbInformation, "Search"
Exit Sub
End If
Call SearchData
End Sub
Private Sub Frame1_Click()
End Sub
Private Sub UserForm_Initialize()
Call Reset
End Sub