Userform Command Button Event

[ddoublev]

Hi,

I have built a sheet which uses a custom userform for data entry.

On the sheet is a "Submit" command button, which inputs all textbox values into a new table row in the respective sheet.
I have been asked to make allowances for if a record is needed to be updated, I have been able to create another userform which on datepicker change searched the table for a record with the date and returns values into the userform, clicking submit on this form will overwrite the record with the new data.

Is it possible to use one userform, with one submit button to complete two seperate actions based on certain conditions?
Basically after the userform is initalized and date selected if a record is not found create new row of data with userform data, if a record is found replace that row with the new data.

Any help is much appreciated.

Thanks

Dylan

Dim DateFound As Range


Private Sub BoxDate_Change()


    Dim dFind As Date


    If IsDate(BoxDate) = False Then Exit Sub
        dFind = BoxDate


    With Range("DateSPWTP") 'a named range on sheet listing dates
        Set DateFound = .Find(dFind)
        If DateFound Is Nothing Then
        Else
            With DateFound
                BoxOperator = .Offset(0, 1)
                Page1Box1 = .Offset(0, 2)
                Page1Box2 = .Offset(0, 6)
                Page1Box3 = .Offset(0, 7)
                Page1Box4 = .Offset(0, 9)
                Page1Box5 = .Offset(0, 10)
                BoxComments = .Offset(0, 78)
            End With
        End If
    End With


    MsgBox "Existing Record Found!"


End Sub


Private Sub cmdSubmit_Click()
    
    'Prevent data submission triggering worksheet_change event
    Application.EnableEvents = False


    'Submit new data record to table
    Dim LastRow As Long
    LastRow = Worksheets("Spinifex Camp WTP").Cells(Worksheets("Spinifex Camp WTP").Rows.Count, 1).End(xlUp).Row + 1
    If BoxDate.Value <> "" Then
        Cells(LastRow, 1).Value = BoxDate
    End If
    If BoxOperator.Value <> "" Then
        Cells(LastRow, 2).Value = BoxOperator
    End If
    If Page1Box1.Value <> "" Then
        Cells(LastRow, 3).Value = Page1Box1
    End If
    If Page1Box2.Value <> "" Then
        Cells(LastRow, 7).Value = Page1Box2
    End If
    If Page1Box3.Value <> "" Then
        Cells(LastRow, 8).Value = Page1Box3
    End If
    If Page1Box4.Value <> "" Then
        Cells(LastRow, 10).Value = Page1Box4
    End If
    If Page1Box5.Value <> "" Then
        Cells(LastRow, 11).Value = Page1Box5
    End If
    If BoxComments.Value <> "" Then
        Cells(LastRow, 81).Value = BoxComments
    End If
    
    'Re-enable event triggering
    Application.EnableEvents = True
   
    MsgBox ("Record Created!")
    
    'Show Dashboard.
    Application.Goto Worksheets("Dashboard").Range("A1")
    'Close UserForm.
    Unload Me
    
End Sub



Private Sub cmdUpdate_Click()


    'Prevent data submission triggering worksheet_change event
    Application.EnableEvents = False
    
Dim dFind As Date
    
    If DateFound Is Nothing Then
        dFind = BoxDate
        Set DateFound = Range(DateSPWTP).Find(dFind)
    End If
    If DateFound Is Nothing Then
        MsgBox "Could not find the date!", vbExclamation, "Not Found"
    End If
    
    With DateFound
            If BoxOperator.Value <> "" Then
                .Offset(0, 1) = BoxOperator
            End If
            If Page7Box1.Value <> "" Then
                .Offset(0, 3) = Page7Box1
            End If
            If Page7Box2.Value <> "" Then
                .Offset(0, 67) = Page7Box2
            End If
            If Page7Box3.Value <> "" Then
                .Offset(0, 68) = Page7Box3
            End If
            If Page7Box4.Value <> "" Then
                .Offset(0, 71) = Page7Box4
            End If
            If Page7Box5.Value <> "" Then
                .Offset(0, 72) = Page7Box5
            End If
            If Page7Box6.Value <> "" Then
                .Offset(0, 61) = Page7Box6
            End If
            If BoxComments.Value <> "" Then
                .Offset(0, 80) = BoxComments
            End If
    End With
     
    Set DateFound = Nothing
    
    'Re-enable event triggering
    Application.EnableEvents = True
    
    MsgBox ("Record Updated!")
    
    'Show Dashboard.
    Application.Goto Worksheets("Dashboard").Range("A1")
    'Close UserForm.
    Unload Me
    
End Sub

 

[RoryA]

It certainly is possible - all you need to do is determine the correct row number. For a new record, you'd add a new row and use that row number, for an existing one use the row number you found in your search. The code to add/edit is then exactly the same as you're just writing data to the same columns.

 

[ddoublev]

Thanks for your help.
Im not sure if i totally understand what you mean.
Is it possible to link a single command button to two different subs based on previous results? ie If record found true call Sub Update, If record found false call Sub Submit?

 

[ddoublev]

Private Sub cmdAddUpdate_Click()


    If DateFound Is Nothing Then
        Call cmdAdd
    Else
        Call cmdUpdate


End Sub