Adding a Delete Record button to my UserForm?

[jmpatrick]

Here's my UserForm:

Here's the fabulous code that (finally) makes it work and quite possibly saved my job:

Private Sub cmdSearchSubCode_Click()
Dim SubCode_id As String
SubCode = Trim(SubCode.Text)
lastrow = Worksheets("DataCenter").Cells(Rows.Count, 2).End(xlUp).Row
For i = 1 To lastrow
If Worksheets("DataCenter").Cells(i, 3).Value = SubCode Then
SubCode.Text = Worksheets("DataCenter").Cells(i, 2).Value
SubName.Text = Worksheets("DataCenter").Cells(i, 5).Value
SubInitials.Text = Worksheets("DataCenter").Cells(i, 4).Value
FieldManager.Text = Worksheets("DataCenter").Cells(i, 7).Value
End If
Next
End Sub

Here's the code I have to delete the entire row of the currently displayed record from the DataCenter sheet:

Private Sub cmdDeleteSub_Click()
Answer = MsgBox("Are you sure you want to delete this Subdivision completely?  There is no undo", vbyeYesNo + vbQuestion, "Delete Record?")
If Answer = vbYes Then
   Cells(currentrow, 1).EntireRow.Delete
End If
End Sub

It doesn't work. I'm thinking the problem is that despite the information displaying on the UserForm, it isn't selected on the database sheet. Am I close?

 

[dmt32]

Hi
Try this update to your codes & see if resolves your issue

Dim i As Long
Dim wsDataCenter As Worksheet

Private Sub cmdSearchSubCode_Click()
    Dim lastrow     As Long
 
    SubCode = Trim(SubCode.Text)
    If Len(SubCode.Text) = 0 Then Exit Sub
 
    With wsDataCenter
        lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
        For i = 1 To lastrow
            If .Cells(i, 3).Value = SubCode Then
                SubCode.Text = .Cells(i, 2).Value
                SubName.Text = .Cells(i, 5).Value
                SubInitials.Text = .Cells(i, 4).Value
                FieldManager.Text = .Cells(i, 7).Value
    
             Me.cmdDeleteSub.Enabled = True
            Exit For
        End If
    Next
 End With
End Sub

Private Sub cmdDeleteSub_Click()
    Dim CurrentRow  As Long
    Dim Answer      As VbMsgBoxResult
 
    CurrentRow = i
    Answer = MsgBox("Are you sure you want to delete this Subdivision completely?  There is no undo", vbyeYesNo + vbQuestion, "Delete Record?")
    If Answer = vbYes Then wsDataCenter.Cells(CurrentRow, 1).EntireRow.Delete

End Sub

Private Sub UserForm_Initialize()
    Set wsDataCenter = ThisWorkbook.Worksheets("DataCenter")
    Me.cmdDeleteSub.Enabled = False
End Sub

Note the variables at the top of codes shown in BOLD - these MUST be placed at very TOP of your forms code page OUTSIDE any procedure

Dave

 

[jmpatrick]

Hi
Try this update to your codes & see if resolves your issue

Dim i As Long
Dim wsDataCenter As Worksheet

Private Sub cmdSearchSubCode_Click()
    Dim lastrow     As Long
 
    SubCode = Trim(SubCode.Text)
    If Len(SubCode.Text) = 0 Then Exit Sub
 
    With wsDataCenter
        lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
        For i = 1 To lastrow
            If .Cells(i, 3).Value = SubCode Then
                SubCode.Text = .Cells(i, 2).Value
                SubName.Text = .Cells(i, 5).Value
                SubInitials.Text = .Cells(i, 4).Value
                FieldManager.Text = .Cells(i, 7).Value
   
             Me.cmdDeleteSub.Enabled = True
            Exit For
        End If
    Next
 End With
End Sub

Private Sub cmdDeleteSub_Click()
    Dim CurrentRow  As Long
    Dim Answer      As VbMsgBoxResult
 
    CurrentRow = i
    Answer = MsgBox("Are you sure you want to delete this Subdivision completely?  There is no undo", vbyeYesNo + vbQuestion, "Delete Record?")
    If Answer = vbYes Then wsDataCenter.Cells(CurrentRow, 1).EntireRow.Delete

End Sub

Private Sub UserForm_Initialize()
    Set wsDataCenter = ThisWorkbook.Worksheets("DataCenter")
    Me.cmdDeleteSub.Enabled = False
End Sub

Note the variables at the top of codes shown in BOLD - these MUST be placed at very TOP of your forms code page OUTSIDE any procedure

Dave

Thanks, Dave.

Added all code following your instructions. Does not delete the row or throw any errors.

 

[dmt32]

Thanks, Dave.

Added all code following your instructions. Does not delete the row or throw any errors.

Sorry did not look at your msgbox construct, you had a typo

try this update

Private Sub cmdDeleteSub_Click()
    Dim CurrentRow  As Long
    Dim Answer      As VbMsgBoxResult
    
    CurrentRow = i
    Answer = MsgBox("Are you sure you want to delete this Subdivision completely?  There is no undo", vbYesNo + vbQuestion, "Delete Record?")
    If Answer = vbYes Then wsDataCenter.Cells(CurrentRow, 1).EntireRow.Delete

End Sub

Dave

 

[jmpatrick]

Sorry did not look at your msgbox construct, you had a typo

try this update

Private Sub cmdDeleteSub_Click()
    Dim CurrentRow  As Long
    Dim Answer      As VbMsgBoxResult
   
    CurrentRow = i
    Answer = MsgBox("Are you sure you want to delete this Subdivision completely?  There is no undo", vbYesNo + vbQuestion, "Delete Record?")
    If Answer = vbYes Then wsDataCenter.Cells(CurrentRow, 1).EntireRow.Delete

End Sub

Dave

Seeing some strange behavior.

I didn't mention, but also have this code to run the search by just typing the SubCode and hitting the enter key:

Private Sub SubCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.SubCode.Value) = "*" Then
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             cmdSearchSubCode_Click
             SubCode.SetFocus
        End If
End Sub

This don't seem to be playing nicely with the new code. If I type a Sub Code in and hit enter or click the button I only get the Sub Name back.

 

[dmt32]

This don't seem to be playing nicely with the new code. If I type a Sub Code in and hit enter or click the button I only get the Sub Name back.

Did the update to your original request resolve the delete record issue?

Dave

 

[jmpatrick]

Did the update to your original request resolve the delete record issue?

Dave

Here's the only way I can get it to delete: Use the combo box to select a Sub. This populates everything correctly. Then I click the Search By Sub button. That activates the Delete Sub button. At that point clicking the delete button successfully deletes the correct row.

 

[dmt32]

Here's the only way I can get it to delete: Use the combo box to select a Sub. This populates everything correctly. Then I click the Search By Sub button. That activates the Delete Sub button. At that point clicking the delete button successfully deletes the correct row.

Seems like you have other events that may be conflicting

Delete line below shown in BOLD & see if helps

Private Sub UserForm_Initialize()
    Set wsDataCenter = ThisWorkbook.Worksheets("DataCenter")
    Me.cmdDeleteSub.Enabled = False
End Sub

Dave

 

[jmpatrick]

Seems like you have other events that may be conflicting

Delete line below shown in BOLD & see if helps

Private Sub UserForm_Initialize()
    Set wsDataCenter = ThisWorkbook.Worksheets("DataCenter")
    Me.cmdDeleteSub.Enabled = False
End Sub

Dave

No difference.

 

[dmt32]

No difference.

you will need to publish all code behind your form to help understand what issue may be or better, place copy of workbook with dummy data in file sharing site like dropbox & provide a link to it

Dave