Friday, November 3, 2017

Textbox Sum Total On Controltiptext excel VBA



VBA Code
Private Sub UserForm_Initialize()
For i = 1 To 10
Me("textbox" & i).Value = Sheet1.Cells(i + 1, "E").Value
a = Application.WorksheetFunction.SumIf(Sheet1.Range("A:A"), _
Me("Textbox" & i).Value, Sheet1.Range("B:B"))
Me("textbox" & i).ControlTipText = a & ".00"
Next i
End Sub

Thursday, October 26, 2017

Create Regestry Form for Employee Support Excel VBA





Code Userform1

Step1
Private Sub UserForm_Initialize()
a = Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))
Me.TextBox1 = a + 1000
Me.TextBox2.SetFocus
End Sub


Step2
Private Sub OptionButton1_Click()
Me.TextBox7 = "Male"
Me.TextBox3.SetFocus
End Sub

Private Sub OptionButton2_Click()
Me.TextBox7 = "Female"
Me.TextBox3.SetFocus
End Sub

Step3
Private Sub CommandButton1_Click()
a = Application.GetSaveAsFilename()
Me.TextBox6 = a
Me.Image1.Picture = LoadPicture(Me.TextBox6)
End Sub

Step4
Private Sub CommandButton2_Click()
Dim i As Long
i = Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))
If Me.TextBox1 = "" Or Me.TextBox2 = "" Then
MsgBox "Please Input Name & Contact"
Else
For X = 1 To 7
Sheet2.Range("A" & i).End(xlToLeft).Offset(1, X - 1) = Me("textbox" & X)
Next X
End If
Unload Me
UserForm1.Show
End Sub

Code Userform2

Step1
Private Sub UserForm_Initialize()
Me.TextBox7.SetFocus
End Sub

Step2
Private Sub CommandButton1_Click()
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))
If Sheet2.Cells(i, 1) = Val(Me.TextBox7) Then
For X = 1 To 4
Me("textbox" & X) = Sheet2.Cells(i, X + 1)
Next X
Me.TextBox5 = Sheet2.Cells(i, "G")
Me.Image1.Picture = LoadPicture(Sheet2.Cells(i, "F"))
End If
Next i
End Sub

Sunday, October 15, 2017

Saturday, October 7, 2017

MS Excell Listbox Search By Textbox VBA



VBA Code

Private Sub UserForm_Initialize()
Me.TextBox1.SetFocus
End Sub

Private Sub TextBox1_Change()
Me.TextBox1.Text = StrConv(Me.TextBox1.Text, vbProperCase)
Dim i As Long
Me.ListBox1.Clear
On Error Resume Next
For i = 1 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
a = Len(Me.TextBox1.Text)
If Left(Sheet1.Cells(i, 1).Text, a) = Left(Me.TextBox1.Text, a) Then
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(i, 2).Value
End If
Next i
End Sub

Thursday, October 5, 2017

VLookup Lookup TableArray In Closed Workbook InFolder Excel VBA



VBA Code

Private Sub CommandButton1_Click()
Dim databook As Workbook
Application.ScreenUpdating = False
Set databook = Workbooks.Open(ThisWorkbook.Path & "/" & "Database.xlsm")
For x = 1 To 4
Sheet1.Cells(2, x) = Application.WorksheetFunction.VLookup(Sheet1.Cells(3, "E"), _
Sheets("Sheet1").Range("A:D"), x, 0)
Next x
databook.Close
Application.ScreenUpdating = True
End Sub

Download Workbook 1
Download Workbook 2

Thursday, September 28, 2017

Monday, September 25, 2017

Listbox Create Labour Working Hours Calculater

Listbox Create Labour Working Hours Calculater 


Private Sub ComboBox1_Change()
Dim i As Long
For i = 0 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(i) = False
c = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
If Me.ListBox1.List(i, 0) = Me.ComboBox1.Value And _
Format(Me.ListBox1.List(i, 1), "MMMM") = Me.ComboBox2.Value Then
Me.ListBox1.Selected(i) = True
For x = 1 To 4
Sheet1.Range("A" & c).End(xlToLeft).Offset(1, x - 1) = _
Me.ListBox1.List(i, x - 1)
Next x
End If
Next i
Dim Totala As Long, Totalb As Long
For r = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(r) = True Then
Totala = Totala + Val(Me.ListBox1.List(r, 2))
Totalb = Totalb + Val(Me.ListBox1.List(r, 3))
End If
Next r
If Me.ComboBox1 <> "" And Me.ComboBox2 <> "" Then
Sheet1.Range("A1000000").End(xlUp).Offset(1, 0).Value = "Total"
Sheet1.Range("A1000000").End(xlUp).Offset(0, 1).Value = Me.ComboBox2.Value
Sheet1.Range("A1000000").End(xlUp).Offset(0, 2).Value = Totala
Sheet1.Range("A1000000").End(xlUp).Offset(0, 3).Value = Totalb
End If
End Sub
_____________________________________________________
Private Sub CommandButton1_Click()
For b = 0 To 6
On Error Resume Next
Me.ComboBox1.ListIndex = b
Next b
End Sub
_____________________________________________________
Private Sub CommandButton2_Click()
Dim i As Long
Me.ComboBox1.ListIndex = 0 - 1
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
Sheet1.Range("A" & 2, "D" & i).ClearContents
Next i
End Sub

Thursday, August 24, 2017

Create Customer Database Notification Worksheet Excell VBA


VBA Code

Sales

Private Sub UserForm_Initialize()
Me.TextBox1.Text = Format(Date, "DD/MMM/YYYY")
a = Application.WorksheetFunction.CountA(Sheets("database").Range("A:A"))
Me.TextBox2.Value = a + 1000
End Sub

Private Sub CommandButton1_Click()
Dim i As Long
i = Application.WorksheetFunction.CountA(Sheets("Database"). _
Range("A:A")) + 1
For x = 1 To 6
Sheets("Database").Range("A" & i).End(xlToLeft).Offset(0, x - 1).Value = _
Me("textbox" & x).Value
Next x
Unload Me
Sales.Show
Call notify
End Sub

Calculation

Sub notify()
Dim a As Long, x As Long, i As Long
a = Application.WorksheetFunction.CountA(Sheets("Notification"). _
   Range("A:A")) + 1
   Sheets("Notification").Range("A" & 2, "F" & a).ClearContents
For i = 2 To Application.WorksheetFunction.CountA(Sheets("Database"). _
   Range("A:A"))
d = Application.WorksheetFunction.CountA(Sheets("Notification"). _
    Range("A:A")) + 1
For x = 1 To 6
If Sheets("database").Cells(i, "F").Value >= 1 Then
Sheets("Notification").Range("A" & d).End(xlToLeft).Offset(0, x - 1).Value = _
Sheets("database").Cells(i, x).Value
End If
Next x
Next i
End Sub

Recept

Private Sub CommandButton1_Click()
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheets("database"). _
Range("A:A"))
If Sheets("Database").Cells(i, "B").Value = Val(Me.TextBox2.Text) Then
Sheets("Database").Cells(i, "E").Value = _
  Val(Sheets("Database").Cells(i, "E").Value) + Val(Me.TextBox3.Value)
Sheets("Database").Cells(i, "F").Value = _
   Val(Sheets("Database").Cells(i, "D").Value) - _
   Val(Sheets("Database").Cells(i, "E").Value)
   End If
   Next i
   Unload Me
   Recept.Show
   Call notify
   End Sub



Thursday, July 27, 2017

Advance Filter#2 Select Criteria In any Column ExcellVBA



Private Sub OptionButton1_Click()
Me.ComboBox1 = ""
Me.ComboBox1.BoundColumn = 1
Sheet1.Range("H3:J3") = ""
End Sub
Private Sub OptionButton2_Click()
Me.ComboBox1 = ""
Me.ComboBox1.BoundColumn = 2
Sheet1.Range("H3:J3") = ""
End Sub
Private Sub OptionButton3_Click()
Me.ComboBox1 = ""
Me.ComboBox1.BoundColumn = 3
Sheet1.Range("H3:J3") = ""
End Sub

Private Sub ComboBox1_Click()
Dim i As Long
If Me.OptionButton1.Value = True Then
Sheet1.Range("H3").Value = Me.ComboBox1.Value
ElseIf Me.OptionButton2.Value = True Then
Sheet1.Range("I3").Value = Me.ComboBox1.Value
Else
Sheet1.Range("J3").Value = Me.ComboBox1.Value
End If
On Error Resume Next
ActiveSheet.ShowAllData
i = Sheet1.Range("A100000").End(xlUp).Offset(1, 0).Row
Sheet1.Range("A" & 2, "G" & i).AdvancedFilter xlFilterInPlace, Sheet1.Range("H2:J3")
End Sub

Thursday, July 20, 2017

Listbox FilterTo Other Listbox Userform Exel VBA



Private Sub UserForm_Initialize()
Me.ListBox1.RowSource = "Data"
End Sub

Private Sub TextBox1_Change()
Me.ListBox2.Clear
For i = 0 To Me.ListBox1.ListCount - 1
a = Len(Me.TextBox1.Text)
Me.ListBox1.Selected(i) = False
If Me.TextBox1 <> "" Then
If LCase(Left(Me.ListBox1.List(i, 0), a)) = Me.TextBox1.Text Or _
UCase(Left(Me.ListBox1.List(i, 0), a)) = Me.TextBox1.Text Then
Me.ListBox1.Selected(i) = True
Me.ListBox2.AddItem Me.ListBox1.List(i, 0)
End If
End If
Next i
End Sub


Thursday, July 13, 2017

Textbox Avoid Duplicate Entry Userform Excel VBA



Private Sub TextBox1_AfterUpdate()
Dim a As Long
a = Application.WorksheetFunction.CountIf(Sheet1.Range("A:A"), _
Me.TextBox1.Text)
If a >= 1 Then
Me.TextBox1 = ""
MsgBox "PLease This Name Already submited"
End If
End Sub

Private Sub CommandButton1_Click()
Dim a As Long, x As Long
a = Application.WorksheetFunction.CountIf(Sheet1.Range("A:A"), Me.TextBox1.Text)
x = Application.WorksheetFunction.CountA(Sheet1.Range("A:A")) + 1
If Me.TextBox1 <> "" And Me.TextBox2 <> "" And a = 0 Then
Sheet1.Range("a" & x).Value = Me.TextBox1.Text
Sheet1.Range("B" & x).Value = Me.TextBox2.Text
Else
MsgBox "PLease This Name Already submited Or Textbox Blank"
End If
End Sub

Friday, July 7, 2017

Create Pdf PlayLlst In Userform And Open Pdf File Excel VBA



Private Sub UserForm_Initialize()
Dim i As Long
Me.ListBox1.Clear
Me.ListBox1.AddItem "File Numbers"
Me.ListBox1.Selected(0) = True
For i = 2 To Sheet1.Range("A10000").End(xlUp).Offset(1, 0).Row - 1
Me.ListBox1.AddItem "File Number" & i + 9999
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(i, 1).Value
Next i
End Sub

Private Sub CommandButton1_Click()
Dim MyFolder As String, i As Long
MyFolder = Application.GetSaveAsFilename()
Sheet1.Range("A10000").End(xlUp).Offset(1, 0).Value = MyFolder
Me.ListBox1.Clear
Me.ListBox1.AddItem "File Numbers"
Me.ListBox1.Selected(0) = True
For i = 2 To Sheet1.Range("A10000").End(xlUp).Offset(1, 0).Row - 1
Me.ListBox1.AddItem "File Number" & i + 9999
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(i, 1).Value
Next i
End Sub

Private Sub ListBox1_Click()
On Error Resume Next
Me.AcroPDF1.LoadFile Me.ListBox1.Column(1)
End Sub


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

Sunday, May 21, 2017

Label Width And Backcolor And Caption Change By Textbox Userform Excell VBA



Private Sub TextBox1_Change()
a = Len(Me.TextBox1.Text)
If a <= 12 Then
Me.Label1.Width = a * 19
If a <= 4 Then
Me.Label1.BackColor = vbRed
Me.Label1.Caption = "Weak"
ElseIf a <= 8 Then
Me.Label1.BackColor = vbYellow
Me.Label1.Caption = "Good"
Else
Me.Label1.Caption = "Excellent"
Me.Label1.BackColor = vbGreen
End If
End If
End Sub

Tuesday, May 2, 2017

Multiple Combobox Additem Multiple Criteria Userform Excell VBA



Private Sub UserForm_Initialize()
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For a = 1 To 3
If Sheet1.Cells(1, a).Value = Me("Label" & a).Caption Then
Me("Combobox" & a).AddItem Sheet1.Cells(i, a).Value
End If
Next a
Next i
End Sub

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

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


Tuesday, February 28, 2017

Convert Numeric Value To Words In Userform Excell VBA







Private Sub TextBox1_Change()

On Error Resume Next

If Me.TextBox1.Text <> "" Then

Me.TextBox2.Text = SpellNumber(Me.TextBox1.Text)

Else

Me.TextBox2 = ""

End If

End Sub

Monday, February 27, 2017

Play Youtube Video IN Userform EXcell VBA





Private Sub CommandButton1_Click()

Me.ShockwaveFlash1.Movie = "https://www.youtube.com/v/acIOtfweCXY&vq=hd720"

Me.ShockwaveFlash1.AllowFullScreen = True

End Sub

Saturday, February 25, 2017

Transfer Data One Sheet TO Multiple Sheets Excell VBA





Private Sub CommandButton1_Click()

Application.ScreenUpdating = False

Dim i As Long

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

b = Application.WorksheetFunction.CountA(Sheet2.Range("A:A")) + 1

For x = 2 To 5

For c = 0 To 3

Sheets("Sheet" & x).Range("A" & b).End(xlToLeft).Offset(0, c).Value = _

Sheet1.Cells(i, c + 1).Value

Next c

Next x

Next i

Application.ScreenUpdating = True

End Sub

Thursday, February 23, 2017

Filter Database With Customer Name In Worksheet Excell VBA





Private Sub ComboBox1_Change()

Dim i As Long

Sheet1.Range("A2:F1000").ClearContents

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

c = Application.WorksheetFunction.CountA(Sheet1.Range("A:A")) + 1

For x = 1 To 6

If Sheet2.Cells(i, 1).Value = Me.ComboBox1.Value Then

Sheet1.Range("A" & c).End(xlToLeft).Offset(0, x - 1).Value = Sheet2.Cells(i, x).Value

End If

Next x

Next i

Sheet1.Range("G2").Value = Application.WorksheetFunction.Sum(Sheet1.Range("F:F"))

End Sub

Friday, February 17, 2017

Transfer Data To worksheet And Count Days Excell VBA



Combobbox Fill
Private Sub UserForm_Initialize()
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))
Me.ComboBox1.AddItem Sheet2.Cells(i, 1).Value
Next i
End Sub
Textbox Format
Private Sub TextBox1_AfterUpdate()
On Error Resume Next
Me.TextBox1 = CDate(Me.TextBox1)
End Sub
Private Sub TextBox2_AfterUpdate()
On Error Resume Next
Me.TextBox2 = CDate(Me.TextBox2)
End Sub
Data Transfer To Sheet
Private Sub CommandButton1_Click()
Dim x As Long
x = Application.WorksheetFunction.CountA(Sheet1.Range("A:A")) + 1
Sheet1.Range("A" & x).Value = Me.ComboBox1.Value
Sheet1.Range("B" & x).Value = Me.TextBox1.Text
Sheet1.Range("C" & x).Value = Me.TextBox2.Text
On Error Resume Next
Sheet1.Range("D" & x).Value = Sheet1.Range("C" & x) - Sheet1.Range("B" & x) & "Days"
End Sub

Thursday, February 16, 2017

Searching Telephone Directory Excell VBA



VBA Code

Private Sub TextBox1_Change()
On Error Resume Next
Me.TextBox1.Text = StrConv(Me.TextBox1.Text, vbProperCase)
Me.ListBox1.Clear
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For x = 1 To 4
a = Len(Me.TextBox1.Text)
If Left(Sheet1.Cells(i, x).Value, a) = Me.TextBox1.Text And Me.TextBox1.Text <> "" Then
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
For c = 1 To 4
Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(i, c + 1).Value
Next c
End If
Next x
Next i

End Sub


New Code With Capture Column Heds Data

Private Sub UserForm_Initialize()
Me.TextBox1.SetFocus
End Sub

Private Sub TextBox1_Change()
On Error Resume Next
Me.TextBox1.Text = StrConv(Me.TextBox1.Text, vbProperCase)
Me.ListBox1.Clear
Me.ListBox1.AddItem Sheet1.Cells(1, "A")
For B = 2 To 4
Me.ListBox1.List(ListBox1.ListCount - 1, B - 1) = Sheet1.Cells(1, B)
Next B
Me.ListBox1.Selected(0) = True
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For x = 1 To 4
a = Len(Me.TextBox1.Text)
If Left(Sheet1.Cells(i, x).Value, a) = Me.TextBox1.Text And Me.TextBox1.Text <> "" Then
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
For c = 1 To 4
Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(i, c + 1).Value
Next c
End If
Next x
Next i
End Sub

Wednesday, February 15, 2017

Worksheet Data Edit From Userform EXcell VBA





Textbox Format
Private Sub TextBox1_AfterUpdate()
On Error Resume Next
Me.TextBox1 = CDate(Me.TextBox1)
End Sub
Private Sub TextBox3_AfterUpdate()
On Error Resume Next
Me.TextBox3.Value = StrConv(Me.TextBox3.Value, vbProperCase)
End Sub
Private Sub TextBox4_AfterUpdate()
On Error Resume Next
Me.TextBox4.Value = Format(Me.TextBox4.Value, "#####.00")
End Sub
Worksheet To Userform
Private Sub TextBox5_AfterUpdate()
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For x = 1 To 4
If Sheet1.Cells(i, "B").Value = Me.TextBox5.Text Then
Me("Textbox" & x).Value = Sheet1.Cells(i, x).Value
End If
Next x
Next i
End Sub
Userform To worksheet
Private Sub CommandButton1_Click()
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For x = 1 To 4
If Sheet1.Cells(i, "B").Value = Me.TextBox5.Text Then
Sheet1.Cells(i, x).Value = Me("Textbox" & x).Value
End If
Next x
Next i
End Sub

Tuesday, February 14, 2017

Worksheet Sum Runningbalance Excell VBA





Private Sub Worksheet_Change(ByVal Target As Range)
 i = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
On Error Resume Next
Sheet1.Cells(i, "D").Value = Val(Sheet1.Cells(i - 1, "D")) + Val(Sheet1.Cells(i, "B")) _
- Val(Sheet1.Cells(i, "C"))
End Sub

Monday, February 13, 2017

Worksheet Send Data Without Duplicate Value In EXcell VBA





Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
a = Application.WorksheetFunction.CountIf(Sheet1.Range("B:B"), _
Sheet1.Cells(i, 1).Value)
If a = 0 Then
Sheet1.Range("B1000000").End(xlUp).Offset(1, 0).Value = Sheet1.Cells(i, 1).Value
End If
Next i
End Sub

Sunday, February 12, 2017

Insert serial Number Automatically In Wortksheet Excell VBA





Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
i = Application.WorksheetFunction.CountA(Sheet1.Range("B:B"))
If i > 1 Then
Sheet1.Range("A" & i).Value = i - 1
End If
End Sub

Saturday, February 11, 2017

Processor Bar In Userform Excell VBA





Private Sub CommandButton1_Click()

Dim i As Long

For i = 1 To 5000 Step 3

Me.Label2.Width = Me.Label2.Width + 0.3

DoEvents

Me.Caption = i / 50 & "%"

Me.Label2.Caption = i / 50 & "%"

If Me.Label2.Width >= 300 Then

Me.Label2.BackColor = vbYellow

End If

Next i

Me.Caption = "Completed"

Me.Label2.Caption = "Completed"

Me.Label2.TextAlign = fmTextAlignCenter

End Sub

Friday, February 10, 2017

Combobox Additem From worksheet Dynamic Range Excell VBA





Combobox Fill Dynamic Range
Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To Sheet1.Range("A1000000").End(xlUp).Offset(1, 0).Row
Me.ComboBox1.AddItem Sheet1.Cells(i, 1).Value
Next i
End Sub

Thursday, February 9, 2017

Listbox Additem From Worksheet Between Two Date Excell VBA





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

Private Sub TextBox2_AfterUpdate()
On Error Resume Next
Me.TextBox2 = CDate(Me.TextBox2)
Me.ListBox1.Clear
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
If Sheet1.Cells(i, 1).Value >= CDate(Me.TextBox1.Value) And _
Sheet1.Cells(i, 1).Value <= CDate(Me.TextBox2.Value) Then
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(i, 2).Value & ".00"
End If
Next i
End Sub

Tuesday, February 7, 2017

Calendar In Excell VBA Useerform





For Combobox & Textbox
Private Sub UserForm_Initialize()
Me.TextBox1.Value = Format(Date, "YYYY")
For i = 0 To 11
a = Application.WorksheetFunction.EDate("1" & "/" & "January" & "/" & Me.TextBox1.Value, i)
Me.ComboBox1.AddItem Format(a, "MMMM")
Next i
Me.ComboBox1.Value = Format(Date, "MMMM")
End Sub
Private Sub TextBox1_Change()
Call ComboBox1_Change
End Sub
For Spinbutton
Private Sub SpinButton1_SpinDown()
On Error Resume Next
Me.TextBox1.Value = Me.TextBox1.Value - 1
End Sub
Private Sub SpinButton1_SpinUp()
On Error Resume Next
Me.TextBox1.Value = Me.TextBox1.Value + 1
End Sub
For Listbox
Private Sub ComboBox1_Change()
Me.ListBox1.Clear
For i = 1 To 31
On Error Resume Next
Me.ListBox1.AddItem CDate(i & "/" & Me.ComboBox1.Value & "/" & Me.TextBox1.Value)
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Format(CDate(i & "/" & _
Me.ComboBox1.Value & "/" & Me.TextBox1.Value), "DDDD")
Next i
End Sub


Monday, February 6, 2017

Listbox Capture From Other Workbook Excell VBA Userform





Private Sub UserForm_Initialize()
Dim database As Workbook, i As Long
Application.ScreenUpdating = False
Set database = Workbooks.Open(ThisWorkbook.Path & "/" & "Database.xlsm")
For i = 2 To Application.WorksheetFunction.CountA(Sheets("Sheet1").Range("A:A"))
Me.ListBox1.AddItem Sheets("Sheet1").Cells(i, 1).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheets("Sheet1").Cells(i, 2).Value
Next i
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub

Sunday, February 5, 2017

Listbox Get sum Monthly total from Worksheet Excell VBA





Combobox Fill
Private Sub UserForm_Initialize()
For i = 0 To 3
Me.ComboBox1.AddItem Format(Date, "YYYY") - i
Next i
Me.ComboBox1.Value = Format(Date, "YYYY")
End Sub
Listbox Fill
Private Sub ComboBox1_Change()
Me.ListBox1.Clear
For i = 0 To 11
A = Application.WorksheetFunction.EDate("1" & "/" & "January" & "/" & Me.ComboBox1.Value, i)
B = Application.WorksheetFunction.EoMonth("1" & "/" & "January" & "/" & Me.ComboBox1.Value, i)
Me.ListBox1.AddItem Format(A, "MMMM")
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Application.WorksheetFunction.SumIfs _
(Sheet1.Range("B:B"), Sheet1.Range("A:A"), _
">=" & A, Sheet1.Range("A:A"), "<=" & B)
Next i
End Sub

Saturday, February 4, 2017

Listbox Additem From Other Listbox Excell VBA





VBA Code

Private Sub CommandButton1_Click()
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) = True Then
Me.ListBox2.AddItem Me.ListBox1.Column(0)
Me.ListBox1.Selected(i) = False
End If
Next i
End Sub

Friday, February 3, 2017

Multiple Listbox Additem From worksheeet Excell VBA





VBA Code

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