dss28
Board Regular
- Joined
- Sep 3, 2020
- Messages
- 165
- Office Version
- 2007
- Platform
- Windows
just joined the group. No experience on vba coding or excel as such but got impressed about vba coding and started learning from online tutorials.
Based on some tutorials I have been able to design a userform to my need, working well with data addition serially. however not able to search and modify the data based on two variables in column B and column G.
in my database, first row is a header,
column A - is serial number autogenerated
column B - is main data entry number (say 1, 2, 3...)
column G - is sub serial number under main data entry number (say 1,2,3,4,5,6)
there are about 10 to 15 columns with different data. my data looks like the below table:
I need help to recall whole row for data editing based on column B and column G one by one by entering B and G values in the user form:
For example:
Column B(1) , Column G(1), then edit the data as required, saving back to its place again, then recall next sub item number for editing eg.
Column B(1) , Column G(2)
Column B(1) , Column G(3)
Based on some tutorials I have been able to design a userform to my need, working well with data addition serially. however not able to search and modify the data based on two variables in column B and column G.
in my database, first row is a header,
column A - is serial number autogenerated
column B - is main data entry number (say 1, 2, 3...)
column G - is sub serial number under main data entry number (say 1,2,3,4,5,6)
there are about 10 to 15 columns with different data. my data looks like the below table:
A | B | C | D | E | F | G |
1 | 1 | 1 | ||||
2 | 1 | 2 | ||||
3 | 1 | 3 | ||||
4 | 1 | 4 | ||||
5 | 2 | 1 | ||||
6 | 3 | 1 |
I need help to recall whole row for data editing based on column B and column G one by one by entering B and G values in the user form:
For example:
Column B(1) , Column G(1), then edit the data as required, saving back to its place again, then recall next sub item number for editing eg.
Column B(1) , Column G(2)
Column B(1) , Column G(3)
VBA Code:
Sub Save()
‘save data
Dim frm As Worksheet
Dim database As Worksheet
Dim iRow As Long
Dim iSerial As Long
Set frm = ThisWorkbook.Sheets("Form")
Set database = ThisWorkbook.Sheets("PODetails")
If Trim(frm.Range("M1").Value) = "" Then
iRow = database.Range("A" & Application.Rows.Count).End(xlUp).Row + 1
If iRow = 2 Then
iSerial = 1
Else
iSerial = database.Cells(iRow - 1, 1).Value + 1
End If
Else
iRow = frm.Range("L1").Value
iSerial = frm.Range("M1").Value
End If
Sheets("PODetails").Cells(iRow, 1).Value = iSerial
Sheets("PODetails").Cells(iRow, 2).Value = frm.Range("I6").Value
Sheets("PODetails").Cells(iRow, 3).Value = frm.Range("I8").Value
Sheets("PODetails").Cells(iRow, 4).Value = frm.Range("I10").Value
Sheets("PODetails").Cells(iRow, 5).Value = frm.Range("I12").Value
Sheets("PODetails").Cells(iRow, 6).Value = frm.Range("I14").Value
Sheets("PODetails").Cells(iRow, 7).Value = frm.Range("I16").Value
Sheets("PODetails").Cells(iRow, 8).Value = frm.Range("I18").Value
Sheets("PODetails").Cells(iRow, 9).Value = frm.Range("I22").Value
Sheets("PODetails").Cells(iRow, 10).Value = frm.Range("I24").Value
Sheets("PODetails").Cells(iRow, 11).Value = frm.Range("I26").Value
Sheets("PODetails").Cells(iRow, 12).Value = frm.Range("I28").Value
Sheets("PODetails").Cells(iRow, 16).Value = frm.Range("I32").Value
Sheets("PODetails").Cells(iRow, 17).Value = frm.Range("I30").Value
Sheets("PODetails").Cells(iRow, 20).Value = Application.UserName
Sheets("PODetails").Cells(iRow, 21).Value = [Text(Now(), "DD-MM-YYYY HH:MM")]
End
frm.Range("L1").Value = ""
frm.Range("M1").Value = ""
MsgBox "data saved succesfully", vbOKOnly + vbCritical, "Save"
With Sheets("Form")
.Range("I8").Interior.ColorIndex = 40
.Range("I8").Value = ""
.Range("I10").Interior.ColorIndex = 40
.Range("I10").Value = ""
.Range("I12").Interior.ColorIndex = 40
.Range("I12").Value = ""
.Range("I14").Interior.ColorIndex = 40
.Range("I14").Value = ""
.Range("I16").Interior.ColorIndex = 40
.Range("I16").Value = ""
.Range("I18").Interior.ColorIndex = 40
.Range("I18").Value = ""
.Range("I22").Interior.ColorIndex = 40
.Range("I22").Value = ""
.Range("I24").Interior.ColorIndex = 40
.Range("I24").Value = ""
.Range("I26").Interior.ColorIndex = 40
.Range("I26").Value = ""
.Range("I28").Interior.ColorIndex = 40
.Range("I28").Value = ""
.Range("I30").Interior.ColorIndex = 40
.Range("I30").Value = ""
.Range("I32").Interior.ColorIndex = 1
.Range("I32").Value = ""
'End
'MsgBox "data saved succesfully", vbOKOnly + vbCritical, "Save"
End With
End Sub
Sub AddItem()
‘add another item in same PO
Dim frm As Worksheet
'Dim database As Worksheet
Dim iRow As Long
Dim iSerial As Long
Set frm = ThisWorkbook.Sheets("Form")
'Set database = ThisWorkbook.Sheets("PODetails")
If Trim(frm.Range("M1").Value) = "" Then
iRow = Sheets("PODetails").Range("B" & Application.Rows.Count).End(xlUp).Row + 1
If iRow = 2 Then
iSerial = 1
Else
iSerial = Sheets("PODetails").Cells(iRow - 1, 1).Value + 1
End If
Else
iRow = frm.Range("L1").Value
iSerial = frm.Range("M1").Value
End If
With Sheets("PODetails")
Sheets("PODetails").Cells(iRow, 1).Value = iSerial
Sheets("PODetails").Cells(iRow, 2).Value = frm.Range("I6").Value
Sheets("PODetails").Cells(iRow, 3).Value = frm.Range("I8").Value
Sheets("PODetails").Cells(iRow, 4).Value = frm.Range("I10").Value
Sheets("PODetails").Cells(iRow, 5).Value = frm.Range("I12").Value
Sheets("PODetails").Cells(iRow, 6).Value = frm.Range("I14").Value
Sheets("PODetails").Cells(iRow, 7).Value = frm.Range("I16").Value
Sheets("PODetails").Cells(iRow, 8).Value = frm.Range("I18").Value
Sheets("PODetails").Cells(iRow, 9).Value = frm.Range("I22").Value
Sheets("PODetails").Cells(iRow, 10).Value = frm.Range("I24").Value
Sheets("PODetails").Cells(iRow, 11).Value = frm.Range("I26").Value
Sheets("PODetails").Cells(iRow, 12).Value = frm.Range("I28").Value
Sheets("PODetails").Cells(iRow, 16).Value = frm.Range("I32").Value
Sheets("PODetails").Cells(iRow, 17).Value = frm.Range("I30").Value
Sheets("PODetails").Cells(iRow, 20).Value = Application.UserName
Sheets("PODetails").Cells(iRow, 21).Value = [Text(Now(), "DD-MM-YYYY HH:MM")]
End With
frm.Range("L1").Value = ""
frm.Range("M1").Value = ""
With Sheets("Form")
.Range("I16").Interior.ColorIndex = 40
.Range("I16").Value = ""
.Range("I18").Interior.ColorIndex = 40
.Range("I18").Value = ""
.Range("I22").Interior.ColorIndex = 40
.Range("I22").Value = ""
.Range("I24").Interior.ColorIndex = 40
.Range("I24").Value = ""
.Range("I26").Interior.ColorIndex = 40
.Range("I26").Value = ""
.Range("I28").Interior.ColorIndex = 40
.Range("I28").Value = ""
.Range("I30").Interior.ColorIndex = 40
.Range("I30").Value = ""
.Range("I32").Interior.ColorIndex = 1
.Range("I32").Value = ""
End With
End Sub
Sub Modify()
‘earch, edit and save any entry in PO
Dim iRow As Long
Dim iSerial As Long
iSerial = Application.InputBox("Please enter PO Number to make modification.", "Modify", , , , , , 1)
iSerial = Application.InputBox("Please enter Sr.No. to make modification.", "Modify", , , , , , 1)
On Error Resume Next
iRow = Application.WorksheetFunction.IfError _
(Application.WorksheetFunction.Match(iSerial, Sheets("PODetails").Range("B:B"), 0), 0)
iRow = Application.WorksheetFunction.IfError _
(Application.WorksheetFunction.Match(iSerial, Sheets("PODetails").Range("G:G"), 0), 0)
On Error GoTo 0
If iRow = 0 Then
MsgBox "No record found.", vbOKOnly + vbCritical, "No Record"
Exit Sub
End If
Sheets("Form").Range("L1").Value = iRow
Sheets("Form").Range("M1").Value = iSerial
Sheets("Form").Range("I6").Value = Sheets("PODetails").Cells(iRow, 2).Value
Sheets("Form").Range("I8").Value = Sheets("PODetails").Cells(iRow, 3).Value
Sheets("Form").Range("I10").Value = Sheets("PODetails").Cells(iRow, 4).Value
Sheets("Form").Range("I12").Value = Sheets("PODetails").Cells(iRow, 5).Value
Sheets("Form").Range("I14").Value = Sheets("PODetails").Cells(iRow, 6).Value
Sheets("Form").Range("I16").Value = Sheets("PODetails").Cells(iRow, 7).Value
Sheets("Form").Range("I18").Value = Sheets("PODetails").Cells(iRow, 8).Value
Sheets("Form").Range("I22").Value = Sheets("PODetails").Cells(iRow, 9).Value
Sheets("Form").Range("I24").Value = Sheets("PODetails").Cells(iRow, 10).Value
Sheets("Form").Range("I26").Value = Sheets("PODetails").Cells(iRow, 11).Value
Sheets("Form").Range("I28").Value = Sheets("PODetails").Cells(iRow, 12).Value
Sheets("Form").Range("I32").Value = Sheets("PODetails").Cells(iRow, 16).Value
Sheets("Form").Range("I30").Value = Sheets("PODetails").Cells(iRow, 17).Value
End Sub
Sub DeleteRecord()
Dim iRow As Long
Dim iSerial As Long
iSerial = Application.InputBox("Please enter P.No. to delete the recor.", "Delete", , , , , , 1)
On Error Resume Next
iRow = Application.WorksheetFunction.IfError _
(Application.WorksheetFunction.Match(iSerial, Sheets("PODetails").Range("B:B"), 0), 0)
On Error GoTo 0
If iRow = 0 Then
MsgBox "No record found.", vbOKOnly + vbCritical, "No Record"
Exit Sub
End If
Sheets("PODetails").Cells(iRow, 2).EntireRow.Delete shift:=xlUp
Sheets("PODetails").Cells(iRow, 2).EntireRow.Delete shift:=xlUp
Sheets("PODetails").Cells(iRow, 2).EntireRow.Delete shift:=xlUp
Sheets("PODetails").Cells(iRow, 2).EntireRow.Delete shift:=xlUp
Sheets("PODetails").Cells(iRow, 2).EntireRow.Delete shift:=xlUp
Sheets("PODetails").Cells(iRow, 2).EntireRow.Delete shift:=xlUp
End Sub