Adding a Delete Record button to my UserForm?

jmpatrick

Board Regular
Joined
Aug 17, 2016
Messages
241
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:

jmpatrick

Board Regular
Joined
Aug 17, 2016
Messages
241
Office Version
  1. 365
Platform
  1. Windows
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

Here's the code from the relevant form:

VBA Code:
Dim i As Long
Dim wsDataCenter As Worksheet
Private Sub UserForm_Initialize()
    SubName.List = [DataCenter!SubName].Value
    FieldManager.List = [FieldManagers!FMNames].Value
    Set wsDataCenter = ThisWorkbook.Worksheets("DataCenter")
    Me.cmdDeleteSub.Enabled = False
End Sub
Private Sub cmdCloseSubMaintenance_Click()
Unload frmSubMaintenance
End Sub
Private Sub cmdSearchSubName_Click()
Dim SubName_id As String
SubName = Trim(SubName.Text)
lastrow = Worksheets("DataCenter").Cells(Rows.Count, 5).End(xlUp).Row
For i = 1 To lastrow
If Worksheets("DataCenter").Cells(i, 5).Value = SubName Then
SubCode.Text = Worksheets("DataCenter").Cells(i, 2).Value
SubInitials.Text = Worksheets("DataCenter").Cells(i, 4).Value
FieldManager.Text = Worksheets("DataCenter").Cells(i, 7).Value
End If
Next
End Sub
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 cmdSearchSubInitials_Click()
Dim SubCode_id As String
SubInitials = Trim(SubInitials.Text)
lastrow = Worksheets("DataCenter").Cells(Rows.Count, 4).End(xlUp).Row
For i = 1 To lastrow
If Worksheets("DataCenter").Cells(i, 4).Value = SubInitials Then
SubName.Text = Worksheets("DataCenter").Cells(i, 5).Value
SubCode.Text = Worksheets("DataCenter").Cells(i, 2).Value
FieldManager.Text = Worksheets("DataCenter").Cells(i, 7).Value
End If
Next
End Sub
Private Sub cmdUpdateSub_Click()
Dim SubName_id As String
SubName = Trim(SubName.Text)
lastrow = Worksheets("DataCenter").Cells(Rows.Count, 5).End(xlUp).Row
For i = 1 To lastrow
If Worksheets("DataCenter").Cells(i, 5).Value = SubName Then
   Worksheets("DataCenter").Cells(i, 2).Value = SubCode.Text
   Worksheets("DataCenter").Cells(i, 4).Value = SubInitials.Text
   Worksheets("DataCenter").Cells(i, 7).Value = FieldManager.Text
End If
Next
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", vbYesNo + vbQuestion, "Delete Record?")
    If Answer = vbYes Then wsDataCenter.Cells(CurrentRow, 1).EntireRow.Delete

With Me
        .SubName.Clear
        .SubCode.Value = ""
        .SubInitials.Value = ""
        .FieldManager.Clear
End With
MsgBox ("Subdivision Successfully Deleted!")
Call UserForm_Initialize
End Sub
Private Sub SubInitials_Change()
    SubInitials.Text = UCase(SubInitials.Text)
End Sub
Private Sub SubCode_Change()
    SubCode.Text = UCase(SubCode.Text)
End Sub
Private Sub SubInitials_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.SubInitials.Value) = "*" Then
                Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             cmdSearchSubInitials_Click
        End If
        SubInitials.SetFocus
End Sub
Private Sub SubName_Change()
     Call cmdSearchSubName_Click
End Sub
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  1. Windows
I should have guessed that you are using variable i in other controls that are doing the same thing, so when they get populated from the search, those controls change events likely to conflict with the variables value.

I do not have time to create your form & controls but if can place a copy on a file sharing site, will have a look at it for you.

Dave
 

jmpatrick

Board Regular
Joined
Aug 17, 2016
Messages
241
Office Version
  1. 365
Platform
  1. Windows
I should have guessed that you are using variable i in other controls that are doing the same thing, so when they get populated from the search, those controls change events likely to conflict with the variables value.

I do not have time to create your form & controls but if can place a copy on a file sharing site, will have a look at it for you.

Dave

Interesting discovery! With a blank form, if I manually add a SubCode and hit enter I only get the SubName. However, if I hit enter a second time it populates the rest of the data.
 

jmpatrick

Board Regular
Joined
Aug 17, 2016
Messages
241
Office Version
  1. 365
Platform
  1. Windows
Ha! I duplicated a line of code to simulate 2 enter key hits and it works:

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
             cmdSearchSubCode_Click
             SubCode.SetFocus
        End If
End Sub

It's probably a wonky way to solve the problem.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  1. Windows
Ha! I duplicated a line of code to simulate 2 enter key hits and it works:

It's probably a wonky way to solve the problem.
Looks like you have a conflict between events but if have a resolution all good

Dave
 

Forum statistics

Threads
1,148,371
Messages
5,746,308
Members
424,006
Latest member
Metal_warrior

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