Increase Userform Height Automatically Excell VBA

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

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

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

Change Textbox Back Color Automatically Excell VBA

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

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

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

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

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

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

How Can Use Optionbutton In Userform In Excell VBA

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