Adding a Delete Record button to my UserForm?

jmpatrick

Board Regular
Joined
Aug 17, 2016
Messages
230
Office Version
  1. 365
Platform
  1. Windows
Here's my UserForm:

edit 2.png


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

VBA Code:
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:

VBA Code:
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?
 
Last edited:

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,915
Office Version
  1. 2019
Platform
  1. Windows
Hi
Try this update to your codes & see if resolves your issue

Rich (BB code):
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

Board Regular
Joined
Aug 17, 2016
Messages
230
Office Version
  1. 365
Platform
  1. Windows
Hi
Try this update to your codes & see if resolves your issue

Rich (BB code):
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,915
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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

Board Regular
Joined
Aug 17, 2016
Messages
230
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

try this update

VBA Code:
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:

VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,915
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Aug 17, 2016
Messages
230
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,915
Office Version
  1. 2019
Platform
  1. Windows
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

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

Dave
 

jmpatrick

Board Regular
Joined
Aug 17, 2016
Messages
230
Office Version
  1. 365
Platform
  1. Windows
Seems like you have other events that may be conflicting

Delete line below shown in BOLD & see if helps

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

Dave

No difference.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,915
Office Version
  1. 2019
Platform
  1. Windows
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
 

Forum statistics

Threads
1,147,560
Messages
5,741,830
Members
423,689
Latest member
Jords998

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top