Friday, June 30, 2017

Worksheet Advance Filter By Combobox Excel VBA



Combobox Fill Data

Private Sub ComboBox1_DropButtonClick()
Me.ComboBox1.ListFillRange = "Name"
End Sub

Advance Filter

Private Sub ComboBox1_Change()
On Error Resume Next
Sheet1.Range("E2") = Me.ComboBox1.Value
a = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
Sheet1.Range("A" & 1, "D" & a).AdvancedFilter xlFilterInPlace, _
Sheet1.Range("E1:E2")
End Sub

Show All Data

Private Sub CommandButton1_Click()
On Error Resume Next
ActiveSheet.ShowAllData
End Sub


Saturday, June 24, 2017

Vlookup Multiple Col_Index_Num InVBA Userform Excel





Private Sub UserForm_Initialize()
Me.ComboBox1.RowSource = "name"
End Sub

Private Sub ComboBox1_Change()
Dim i As Long
i = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For a = 1 To 4
Me("Textbox" & a).Value = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, _
Sheet1.Range("A" & 2, "E" & i), a + 1, 0)
Next a
End Sub

Saturday, June 10, 2017

Create Employee Present Data Base In Excell Worksheet VBA




Private Sub UserForm_Initialize()
Me.TextBox1.Text = Format(Date, "DD/MMM/YYYY")
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
Next i
End Sub

Private Sub SpinButton1_SpinDown()
On Error Resume Next
Me.TextBox1.Text = CDate(Me.TextBox1.Text) - 1
For i = 0 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(i) = False
Next i
End Sub

Private Sub SpinButton1_SpinUp()
On Error Resume Next
Me.TextBox1.Text = CDate(Me.TextBox1.Text) + 1
For i = 0 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(i) = False
Next i
End Sub

Private Sub CommandButton1_Click()
Dim i As Long, x As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For x = 0 To Me.ListBox1.ListCount - 1
For a = 2 To 18
If Me.ListBox1.Selected(x) = True Then
If Sheet1.Cells(i, 1).Value = Me.ListBox1.List(x, 0) And _
Sheet1.Cells(1, a).Value = CDate(Me.TextBox1.Text) Then
Sheet1.Cells(i, a).Value = "Present"
End If
End If
Next a
Next x
Next i
End Sub