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