Monday, February 22, 2021

Create Hide And Seek Contact form Excel VBA

 Create Hide And Seek Contact form Excel VBA

Create Hide And Seek Contact form Excel VBA


Watch Video


VBA Code

Private Sub CMD1_Click()
On Error Resume Next
a = 24
b = 30
Me.TEL2.Left = a
Me.TEL2.Top = Me.TEL1.Top + b
Me.MOBILE1.Top = Me.MOBILE1.Top + b
Me.MOBILE2.Top = Me.MOBILE1.Top + b
Me.EMAIL1.Top = Me.EMAIL1.Top + b
Me.EMAIL2.Top = Me.EMAIL1.Top + b
Me.ADDRESS1.Top = Me.ADDRESS1.Top + b
Me.ADDRESS2.Top = Me.ADDRESS1.Top + b
Me.CMD2.Top = Me.MOBILE1.Top
Me.CMD3.Top = Me.EMAIL1.Top
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD6.Top = Me.MOBILE1.Top
Me.CMD7.Top = Me.EMAIL1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.TEL2.TabStop = True
Me.CMD1.Visible = False
Me.CMD5.Visible = True
End Sub

Private Sub CMD2_Click()
On Error Resume Next
a = 24
b = 30
Me.MOBILE2.Left = a
Me.MOBILE2.Top = Me.MOBILE1.Top + 30
Me.EMAIL1.Top = Me.EMAIL1.Top + b
Me.EMAIL2.Top = Me.EMAIL1.Top + b
Me.ADDRESS1.Top = Me.ADDRESS1.Top + b
Me.ADDRESS2.Top = Me.ADDRESS1.Top + b
Me.CMD2.Top = Me.MOBILE1.Top
Me.CMD3.Top = Me.EMAIL1.Top
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD6.Top = Me.MOBILE1.Top
Me.CMD7.Top = Me.EMAIL1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.MOBILE2.TabStop = True
Me.CMD2.Visible = False
Me.CMD6.Visible = True
End Sub

Private Sub CMD3_Click()
On Error Resume Next
a = 24
b = 30
Me.EMAIL2.Left = a
Me.EMAIL2.Top = Me.EMAIL1.Top + 30
Me.ADDRESS1.Top = Me.ADDRESS1.Top + b
Me.ADDRESS2.Top = Me.ADDRESS1.Top + b
Me.CMD2.Top = Me.MOBILE1.Top
Me.CMD3.Top = Me.EMAIL1.Top
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD6.Top = Me.MOBILE1.Top
Me.CMD7.Top = Me.EMAIL1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.EMAIL2.TabStop = True
Me.CMD3.Visible = False
Me.CMD7.Visible = True
End Sub

Private Sub CMD4_Click()
On Error Resume Next
a = 24
b = 30
Me.ADDRESS2.Left = a
Me.ADDRESS2.Top = Me.ADDRESS1.Top + b
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.ADDRESS2.TabStop = True
Me.CMD4.Visible = False
Me.CMD8.Visible = True
End Sub

Private Sub CMD5_Click()
On Error Resume Next
a = 465.75
b = 30
Me.TEL2.Left = a
Me.TEL2.Top = Me.TEL1.Top - b
Me.MOBILE1.Top = Me.MOBILE1.Top - 30
Me.MOBILE2.Top = Me.MOBILE1.Top - 30
Me.EMAIL1.Top = Me.EMAIL1.Top - b
Me.EMAIL2.Top = Me.EMAIL1.Top - b

Me.ADDRESS1.Top = Me.ADDRESS1.Top - b
Me.ADDRESS2.Top = Me.ADDRESS1.Top - b
Me.CMD2.Top = Me.MOBILE1.Top
Me.CMD3.Top = Me.EMAIL1.Top
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD6.Top = Me.MOBILE1.Top
Me.CMD7.Top = Me.EMAIL1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.CMD1.Visible = True
Me.CMD5.Visible = False
End Sub

Private Sub CMD6_Click()
On Error Resume Next
a = 465.75
b = 30
Me.MOBILE2.Left = a
Me.MOBILE2.Top = Me.MOBILE1.Top - 30
Me.EMAIL1.Top = Me.EMAIL1.Top - b
Me.EMAIL2.Top = Me.EMAIL1.Top - b

Me.ADDRESS1.Top = Me.ADDRESS1.Top - b
Me.ADDRESS2.Top = Me.ADDRESS1.Top - b
Me.CMD2.Top = Me.MOBILE1.Top
Me.CMD3.Top = Me.EMAIL1.Top
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD6.Top = Me.MOBILE1.Top
Me.CMD7.Top = Me.EMAIL1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.CMD2.Visible = True
Me.CMD6.Visible = False

End Sub

Private Sub CMD7_Click()
On Error Resume Next
a = 465.75
b = 30
Me.EMAIL2.Left = a
Me.EMAIL2.Top = Me.EMAIL1.Top - b
Me.ADDRESS1.Top = Me.ADDRESS1.Top - b
Me.ADDRESS2.Top = Me.ADDRESS2.Top - b
Me.CMD3.Top = Me.EMAIL1.Top
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.CMD3.Visible = True
Me.CMD7.Visible = False
End Sub

Private Sub CMD8_Click()
On Error Resume Next
a = 465.75
b = 30
Me.ADDRESS2.Left = a
Me.ADDRESS2.Top = Me.ADDRESS1.Top - b
Me.CMD4.Top = Me.ADDRESS1.Top
Me.CMD8.Top = Me.ADDRESS1.Top
Me.CMD4.Visible = True
Me.CMD8.Visible = False
End Sub

Private Sub CommandButton2_Click()
Dim I As Long
I = Sheet2.Range("A1000000").End(xlUp).Row + 1
Sheet2.Range("A" & I) = Me.NMEBOX1
Sheet2.Range("B" & I) = Me.NMEBOX2
Sheet2.Range("C" & I) = Me.TELBOX1
Sheet2.Range("D" & I) = Me.TELBOX2
Sheet2.Range("E" & I) = Me.mobbox
Sheet2.Range("F" & I) = Me.mobbox2
Sheet2.Range("G" & I) = Me.EMBOX1
Sheet2.Range("H" & I) = Me.EMBOX2
Sheet2.Range("I" & I) = Me.ADDRESSBOX1
Sheet2.Range("j" & I) = Me.ADDRESSBOX2

'Fore Clear YTextbox
Dim ctr As Control
For Each ctr In UserForm1.Controls
On Error Resume Next
ctr.Value = ""
Next ctr
Me.NMEBOX1.SetFocus
End Sub



Friday, February 5, 2021

Create Cashbook Report Userform Excel VBA

Create Cashbook Report Userform Excel VBA

Create Cashbook Report Userform Excel VBA



VBA CODE
Private Sub ComboBox1_Change()
Me.ListBox1.Clear
Me.ListBox1.AddItem
For r = 1 To 9
Me.ListBox1.List(0, r - 1) = Sheet1.Cells(1, r)
Next r
Me.ListBox1.Selected(0) = True

Dim I As Long
For I = 2 To Sheet1.Range("A1000000").End(xlUp).Row
If Sheet1.Cells(I, "G") = CStr(Me.ComboBox1) And Sheet1.Cells(I, "A") = CDate(Me.TextBox1) Then
Me.ListBox1.AddItem
For c = 0 To 9
Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(I, c + 1)
Next c
End If
Next I

Dim M As Integer
Dim SUM, SUM1 As Double
For M = 1 To Me.ListBox1.ListCount - 1
If Me.ListBox1.List(M, 6) = Me.ComboBox1 Then
SUM = SUM + Val(Me.ListBox1.List(M, 4))
SUM1 = SUM1 + Val(Me.ListBox1.List(M, 5))
End If
Next M
Me.ListBox1.AddItem Me.ComboBox1
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Total"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Format(SUM, "#####.00")
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = Format(SUM1, "#####.00")

Me.ListBox1.AddItem "____________"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Balance"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Format(SUM - SUM1, "#####.00")
SUM = 0
SUM1 = 0
End Sub

Private Sub ComboBox2_Change()
Me.ListBox1.Clear
Me.ListBox1.AddItem
For r = 1 To 9
Me.ListBox1.List(0, r - 1) = Sheet1.Cells(1, r)
Next r
Me.ListBox1.Selected(0) = True

Dim I As Long
For I = 2 To Sheet1.Range("A1000000").End(xlUp).Row
If Sheet1.Cells(I, "i") = CStr(Me.ComboBox2) And Sheet1.Cells(I, "A") = CDate(Me.TextBox1) Then
Me.ListBox1.AddItem
For c = 0 To 9
Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(I, c + 1)
Next c
End If
Next I

Dim M As Integer
Dim SUM, SUM1 As Double
For M = 1 To Me.ListBox1.ListCount - 1
If Me.ListBox1.List(M, 8) = Me.ComboBox2 Then
SUM = SUM + Val(Me.ListBox1.List(M, 4))
SUM1 = SUM1 + Val(Me.ListBox1.List(M, 5))
End If
Next M
Me.ListBox1.AddItem Me.ComboBox2
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Total"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Format(SUM, "#####.00")
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = Format(SUM1, "#####.00")

Me.ListBox1.AddItem "____________"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Balance"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Format(SUM - SUM1, "#####.00")
SUM = 0
SUM1 = 0
End Sub

Private Sub CommandButton1_Click()
Me.ListBox1.Clear
Me.ListBox1.AddItem
For r = 1 To 9
Me.ListBox1.List(0, r - 1) = Sheet1.Cells(1, r)
Next r
Me.ListBox1.Selected(0) = True

Dim I As Long
Dim X As Integer
For X = 0 To Me.ComboBox1.ListCount - 1
For I = 2 To Sheet1.Range("A1000000").End(xlUp).Row
If Sheet1.Cells(I, "G") = CStr(Me.ComboBox1.List(X, 0)) And Sheet1.Cells(I, "A") = CDate(Me.TextBox1) Then
Me.ListBox1.AddItem
For c = 0 To 9
Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(I, c + 1)
Next c
End If
Next I

Dim M As Integer
Dim SUM, SUM1 As Double
For M = 1 To Me.ListBox1.ListCount - 1
If Me.ListBox1.List(M, 6) = Me.ComboBox1.List(X, 0) Then
SUM = SUM + Val(Me.ListBox1.List(M, 4))
SUM1 = SUM1 + Val(Me.ListBox1.List(M, 5))
End If
Next M
Me.ListBox1.AddItem Me.ComboBox1.List(X, 0)
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Total"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Format(SUM, "#####.00")
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = Format(SUM1, "#####.00")

Me.ListBox1.AddItem "____________"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Balance"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Format(SUM - SUM1, "#####.00")
SUM = 0
SUM1 = 0
Next X
End Sub


Private Sub SpinButton1_SpinDown()
On Error Resume Next
Me.TextBox1 = CDate(Me.TextBox1) - 1
Call CommandButton1_Click
End Sub

Private Sub SpinButton1_SpinUp()
On Error Resume Next
Me.TextBox1 = CDate(Me.TextBox1) + 1
Call CommandButton1_Click
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1 = Date
Dim UID As New Collection
Dim I As Long
For I = 2 To Sheet1.Range("A1000000").End(xlUp).Row
On Error Resume Next
UID.Add CStr(Sheet1.Cells(I, "G")), CStr(Sheet1.Cells(I, "G"))
Next I
For Each itm In UID
Me.ComboBox1.AddItem itm
Next itm
Me.ComboBox2.List = Array("Cash", "Bank")
End Sub