Saturday, March 25, 2017

Display Monthly Transaction Listbox From Worksheet Excell VBA





Private Sub UserForm_Initialize()
Me.ComboBox1.Value = Format(Date, "YYYY")
Me.ComboBox2.Value = Format(Date, "MMMM")
For a = 0 To 5
Me.ComboBox1.AddItem Format(Date, "YYYY") - a
Next a
For b = 0 To 11
c = Application.WorksheetFunction.EoMonth _
("1" & "/" & "January" & "/" & Me.ComboBox1.Value, b)
Me.ComboBox2.AddItem Format(c, "MMMM")
Next b
End Sub

Me.ListBox1.Selected(0) = True
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
b = Application.WorksheetFunction.EDate _
("1" & "/" & Me.ComboBox2.Value & "/" & Me.ComboBox1.Value, 0)
c = Application.WorksheetFunction.EoMonth _
("1" & "/" & Me.ComboBox2.Value & "/" & Me.ComboBox1.Value, 0)
If Sheet1.Cells(i, 1).Value >= CDate(b) And Sheet1.Cells(i, 1).Value <= CDate(c) Then
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
For d = 1 To 4
Me.ListBox1.List(ListBox1.ListCount - 1, d) = Sheet1.Cells(i, d + 1).Value & ".00"
Next d
End If
Next i
End Sub

Wednesday, March 22, 2017

Create Transaction Form In Userform Excell VBA





Userfform1

Private Sub TextBox1_AfterUpdate()
On Error Resume Next
Me.TextBox1 = CDate(Me.TextBox1)
End Sub
Private Sub TextBox2_Enter()
UserForm2.Show
End Sub

Private Sub TextBox3_Enter()
UserForm3.Show
End Sub

Private Sub CommandButton1_Click()
Dim i As Long
i = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For x = 1 To 4
Sheet1.Range("A" & i).End(xlToLeft).Offset(1, x - 1).Value = _
Me("textbox" & x).Value
Next x
Me.TextBox1 = ""
Me.TextBox2 = ""
Me.TextBox3 = ""
Me.TextBox4 = ""
Me.TextBox1.SetFocus

Userform2
Private Sub UserForm_Initialize()
Me.ListBox1.List = Array("Cash", "Bank", "Creditcard")
Me.ListBox1.Selected(0) = True
End Sub
Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
UserForm1.TextBox2.Text = Me.ListBox1.Column(0)
Unload Me
UserForm1.TextBox2.SetFocus
End Sub

Userform3
Private Sub UserForm_Initialize()
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))
Me.ListBox1.AddItem Sheet2.Cells(i, 1).Value
Next i
Me.ListBox1.Selected(0) = True
End Sub

Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
UserForm1.TextBox3.Text = Me.ListBox1.Column(0)
Unload Me
UserForm1.TextBox3.SetFocus
End Sub


Saturday, March 18, 2017

Display Contact In Listbox Userform VBA Excell





Private Sub UserForm_Initialize()
'listbox additem
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
Next i
Me.ListBox1.Selected(0) = True
End Sub

Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'search contact
Me.ListBox1.Clear
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For x = 1 To 5
If Sheet1.Cells(i, 1).Value = Me.TextBox1.Text Then
Me.ListBox1.AddItem Sheet1.Cells(1, x).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(i, x).Value
End If
Next x
Next i
Me.ListBox1.Selected(0) = True
End Sub



Private Sub CommandButton1_Click()

'for back button

Me.ListBox1.Clear

Call UserForm_Initialize

End Sub



Private Sub ListBox1_Click()

'textbox fill

Me.TextBox1.Text = Me.ListBox1.Column(0)

End Sub

Monday, March 13, 2017

Calculate Hour In Userform Excell VBA





Private Sub CommandButton1_Click()

Sheet1.Range("A100000").End(xlUp).Offset(1, 0).Value = Me.ComboBox1.Text

Sheet1.Range("A100000").End(xlUp).Offset(0, 1).Value = Me.TextBox1.Text

Sheet1.Range("A100000").End(xlUp).Offset(0, 2).Value = Me.TextBox2.Text

Sheet1.Range("A100000").End(xlUp).Offset(0, 3).Value = _

Abs(TimeValue(Me.TextBox2) - TimeValue(Me.TextBox1)) * 24

End Sub


Friday, March 10, 2017

Transfer Duplicate Value With Count Numbers Excell VBA





Private Sub CommandButton1_Click()

a = Application.WorksheetFunction.CountIf(Sheet1.Range("A:A") _

, "*" & Me.TextBox1.Text & "*")

If a >= 1 Then

Sheet1.Range("A100000").End(xlUp).Offset(1, 0).Value = Me.TextBox1.Text & a

Sheet1.Range("A100000").End(xlUp).Offset(0, 1).Value = Me.TextBox2.Text

Else

Sheet1.Range("A100000").End(xlUp).Offset(1, 0).Value = Me.TextBox1.Text

Sheet1.Range("A100000").End(xlUp).Offset(0, 1).Value = Me.TextBox2.Text

End If

End Sub

Wednesday, March 8, 2017

How Can Use Togglebutton InUserform Excell VBA





Private Sub ToggleButton1_Click()

On Error Resume Next

For i = 1 To 5000

If Me.ToggleButton1.Value = True Then

Me.ToggleButton1.Caption = "OFF"

Me.ToggleButton1.BackColor = vbRed

Else

Me.ToggleButton1.Caption = "ON"

Me.ToggleButton1.BackColor = vbGreen

End If

If Me.TextBox1.Text <= 4999 And Me.ToggleButton1.Caption = "OFF" Then

Me.TextBox1.Text = Me.TextBox1.Text + 1

DoEvents

End If

Next i

Me.ToggleButton1.Caption = "ON"

Me.ToggleButton1.Value = False

End Sub

Sunday, March 5, 2017

Open PDF File In Userform Excell VBA





Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

'For Open PDF file In Excell Userform

On Error Resume Next

ThisWorkbook.FollowHyperlink "C:\Invoice\" & Me.ListBox1.Column(2) & ".pdf"

End Sub

Private Sub UserForm_Initialize()

'For Listbox Fill Data

Dim i As Long

For i = 1 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))

Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value

For a = 1 To 4

Me.ListBox1.List(ListBox1.ListCount - 1, a) = Sheet1.Cells(i, a + 1).Value

Next a

Next i

Me.ListBox1.Selected(0) = True

End Sub

Saturday, March 4, 2017

Listbox Find Paid Invoice And Outstanding Invoice Userform Excell VBA





Private Sub OptionButton1_Click()

Me.ListBox1.Clear

Me.ListBox1.AddItem "Date"

Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "Customer Name"

Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "Invoice NO"

Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Amount"

Me.ListBox1.List(ListBox1.ListCount - 1, 4) = "Paid Amount"

Me.ListBox1.List(ListBox1.ListCount - 1, 5) = "Balance"

Me.ListBox1.Selected(0) = True

Dim i As Long

For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))

If Sheet1.Cells(i, "F").Value < 1 Then

Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value

For a = 1 To 5

Me.ListBox1.List(ListBox1.ListCount - 1, a) = Sheet1.Cells(i, a + 1).Value

Next a

End If

Next i

End Sub

Friday, March 3, 2017

Listbox Find Paid Invoice And Outstanding Invoice Userform Excell VBA





Private Sub OptionButton1_Click()

Me.ListBox1.Clear

Me.ListBox1.AddItem "Date"

Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "Customer Name"

Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "Invoice NO"

Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Amount"

Me.ListBox1.List(ListBox1.ListCount - 1, 4) = "Paid Amount"

Me.ListBox1.List(ListBox1.ListCount - 1, 5) = "Balance"

Me.ListBox1.Selected(0) = True

Dim i As Long

For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))

If Sheet1.Cells(i, "F").Value < 1 Then

Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value

For a = 1 To 5

Me.ListBox1.List(ListBox1.ListCount - 1, a) = Sheet1.Cells(i, a + 1).Value

Next a

End If

Next i

End Sub

Wednesday, March 1, 2017

Create Invoice And Save PDF Format Excell VBA





Private Sub CommandButton1_Click()

'for invoice number

Sheet1.Range("B4").Value = Sheet1.Range("B4").Value + 1

'for PDF file save

Sheet1.Range("A2:I27").ExportAsFixedFormat xlTypePDF, Filename:= _

"C:\Invoice\" & Sheet1.Range("B4").Value, Openafterpublish:=True

'for clear invoice

Sheet1.Range("B5:E9").ClearContents

Sheet1.Range("A11:F22").ClearContents

Sheet1.Range("G25:I25").ClearContents

End Sub

Download Workbook