Hi All,
As I am new to VBA, i have amalgamated code that I've found online to complete the required function (I hear the collective groans :P). I have data entry form that allows me to enter data into a worksheet as a record (cmAdd). I've managed to create a "Check" to ensure I'm not duplicating entries, but I am coming short when trying to get something that updates an existing record if it already exists (cmdClose) instead of adding another. Logically, I suppose I want to search for the existing record, and then if found, overwrite. I'm just not sure how to do so. I've tried to find the input value in the worksheet to define the row i'm writing to, but no success. Setting iRow for sub CmdClose is where I'm getting the problem, though I could be completely off here, as I don't have any coding experience, but see below code i've got so far. Any help would be greatly appreciated. And again, apologies for my coding/VBA illiteracy
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")
'revised code to avoid problems with
'Excel lists and tables in newer versions
'find first empty row in database
''lRow = ws.Cells(Rows.Count, 1) _
'' .End(xlUp).Offset(1, 0).Row
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
lPart = Me.cboPart.ListIndex
If WorksheetFunction.CountIf(ws.Range("D2", ws.Cells(lRow, 1)), Me.cboPart.Value) > 0 And WorksheetFunction.CountIf(ws.Range("J2", ws.Cells(lRow, 1)), Me.ComboBox1.Value) > 0 Then
MsgBox "Entry Exists for " & Me.cboPart.Value & " on " & Me.ComboBox1.Value & " - Use Update Function", vbCritical
Exit Sub
End If
'check for a Sub component
If Trim(Me.cboPart.Value) = "" Then
Me.cboPart.SetFocus
MsgBox "Please select a sub component"
Exit Sub
End If
'check for a Wind Turbine number
If Trim(Me.ComboBox1.Value) = "" Then
Me.ComboBox1.SetFocus
MsgBox "Please enter a Wind Turbine"
Exit Sub
End If
'copy the data to the database
With ws
.Cells(lRow, 4).Value = Me.cboPart.Value
.Cells(lRow, 5).Value = Me.cboType.Value
.Cells(lRow, 6).Value = Me.cboPart.List(lPart, 1)
.Cells(lRow, 7).Value = Me.cboLocation.Value
.Cells(lRow, 8).Value = Me.txtDate.Value
.Cells(lRow, 9).Value = Me.txtQty.Value
.Cells(lRow, 10).Value = Me.ComboBox1.Value
.Cells(lRow, 11).Value = Me.ComboBox2.Value
.Cells(lRow, 2).Value = Application.UserName
With .Cells(lRow, 3)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End With
'clear the data
'ClearParts
Me.cboType.Value = ""
Me.cboPart.Value = ""
Me.cboPart.RowSource = ""
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.cboLocation.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.cboType.SetFocus
Me.ComboBox2.SetFocus
End Sub
Private Sub cmdClose_Click()
Dim iRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")
'revised code to avoid problems with
'Excel lists and tables in newer versions
'find first empty row in database
''lRow = ws.Cells(Rows.Count, 1) _
'' .End(xlUp).Offset(1, 0).Row
iRow = ws.Cells.Find(What:="Me.cboPart.Value", LookIn:=xlValues).Row
lPart = Me.cboPart.ListIndex
'check for a Sub component
If Trim(Me.cboPart.Value) = "" Then
Me.cboPart.SetFocus
MsgBox "Please select a sub component"
Exit Sub
End If
'check for a Wind Turbine number
If Trim(Me.ComboBox1.Value) = "" Then
Me.ComboBox1.SetFocus
MsgBox "Please enter a Wind Turbine"
Exit Sub
End If
'copy the data to the database
With ws
.Cells(iRow, 4).Value = Me.cboPart.Value
.Cells(iRow, 5).Value = Me.cboType.Value
.Cells(iRow, 6).Value = Me.cboPart.List(lPart, 1)
.Cells(iRow, 7).Value = Me.cboLocation.Value
.Cells(iRow, 8).Value = Me.txtDate.Value
.Cells(iRow, 9).Value = Me.txtQty.Value
.Cells(iRow, 10).Value = Me.ComboBox1.Value
.Cells(iRow, 11).Value = Me.ComboBox2.Value
.Cells(iRow, 2).Value = Application.UserName
With .Cells(iRow, 3)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End With
'clear the data
'ClearParts
Me.cboType.Value = ""
Me.cboPart.Value = ""
Me.cboPart.RowSource = ""
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.cboLocation.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.cboType.SetFocus
Me.ComboBox2.SetFocus
End Sub
Display More