Option Explicit
Private Sub CommandButton1_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Robiul")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton10_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Tamzid")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton11_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Nobel")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton12_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Shajedul")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton13_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Hasan")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton14_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Monir")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton15_Click()
On Error Resume Next
Worksheets("Robiul").Activate
End Sub
Private Sub CommandButton16_Click()
On Error Resume Next
Worksheets("Borhan").Activate
End Sub
Private Sub CommandButton17_Click()
On Error Resume Next
Worksheets("Sobuj").Activate
End Sub
Private Sub CommandButton18_Click()
On Error Resume Next
Worksheets("Uzzal").Activate
End Sub
Private Sub CommandButton19_Click()
On Error Resume Next
Worksheets("Pias").Activate
End Sub
Private Sub CommandButton2_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Borhan")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton20_Click()
On Error Resume Next
Worksheets("Mukter").Activate
End Sub
Private Sub CommandButton21_Click()
On Error Resume Next
Worksheets("Ashraf").Activate
End Sub
Private Sub CommandButton22_Click()
On Error Resume Next
Worksheets("Mainuddin").Activate
End Sub
Private Sub CommandButton23_Click()
On Error Resume Next
Worksheets("Jahid").Activate
End Sub
Private Sub CommandButton24_Click()
On Error Resume Next
Worksheets("Tamzid").Activate
End Sub
Private Sub CommandButton25_Click()
On Error Resume Next
Worksheets("Nobel").Activate
End Sub
Private Sub CommandButton26_Click()
On Error Resume Next
Worksheets("Shajedul").Activate
End Sub
Private Sub CommandButton27_Click()
On Error Resume Next
Worksheets("Hasan").Activate
End Sub
Private Sub CommandButton28_Click()
On Error Resume Next
Worksheets("Monir").Activate
End Sub
Private Sub CommandButton3_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Sobuj")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton4_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Uzzal")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton5_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Pias")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton6_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Mukter")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton7_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Ashraf")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton8_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Mainuddin")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton9_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Jahid")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim rngA As Range
Dim lRec As Long
Dim lRecRow As Long
Dim lLastRec As Long
Dim lastRow As Long
Set rngA = ActiveCell
If Target.Address = Me.Range("CurrRec").Address _
Or Target.Address = Me.Range("OrderSel").Address Then
Application.EnableEvents = False
If Target.Address = Me.Range("OrderSel").Address Then
Me.Range("CurrRec").Value = Me.Range("SelRec").Value
End If
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
With historyWks
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
lLastRec = lastRow - 1
End With
With inputWks
lRec = .Range("CurrRec").Value
If lRec > 0 And lRec <= lLastRec Then
lRecRow = lRec + 1
historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 11)).Copy
.Range("D5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
rngA.Select
End If
End With
Application.EnableEvents = True
End If
End Sub
Private Sub CommandButton1_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Robiul")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton10_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Tamzid")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton11_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Nobel")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton12_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Shajedul")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton13_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Hasan")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton14_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Monir")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton15_Click()
On Error Resume Next
Worksheets("Robiul").Activate
End Sub
Private Sub CommandButton16_Click()
On Error Resume Next
Worksheets("Borhan").Activate
End Sub
Private Sub CommandButton17_Click()
On Error Resume Next
Worksheets("Sobuj").Activate
End Sub
Private Sub CommandButton18_Click()
On Error Resume Next
Worksheets("Uzzal").Activate
End Sub
Private Sub CommandButton19_Click()
On Error Resume Next
Worksheets("Pias").Activate
End Sub
Private Sub CommandButton2_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Borhan")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton20_Click()
On Error Resume Next
Worksheets("Mukter").Activate
End Sub
Private Sub CommandButton21_Click()
On Error Resume Next
Worksheets("Ashraf").Activate
End Sub
Private Sub CommandButton22_Click()
On Error Resume Next
Worksheets("Mainuddin").Activate
End Sub
Private Sub CommandButton23_Click()
On Error Resume Next
Worksheets("Jahid").Activate
End Sub
Private Sub CommandButton24_Click()
On Error Resume Next
Worksheets("Tamzid").Activate
End Sub
Private Sub CommandButton25_Click()
On Error Resume Next
Worksheets("Nobel").Activate
End Sub
Private Sub CommandButton26_Click()
On Error Resume Next
Worksheets("Shajedul").Activate
End Sub
Private Sub CommandButton27_Click()
On Error Resume Next
Worksheets("Hasan").Activate
End Sub
Private Sub CommandButton28_Click()
On Error Resume Next
Worksheets("Monir").Activate
End Sub
Private Sub CommandButton3_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Sobuj")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton4_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Uzzal")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton5_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Pias")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton6_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Mukter")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton7_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Ashraf")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton8_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Mainuddin")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton9_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Jahid")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim rngA As Range
Dim lRec As Long
Dim lRecRow As Long
Dim lLastRec As Long
Dim lastRow As Long
Set rngA = ActiveCell
If Target.Address = Me.Range("CurrRec").Address _
Or Target.Address = Me.Range("OrderSel").Address Then
Application.EnableEvents = False
If Target.Address = Me.Range("OrderSel").Address Then
Me.Range("CurrRec").Value = Me.Range("SelRec").Value
End If
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
With historyWks
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
lLastRec = lastRow - 1
End With
With inputWks
lRec = .Range("CurrRec").Value
If lRec > 0 And lRec <= lLastRec Then
lRecRow = lRec + 1
historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 11)).Copy
.Range("D5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
rngA.Select
End If
End With
Application.EnableEvents = True
End If
End Sub
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন