DanAnderton
New Member
- Joined
- Sep 22, 2017
- Messages
- 16
Second question of the day.
I have a delete button as part of a userform, it originally worked just fine, I've since added a column to the worksheet, adjusted all the VB code to account for it but the thing that I can't seem to get working again is the delete button.
The delete button code:
The full code for the form is below:
Any help would be much appreciated.
I have a delete button as part of a userform, it originally worked just fine, I've since added a column to the worksheet, adjusted all the VB code to account for it but the thing that I can't seem to get working again is the delete button.
The delete button code:
Code:
Private Sub Delete_Click()
Dim sil As Long
If ListBox1.ListIndex = -1 Then
MsgBox "Choose an entry", vbExclamation
Exit Sub
End If
If ListBox1.ListIndex >= 0 Then
cevap = MsgBox("Confirm you wish to delete this rower?", vbYesNo)
If cevap = vbYes Then
Sheets("FullRowerDetails").Range("D:D").Find(What:=ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate
sil = ActiveCell.Row
Sheets("FullRowerDetails").Rows(sil).Delete
End If
End If
ListBox1.List = Sheets("FullRowerDetails").Range("D8:Dl00" & Cells(Rows.Count, 1).End(xlUp).Row).Value
Application.ScreenUpdating = False
Unload Me
UpdateRowers.Show
Application.ScreenUpdating = True
End Sub
The full code for the form is below:
Code:
Private Sub AddCancel_Click()Unload Me
End Sub
Private Sub AddSubmit_Click()
Dim Degistirilecek_Satir As Long
Dim sor As String
If AddSurname = "" Or AddFirstName = "" Then
Call MsgBox("click the contact so it can be updated", vbInformation, "Edit Contact")
Exit Sub
End If
sor = MsgBox("Are your sure?", vbYesNo)
If sor = vbNo Then Exit Sub
Degistirilecek_Satir = ListBox1.ListIndex + 8
Sheets("FullRowerDetails").Range("B" & Degistirilecek_Satir).Value = WSRADate.Text
Sheets("FullRowerDetails").Range("C" & Degistirilecek_Satir).Value = MYCDate.Text
Sheets("FullRowerDetails").Range("D" & Degistirilecek_Satir).Value = AddSurname.Text
Sheets("FullRowerDetails").Range("E" & Degistirilecek_Satir).Value = AddFirstName.Text
Sheets("FullRowerDetails").Range("F" & Degistirilecek_Satir).Value = AddPhone.Text
Sheets("FullRowerDetails").Range("G" & Degistirilecek_Satir).Value = AddMobile.Text
Sheets("FullRowerDetails").Range("H" & Degistirilecek_Satir).Value = AddEmail.Text
Sheets("FullRowerDetails").Range("I" & Degistirilecek_Satir).Value = AddAddress.Text
Sheets("FullRowerDetails").Range("J" & Degistirilecek_Satir).Value = AddSex.Value
Sheets("FullRowerDetails").Range("K" & Degistirilecek_Satir).Value = AddDOB.Text
Sheets("FullRowerDetails").Range("M" & Degistirilecek_Satir).Value = AddNOK.Text
Sheets("FullRowerDetails").Range("N" & Degistirilecek_Satir).Value = AddNOKPhone.Text
Sheets("FullRowerDetails").Range("O" & Degistirilecek_Satir).Value = AddFirstAid.Value
Sheets("FullRowerDetails").Range("P" & Degistirilecek_Satir).Value = AddCoach.Value
Sheets("FullRowerDetails").Range("Q" & Degistirilecek_Satir).Value = AddRadio.Value
Sheets("FullRowerDetails").Range("R" & Degistirilecek_Satir).Value = AddDaySkipper.Value
Sheets("FullRowerDetails").Range("S" & Degistirilecek_Satir).Value = AddCRB.Value
Sheets("FullRowerDetails").Range("T" & Degistirilecek_Satir).Value = AddIntroTraining.Value
Sheets("FullRowerDetails").Range("U" & Degistirilecek_Satir).Value = AddPowerBoat.Value
Sheets("FullRowerDetails").Range("V" & Degistirilecek_Satir).Value = AddLifejacketTesting.Value
Call MsgBox("The rower has been updated", vbInformation, "Update Rower")
End Sub
Private Sub Delete_Click()
Dim sil As Long
If ListBox1.ListIndex = -1 Then
MsgBox "Choose an entry", vbExclamation
Exit Sub
End If
If ListBox1.ListIndex >= 0 Then
cevap = MsgBox("Entry will be deleted. ... Are you sure ?", vbYesNo)
If cevap = vbYes Then
Sheets("FullRowerDetails").Range("D:D").Find(What:=ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate
sil = ActiveCell.Row
Sheets("FullRowerDetails").Rows(sil).Delete
End If
End If
ListBox1.List = Sheets("FullRowerDetails").Range("D8:Dl00" & Cells(Rows.Count, 1).End(xlUp).Row).Value
Application.ScreenUpdating = False
Unload Me
UpdateRowers.Show
Application.ScreenUpdating = True
End Sub
Private Sub ListBox1_AfterUpdate()
Dim Rw As Long
Rw = ListBox1.ListIndex + 8
Me.WSRADate.Value = Range("B" & Rw).Value
Me.MYCDate.Value = Range("C" & Rw).Value
Me.AddSurname.Value = Range("D" & Rw).Value
Me.AddFirstName.Value = Range("E" & Rw).Value
Me.AddPhone.Value = Range("F" & Rw).Value
Me.AddMobile.Value = Range("G" & Rw).Value
Me.AddEmail.Value = Range("H" & Rw).Value
Me.AddAddress.Value = Range("I" & Rw).Value
Me.AddSex.Value = Range("J" & Rw).Value
Me.AddDOB.Value = Range("K" & Rw).Value
Me.AddNOK.Value = Range("M" & Rw).Value
Me.AddNOKPhone.Value = Range("N" & Rw).Value
If Range("O" & Rw).Value = "True" Then Me.AddFirstAid.Value = True Else Me.AddFirstAid.Value = False
If Range("P" & Rw).Value = "True" Then Me.AddCoach = True Else Me.AddCoach = False
If Range("Q" & Rw).Value = "True" Then Me.AddRadio = True Else Me.AddRadio = False
If Range("R" & Rw).Value = "True" Then Me.AddDaySkipper = True Else Me.AddDaySkipper = False
If Range("S" & Rw).Value = "True" Then Me.AddCRB = True Else Me.AddCRB = False
If Range("T" & Rw).Value = "True" Then Me.AddIntroTraining = True Else Me.AddIntroTraining = False
If Range("U" & Rw).Value = "True" Then Me.AddPowerBoat = True Else Me.AddPowerBoat = False
If Range("V" & Rw).Value = "True" Then Me.AddLifejacketTesting = True Else Me.AddLifejacketTesting = False
End Sub
Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 2 'COLUMN NUMBER OF LISTBOX
ListBox1.List = ThisWorkbook.Worksheets("FullRowerDetails").Range("D8:l" & Cells(Rows.Count, 3).End(xlUp).Row).Value
AddSex.List = Array("Male", "Female")
End Sub
Any help would be much appreciated.