Wednesday, April 26, 2017

Hiighlight Worksheet Data With Textbox Userform Excell VBA



Private Sub ListBox1_Click()
For i = 2 To 13
If Sheet1.Cells(i, 1).Value = Me.ListBox1.Column(0) Then
Sheet1.Cells(i, 1).Interior.Color = vbGreen
Sheet1.Cells(i, 1).Borders.Color = vbRed
Sheet1.Cells(i, 1).Font.Color = vbRed
Else
Sheet1.Cells(i, 1).Interior.Color = vbWhite
Sheet1.Cells(i, 1).Borders.Color = vbBlack
Sheet1.Cells(i, 1).Font.Color = vbBlack
End If
Next i
End Sub

Private Sub UserForm_Initialize()
Me.ListBox1.Selected(0) = True
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
For i = 2 To 13
Sheet1.Cells(i, 1).Interior.Color = vbWhite
Sheet1.Cells(i, 1).Borders.Color = vbBlack
Sheet1.Cells(i, 1).Font.Color = vbBlack
Next i
End Sub

Wednesday, April 19, 2017

Display Post Dated Cheque Transaction In Userform Excell VBA



Private Sub UserForm_Initialize()
Dim i As Long
Me.Label1.Caption = Format(Date, "DD/MMM/YYYY")
Me.ListBox1.AddItem Sheet1.Cells(1, 1).Value
Me.ListBox1.Selected(0) = True
For a = 1 To 5
Me.ListBox1.List(ListBox1.ListCount - 1, a) = Sheet1.Cells(1, a + 1).Value
Next a
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
If Sheet1.Cells(i, "C").Value >= Date And Sheet1.Cells(i, "D").Value = "Bank" Then
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
For b = 1 To 5
Me.ListBox1.List(ListBox1.ListCount - 1, b) = Sheet1.Cells(i, b + 1).Value
Next b
End If

Next i

End Sub

Thursday, April 13, 2017

Transfer textbox Numeric Value To worksheet In words format



Private Sub CommandButton1_Click()
Sheet1.Range("A100000").End(xlUp).Offset(1, 0).Value = Me.TextBox1.Text
Sheet1.Range("A100000").End(xlUp).Offset(0, 1).Value = Me.TextBox2.Text
Sheet1.Range("A100000").End(xlUp).Offset(0, 2).Value = SpellNumber(Me.TextBox2.Text)
End Sub


Thursday, April 6, 2017

Worksheet Data TransferTo Multiple Sheets Match Criteria Every Month



Private Sub CommandButton1_Click()
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For a = 1 To 7
x = Application.WorksheetFunction.CountA(Sheets("Sheet" & a).Range("A:A"))
For b = 1 To 4
If Format(Sheet1.Cells(i, 1).Value, "MMMM") = _
Sheets("Sheet" & a).Range("E1").Value Then
Sheets("sheet" & a).Range("A" & x).End(xlToLeft).Offset(1, b - 1).Value = _
Sheet1.Cells(i, b).Value
End If
Next b
Next a
Next i
End Sub